SQL Server 의 Error Log의 데이터가 많을 경우 로드하는데 많은 시간이 발생됩니다.
Error Log 파일의 구성을 보면
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer 위치에
NumErrorLogs 숫자 만큼 파일이 보관되며(6개가 Default) 99 까지 설정 있습니다.

파일 개수 변경 방법은
방법 1.
USE [master]
GO
EXEC
xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, [변경할 파일 숫자]
GO

방법2.

로 파일 개수를 변경할 수 있습니다.

새로운 Error Log 파일로 시작하는 방법은 sp_cycle_errorlog 프로시져를 사용하는 방법이 있습니다.
EXEC master.sys.sp_cycle_errorlog;

SQL 에는 Agent Error Log가 따로 존재 합니다.

새로운 Error Log 파일로 시작하는 방법은 sp_cycle_agent_errorlog 프로시져를 사용하는 방법이 있습니다.

EXEC msdb.dbo.sp_cycle_agent_errorlog;

Agent Error 로그의 구성은

에서 설정 할 수 있습니다.

USE
[msdb]
GO
EXEC
msdb.dbo.sp_set_sqlagent_properties @errorlogging_level=7
GO
로 에이전트 로그 수준을 설정 할 수 있습니다.

'MS SQL' 카테고리의 다른 글

MS SQL User Mapping 정보 확인  (0) 2014.01.06
Replication(복제) 삭제 에러시 ( error 18482 )  (0) 2013.08.09
SQL Server Error Log 관리  (0) 2013.04.25
TRY CATCH는 과연 만고땡인가?  (0) 2013.04.23
MS SQL 2008 이상 BACKUP LOG TRUNCATE_ONLY , NO_LOG  (0) 2013.04.23
Fill Factor  (0) 2013.04.16

버전 : SQL 2005, SQL 2008, SQL 2008 R2, SQL 2012

MS SQL Server 2005 이상부터 C++에나 있던 TRY CATCH 구문이
도입되어서 굉장히 편하게 에러가 발생되는 부분을 찾고 처리하기 쉽게 되었다.
BEGIN TRY
    SELECT 1 / 0
END TRY
BEGIN
CATCH
SELECT
        ERROR_NUMBER()    AS ErrorNumber
    ,    ERROR_SEVERITY()    AS ErrorSeverity
    ,    ERROR_STATE()        AS ErrorState
    ,    ERROR_PROCEDURE()    AS ErrorProcedure
    ,    ERROR_LINE()        AS ErrorLine
    ,    ERROR_MESSAGE()    AS ErrorMessage;
END CATCH


그러나 TRY CATCH 가 모든 에러를 체크하는 것은 아니다.

우선 아래의 상황을 보자

1번 세션

BEGIN TRY
    BEGIN TRAN
        -- 무엇인가 작업???
        -- INSERT INTO Test1234 VALUES(1)
        -- 10 초 대기
        WAITFOR DELAY '00:00:10'
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK

SELECT
        ERROR_NUMBER()    AS ErrorNumber
    ,    ERROR_SEVERITY()    AS ErrorSeverity
    ,    ERROR_STATE()        AS ErrorState
    ,    ERROR_PROCEDURE()    AS ErrorProcedure
    ,    ERROR_LINE()        AS ErrorLine
    ,    ERROR_MESSAGE()    AS ErrorMessage;
END CATCH


실행 후에 10초가 넘기 전에 중지버튼으로 쿼리를 중지해 보자

그리고

SELECT    @@TRANCOUNT AS TRAN_COUNT

실행하면 Transaction이 1개 남아 있는 것을 확인 할 수 있다.

 

이제 테이블을 하나 만들고 다음과 같은 상황을 만들자

 

CREATE TABLE Test1234

(

    A INT

)

GO

 

아래 부분의 주석을 제거 하고

INSERT INTO Test1234 VALUES(1)

 

"새 쿼리(N)" 버튼을 눌러서 다른 세션을 만들고 다음과 같이 입력을 해 놓자

2번 세션

SELECT * FROM Test1234

 

자 이제 테스트를 해보자

 

1번 세션에서 쿼리 옵션의 TIMEOUT 값을 변경하자

 

1번 세션을 실행하고 바로 2번 세션을 실행하고 1번 세션에서 5초를 기다려 보자.
(실행 제한 시간으로 5초 후에 자동 중단된다.)
아까 TRANCOUNT를 봐서 예상 했겠지만 2번 세션은 LOCK이 걸려 블로킹 상태로 대기 중이다.

 

이런 현상이 외부 PDA같은 것을 사용하는 경우 빈번하게 발생 할 수 있다.

해결 방법은 다음과 같다

1번 세션

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN
        INSERT INTO Test1234 VALUES(1)
        -- 10 초 대기
        WAITFOR
DELAY '00:00:10'
    COMMIT
END
TRY
BEGIN
CATCH
    ROLLBACK

    SELECT
            ERROR_NUMBER()        AS ErrorNumber
        ,    ERROR_SEVERITY()    AS ErrorSeverity
        ,    ERROR_STATE()        AS ErrorState
        ,    ERROR_PROCEDURE()    AS ErrorProcedure
        ,    ERROR_LINE()        AS ErrorLine
        ,    ERROR_MESSAGE()        AS ErrorMessage;
END CATCH
SET
XACT_ABORT OFF;

 

XACT_ABORT 은 보통 링크드 서버나 이기종간의 쿼리를 작성할 때 많이 사용했었는데

같은 원리로 세션의 문제가 생겼을때도 사용할 수 있다.

 

 

버전 : SQL 2008, SQL 2008 R2, SQL 2012

MS SQL 2008 부터는

BACKUP LOG [데이터베이스명] WITH TRUNCATE_ONLY
GO


를 지원하지 않고 있습니다.
이유는 이걸 사용했을 경우 로그체인이 끊어져서 데이터베이스에 문제가 발생 할 경우 데이터를 복구 할 수
없는 상태가 발생 할 수 있습니다.
그러나 관리상의 문제나 기타 다른 문제로 LOG의 백업이 당장 필요 없는 경우도 발생을 합니다.

그럴 경우 사용 할 수 있는 방법은 다음과 같습니다.

주의!!! 아래의 방법은 권장하는 방법이 아니므로 반드시 전체백업 후에 진행하기시 바랍니다.

저의 TestDB 상태입니다.


BACKUP LOG [데이터베이스명] TO DISK='NUL:'
GO



DBCC SHRINKFILE ([데이터베이스 로그 파일명], 1);


그런데 처음 한번 했을 경우 파일이 많이 줄지 않았습니다.
분명 위의 메시지에는 처리 되었다고 나오는데요. 한번 더 BACKUP LOG 를 해
주니 용량을 줄일 수 있었습니다. 이유가 궁금하네요





 

권장되지 않는 방법이므로 주의를 기울여서 사용하시기 바랍니다.

'MS SQL' 카테고리의 다른 글

SQL Server Error Log 관리  (0) 2013.04.25
TRY CATCH는 과연 만고땡인가?  (0) 2013.04.23
MS SQL 2008 이상 BACKUP LOG TRUNCATE_ONLY , NO_LOG  (0) 2013.04.23
Fill Factor  (0) 2013.04.16
암호화된 Procedure 보는 방법  (2) 2013.04.13
SSMS 관리자 전용 연결하기  (0) 2013.04.13

Fill Factor는 Index Data의 채우기 비율을 말한다.
이게 무슨 이야기냐 하면 SQL Server의 데이터는 PAGE라는 개념에 저장이 된다.
PAGE에 저장할 수 있는 용량은 약 8000byte 정도가 된다.
("약 8000 byte" 이라고 표현한 이유는 Heap 데이터, 클러스터인덱스, 넌클러스터 인덱스 구조냐에 따라서 실제 저장 용량이 차이가 난다.)

실제 계산을 해보자.

CREATE TABLE Table1
(
    A
    INT            -- 4 byte
,    B    CHAR(900)        -- 900 byte
)
GO
테이블은
Row당 904 byte를 차지한다.
그렇다면 한 개 PAGE에는 8개(904 * 8 = 7232 byte)의 Row가 들어간다.

INSERT INTO Table1 VALUES( 1, 'A1' )    -- 1
INSERT INTO Table1 VALUES( 2, 'B2' )    -- 2
INSERT INTO Table1 VALUES( 3, 'C3' )    -- 3
INSERT INTO Table1 VALUES( 4, 'D4' )    -- 4
INSERT INTO Table1 VALUES( 5, 'E5' )    -- 5
INSERT INTO Table1 VALUES( 6, 'F6' )    -- 6
INSERT INTO Table1 VALUES( 7, 'G7' )    -- 7
INSERT INTO Table1 VALUES( 8, 'H8' )    -- 8    (1 PAGE)
INSERT INTO Table1 VALUES( 9, 'I9' )    -- 9    (2 PAGE)

확인 하는 방법은 다음과 같다.

 

-- Index 번호 확인

SELECT * FROM sys.indexes

WHERE object_id = OBJECT_ID('Table1')

GO

Index_id : 0 – Heap
Index_id : 1 – 클러스터 인덱스
Index_id : 2 이상 – 넌클러스터 인덱스


-- 페이지 번호 확인

-- DBCC IND('[데이터베이스명]', '[테이블명]', [인덱스번호])

DBCC IND('TestDB', 'Table1', 0)

GO

8번까지는 PagePID 548이 생성되고 9번부터 552가 생성된다.

 

-- 페이지 내용 확인

DBCC TRACEON(3604)

GO

 

-- DBCC PAGE('[데이터베이스명]', [파일번호], [페이지번호], [출력옵션])

DBCC PAGE('TestDB', 1, 548, 3)



Heap 구조는 인덱스가 없는 경우이므로 Fill Factor를 설정 할 수 없다
테이블을 삭제하고 다시 테이블을 만들고 인덱스를 만들자.


CREATE
INDEX IX_Table1_B ON Table1(B)
GO

-- Index 번호 확인

SELECT * FROM sys.indexes
WHERE object_id = OBJECT_ID('Table1')
GO
index_id 2
추가
것을 확인 할 수 있다.


이제 9번까지 INSERT를 하고 PAGE를 보면 3개의 PAGE가 생성된 것을 볼 수 있다.

Indexl Level이 0인 것이 실제 데이터가 들어 있는 Leaf Node가 된다.


그러나 모든 데이터가 인덱스 정렬에 맞게 순차적으로 들어 갈 수는 없다.
만약 'A2'라는 데이터를 넣게 된다면 552 PageID에 데이터를 넣어야 하지만 데이터가 가득 차 있어서 데이터를 넣을 수 없는 경우가 발생된다.
실제 어떤 일이 일어나는지 확인해 보기 위해서

INSERT INTO Table1 VALUES( 2, 'A2' )    -- 1-1
넣어 보자.


PageID 557이 추가된 것을 확인 할 수 있다.

실제 페이지 데이터의 변경 내역을 보면


루트 노트에 Leaf 노드의 내용이 추가 되고 552 Page의 데이터가 절반으로 잘려서 557 Page가 추가로 생성이 되었다.
이걸 페이지 분할이라고 한다.

모든 페이지는 기본적으로 더 이상 데이터를 넣을 수 없는 경우 페이지 분할이 발생된다.
여기서 발생되는 문제가 페이지 분할시 디스크에서 신규 페이지 영역을 할당받고 기존 데이터의 반을 다른 페이지에 넣고
하는데 SQL Server에 영향을 미치게 된다.

그래서 나온 것이 Fill Factor이다.(서론이 너무 길었다.)
Fill Factor의 개념은 미리 Index데이터를 정렬한다는 의미이다.
말 보다는 실전으로 데이터를 넣어 보자.

테이블을 만들고 데이터를 넣어 보자.(인덱스는 만들지 않는다.)
그러면 Heap 구조로 데이터가 들어가 있을 것이다. 그리고
IX_Table1_B를 만들어 보자.
이렇게 구성하면 첫 번째 페이지에 데이터가 가득 들어가 있는 형태가 나온다.
이러면 페이지 분할이 발생하므로 Index를 구성할 때 Fill Factor 옵션을 주어서 구성을 해보자

CREATE INDEX IX_Table1_B1 ON Table1(B)
WITH FILLFACTOR =
50                    --
Fill factor : 50%
GO



페이지 번호를 보고 페이지 실제 데이터를 보면 데이터들이 페이지 용량의 50%만 들어 있는 것을 확인 할 수 있다.

 

Fill Factor가 구성된 데이터는 다음 페이지 분할 시 어떻게 될지가 또 궁금할 것이다.

인터넷의 글들을 보게 되면 다음 분할시에도 Fill Factor에 맞추어서 분할 된다고 되어 있으나 실제로는 그렇지 않고 50%:50% 으로 페이지가 분할 된다.


CREATE INDEX IX_Table1_B2 ON Table1(B)
WITH FILLFACTOR =
20                    --
Fill factor : 20%
GO

로 테스트를 해보자

 

INSERT INTO Table1 VALUES( 1, 'A2' )
GO 6
INSERT INTO Table1 VALUES( 1, 'A1' )
GO
실행하면 페이지 분할이 일어난다.


565 PageId가 분리되어서 575가 생성됨.

분할된 페이지 Fill Factor가 20%이지만 50%:50%으로 분리된다.




그렇다면 Fill Factor의 퍼센트로 구성되는 시점은 언제일까?
인덱스를 생성하거나 인덱스를 Rebuild 하는 경우에 Fill Factor 비율에 맞게 인덱스가 정렬된다.
그러므로 Fill Factor가 시스템에 영향을 준다고 판단된다면 자주 인덱스를 Rebuild 해야 한다.

Fill Factor는 기본으로 0% 채우기 비율을 가지고 있다. (0%은 100%의 채우기 비율과 거의 동일한 의미를 가짐.)

조정하는 방법은 다음과 같다.

 

-- Fill Factor 설정
sp_configure 'show advanced options', 1;

GO

sp_configure 'fill factor', 0;

GO

RECONFIGURE;

GO

 

-- Fill Factor 조회
SELECT    DB_NAME()                AS DatabaseName
    ,    SCHEMA_NAME(o.Schema_ID)    AS SchemaName
    ,    OBJECT_NAME(s.[object_id])    AS TableName
    ,
    i.name                AS IndexName
    ,
    i.fill_factor            AS Fill_Factor
FROM    sys.dm_db_index_usage_stats s
        INNER
JOIN sys.indexes i
        ON    s.[object_id] = i.[object_id]
            AND
s.index_id = i.index_id
        INNER
JOIN sys.objects o
        ON    i.object_id = O.object_id
WHERE    s.database_id = DB_ID()
        AND i.name IS NOT NULL
        AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
ORDER BY    fill_factor
DESC

버전 : SQL Server 2005, 2008, 2008 R2, 2012

프로시져를 암호화해서 만들어 놓았는데 원본 소스를 삭제한 경우 기존에 암호화 된 프로시져를 분석해야 한다.

분석할 암호화된 프로지셔를 만들어보자.

  1. -- 암호화된 Procedure   
  2. -- 2013.04.13  
  3. SET ANSI_NULLS ON  
  4. GO  
  5. SET QUOTED_IDENTIFIER ON  
  6. GO  
  7.     
  8. CREATE PROCEDURE sp_EncryptionProc   
  9.     @TEST VARCHAR(100)  
  10. WITH ENCRYPTION  
  11. AS  
  12. BEGIN  
  13.     -- SET NOCOUNT ON added to prevent extra result sets from  
  14.     -- interfering with SELECT statements.  
  15.     SET NOCOUNT ON;  
  16.     
  17.     SELECT  *   
  18.     FROM    sys.objects  
  19. END  
  20. GO  

 

분석에 사용될 Procedure를 만들자

/****** Object: StoredProcedure [dbo].[sp__procedure] Script Date: 2013-04-13 오후 1:36:39 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[sp__procedure]

(@procedure sysname = NULL)

AS

    SET NOCOUNT ON

 

    DECLARE @intProcSpace    bigint

        ,    @t                bigint

        ,    @maxColID        smallint

        ,    @intEncrypted    tinyint

        ,    @procNameLength int

    

    select    @maxColID = max(subobjid)

        --,    @intEncrypted = encrypted

    FROM

    sys.sysobjvalues WHERE objid = object_id(@procedure)

    --GROUP BY encrypted

 

    --select @maxColID as 'Rows in sys.sysobjvalues'

    select @procNameLength = datalength(@procedure) + 29

 

    DECLARE @real_01 nvarchar(max)

    DECLARE @fake_01 nvarchar(max)

    DECLARE @fake_encrypt_01 nvarchar(max)

    DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max)

 

    select @real_decrypt_01a = ''

 

    -- extract the encrypted imageval rows from sys.sysobjvalues

    SET @real_01 = (SELECT imageval FROM sys.sysobjvalues WHERE objid = object_id(@procedure) and valclass = 1 and subobjid = 1 )

 

    -- create this table for later use

    create table #output

    (

        [ident] [int] IDENTITY (1, 1) NOT NULL

    ,    [real_decrypt] NVARCHAR(MAX)

    )

 

    -- We'll begin the transaction and roll it back later

    BEGIN TRAN

 

    -- alter the original procedure, replacing with dashes

    SET @fake_01='ALTER PROCEDURE '+ @procedure +' WITH ENCRYPTION AS

    '+REPLICATE('-', 40003 - @procNameLength)

 

    EXECUTE (@fake_01)

 

    -- extract the encrypted fake imageval rows from sys.sysobjvalues

    SET @fake_encrypt_01=(SELECT imageval FROM sys.sysobjvalues WHERE objid = object_id(@procedure) and valclass = 1 and subobjid = 1)

 

    SET @fake_01='CREATE PROCEDURE '+ @procedure +' WITH ENCRYPTION AS

    '+REPLICATE('-', 40003 - @procNameLength)

    --start counter

    SET @intProcSpace=1

    --fill temporary variable with with a filler character

    SET @real_decrypt_01 = replicate(N'A', (datalength(@real_01) /2 ))

 

    --loop through each of the variables sets of variables, building the real variable

    --one byte at a time.

    SET @intProcSpace=1

 

    -- Go through each @real_xx variable and decrypt it, as necessary

    WHILE @intProcSpace<=(datalength(@real_01)/2)

    BEGIN

    --xor real & fake & fake encrypted

    SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1,

    NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^

    (UNICODE(substring(@fake_01, @intProcSpace, 1)) ^

    UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))

    SET @intProcSpace=@intProcSpace+1

    END

 

    -- Load the variables into #output for handling by sp_helptext logic

 

    insert #output (real_decrypt) select @real_decrypt_01

    -- select real_decrypt AS '#output chek' from #output — Testing

 

    -- ————————————-

    -- Beginning of extract from sp_helptext

    -- ————————————-

    declare @dbname sysname

    ,@BlankSpaceAdded int

    ,@BasePos int

    ,@CurrentPos int

    ,@TextLength int

    ,@LineId int

    ,@AddOnLen int

    ,@LFCR int -- lengths of line feed carriage return

    ,@DefinedLength int

    ,@SyscomText nvarchar(4000)

    ,@Line nvarchar(255)

 

    Select @DefinedLength = 255

    SELECT @BlankSpaceAdded = 0

    --Keeps track of blank spaces at end of lines. Note Len function ignores trailing blank spaces

    CREATE TABLE #CommentText

    (LineId int

    ,Text nvarchar(255) collate database_default)

 

    -- use #output instead of sys.sysobjvalues

    DECLARE ms_crs_syscom CURSOR LOCAL

    FOR SELECT real_decrypt from #output

    ORDER BY ident

    FOR READ ONLY

 

    -- Else get the text.

 

    SELECT @LFCR = 2

    SELECT @LineId = 1

 

    OPEN ms_crs_syscom

 

    FETCH NEXT FROM ms_crs_syscom into @SyscomText

 

    WHILE @@fetch_status >= 0

    BEGIN

 

    SELECT @BasePos = 1

    SELECT @CurrentPos = 1

    SELECT @TextLength = LEN(@SyscomText)

 

    WHILE @CurrentPos != 0

    BEGIN

    --Looking for end of line followed by carriage return

    SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,

    @BasePos)

 

    --If carriage return found

    IF @CurrentPos != 0

    BEGIN

    --If new value for @Lines length will be > then the

    --set length then insert current contents of @line

    --and proceed.

 

    While (isnull(LEN(@Line),0) + @BlankSpaceAdded +

    @CurrentPos-@BasePos + @LFCR) > @DefinedLength

    BEGIN

    SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +

    @BlankSpaceAdded)

    INSERT #CommentText VALUES

    ( @LineId,

    isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText,

    @BasePos, @AddOnLen), N''))

    SELECT @Line = NULL, @LineId = @LineId + 1,

    @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0

    END

    SELECT @Line = isnull(@Line, N'') +

    isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')

    SELECT @BasePos = @CurrentPos+2

    INSERT #CommentText VALUES( @LineId, @Line )

    SELECT @LineId = @LineId + 1

    SELECT @Line = NULL

    END

    ELSE

    --else carriage return not found

    BEGIN

    IF @BasePos <= @TextLength

    BEGIN

    --If new value for @Lines length will be > then the

    --defined length

    --

    While (isnull(LEN(@Line),0) + @BlankSpaceAdded +

    @TextLength-@BasePos+1 ) > @DefinedLength

    BEGIN

    SELECT @AddOnLen = @DefinedLength -

    (isnull(LEN(@Line),0) + @BlankSpaceAdded)

    INSERT #CommentText VALUES

    ( @LineId,

    isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText,

    @BasePos, @AddOnLen), N''))

    SELECT @Line = NULL, @LineId = @LineId + 1,

    @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =

    0

    END

    SELECT @Line = isnull(@Line, N'') +

    isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')

    if LEN(@Line) < @DefinedLength and charindex(' ',

    @SyscomText, @TextLength+1 ) > 0

    BEGIN

    SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1

    END

    END

    END

    END

 

    FETCH NEXT FROM ms_crs_syscom into @SyscomText

    END

 

    IF @Line is NOT NULL

    INSERT #CommentText VALUES( @LineId, @Line )

 

    select Text from #CommentText order by LineId

 

    CLOSE ms_crs_syscom

    DEALLOCATE ms_crs_syscom

 

    DROP TABLE #CommentText

 

    -- ————————————-

    -- End of extract from sp_helptext

    -- ————————————-

 

    -- Drop the procedure that was setup with dashes and rebuild it with the good stuff

    -- Version 1.1 mod; makes rebuilding hte proc unnecessary

    ROLLBACK TRAN

 

    DROP TABLE #output

 


sp__procedure를 사용하기 위해서는 관리자 전용 연결로 연결(DAC)을 해야 사용이 가능하다.
(관리자 전용 연결(DAC) : http://catchv.tistory.com/34)

 

EXEC sp__procedure 'sp_EncryptionProc'
실행하면
암호화된 Procedure를 볼 수 있다.

 

  1. 형...

    http://catchv.tistory.com/trackback/34 이거 오타 아니예요...

  2. catchv 2013.04.16 12:00 신고

    그렇구나...ㅎㅎㅎ

+ Recent posts