트랜잭션 로그 읽기 입니다.
추적시 범위를 좁히며 접근할 수 있어 유용(?) 할 것 같네요.
CREATE DATABASE [Crack_Me];
GO
USE Crack_Me;
GO
/* 테스트테이블 생성및 데이터를 한개 입력합니다. */
CREATE TABLE [dbo].[Crack_Me_If_You_Can](
[ID] [int] PRIMARY KEY IDENTITY NOT NULL,
[Insert_Date] [datetime] NOT NULL,
[Some_Data] [varchar](100) NOT NULL,
[Optional_Data] [varchar](50)NULL,
[Life_the_Universe_and_Everything] [int] NOT NULL,
);
GO
INSERT INTO [Crack_Me_If_You_Can]
(
Insert_Date,
Some_Data,
Optional_Data,
Life_the_Universe_and_Everything
)
VALUES (GetDate(), 'Don''t Panic', 'Share and Enjoy', 42)
GO
SELECT * FROM Crack_Me_If_You_Can;
GO
/* fn_dblog함수로 트랜잭션로그 내용을 확인 할 수 있습니다. */
SELECT * FROM fn_dblog(NULL, NULL)
GO
/* 개체별allocation_unit_id를 확인 할 수 있습니다.
sys.allocation_units 의
type이1 또는3이면container_id는sys.partitions.hobt_id 입니다.
type이2이면container_id = sys.partitions.partition_id 입니다.
*/
SELECT
allocunits.allocation_unit_id
,objects.name
,objects.id
FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id)
OR (allocunits.type = 2 and partitions.partition_id = allocunits.container_id)
INNER JOIN sysobjects objects ON partitions.object_id = objects.id
AND objects.type IN ('U', 'u')
WHERE
partitions.index_id IN (0, 1)
GO
SELECT
*
FROM fn_dblog(NULL, NULL)
WHERE
AllocUnitId = 2105058535
AND Operation = 'LOP_INSERT_ROWS'
GO
/* 활성로그뿐 아니라 전체로그를 보여주기 위해 추적플래그2537을 설정 합니다.
아래스크립트는 입력된 데이터로그정보를 확인 하기 위함입니다.
fn_dblog 함수의Operation:
0)AFTER TOTAL SHRINK (O KB)
LOP_BEGIN_CKPT
LOP_END_CKPT
LOP_FILE_HDR_MODIFY
1)FOR INSERT STATEMENT:
LOP_BEGIN_XACT
LOP_INSERT_ROWS
LOP_COMMIT_XACT
2)FOR UPDATE STATEMENT:
LOP_BEGIN_XACT
LOP_MODIFY_ROW
LOP_COMMIT_XACT
3)FOR CREATE OBJECT STATEMENT:
LOP_BEGIN_XACT
LOP_INSERT_ROWS
LOP_MARK_DDL
*/
DBCC TRACEON(2537)
SELECT
[Current LSN],
Operation,
dblog.[Transaction ID],
AllocUnitId,
AllocUnitName,
[Page ID],
[Slot ID],
[Num Elements],
dblog1.[Begin Time],
dblog1.[Transaction Name],
[RowLog Contents 0],
[Log Record]
FROM ::fn_dblog(NULL, NULL) dblog
INNER JOIN (
SELECT
allocunits.allocation_unit_id
,objects.name
,objects.id
FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id)
OR (allocunits.type = 2 and partitions.partition_id = allocunits.container_id)
INNER JOIN sysobjects objects ON partitions.object_id = objects.id
AND objects.type IN ('U', 'u')
WHERE
partitions.index_id IN (0, 1)
) allocunits ON dblog.AllocUnitID = allocunits.allocation_unit_id
INNER JOIN (
SELECT
[Begin Time]
,[Transaction Name]
,[Transaction ID]
FROM fn_dblog(NULL, NULL) x
WHERE Operation = 'LOP_BEGIN_XACT'
) dblog1 ON dblog1.[Transaction ID] = dblog.[Transaction ID]
WHERE
[Page ID] IS NOT NULL AND [Slot ID] >= 0
AND dblog.[Transaction ID] != '0000:00000000'
AND Context in ('LCX_HEAP', 'LCX_CLUSTERED')
DBCC TRACEOFF(2537)
GO
TransactionLog_1.jpg
/*
페이지(PageID:0001:0000004d)내용을 테이블결과셋 형태로 확인 합니다.
*/
DECLARE @pageID$ NVARCHAR(23), @pageID NVARCHAR(50), @sqlCmd NVARCHAR(4000);
SET @pageID$ = '0001:0000004d'
SELECT @pageID =
CONVERT(VARCHAR(4), CONVERT(INT, CONVERT(VARBINARY,
SUBSTRING(@pageID$, 0, 5), 2)))
+ ',' +
CONVERT(VARCHAR(8), CONVERT(INT, CONVERT(VARBINARY,
SUBSTRING(@pageID$, 6, 8), 2)))
SET @sqlCmd = 'DBCC PAGE (''Crack_Me'',' + @pageID + ',3) WITH TABLERESULTS'
PRINT @sqlCmd
EXECUTE(@sqlCmd)
TransactionLog_2.jpg
/*
위 첫번째결과에서 나온 [RowLog Contents 0]의 값을 아래@RowLogContents 변수에 입력합니다.
위 두번째결과에서 나온 [Metadata: AllocUnitId]값 72057594039828480 을 아래 스크립트
(allocunits.allocation_unit_id = 72057594039697408) 입력합니다.
여기부터 끝까지 선택한 후 실행합니다.
*/
DECLARE @RowLogContents VARBINARY(8000)
SET @RowLogContents = 0x300014000100000045040200449E00002A000000050000020028003700446F6E27742050616E6963536861726520616E6420456E6A6F79
DECLARE @lenFixedBytes SMALLINT, @noOfCols SMALLINT, @nullBitMapLength SMALLINT, @nullByteMap VARBINARY(MAX), @nullBitMap VARCHAR(MAX), @noVarCols SMALLINT, @columnOffsetArray VARBINARY(MAX), @varColPointer SMALLINT
SELECT
@lenFixedBytes = CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING(@RowLogContents, 2 + 1, 2)))),
@noOfCols = CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING(@RowLogContents, @lenFixedBytes + 1, 2)))),
@nullBitMapLength = CONVERT(INT, ceiling(@noOfCols/8.0)),
@nullByteMap = SUBSTRING(@RowLogContents, @lenFixedBytes + 3, @nullBitMapLength),
@noVarCols = CASE WHEN SUBSTRING(@RowLogContents, 1, 1) = 0x30 THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING(@RowLogContents, @lenFixedBytes + 3 + @nullBitMapLength, 2))))
ELSE null
END,
@columnOffsetArray = CASE WHEN SUBSTRING(@RowLogContents, 1, 1) = 0x30 THEN
SUBSTRING(@RowLogContents, @lenFixedBytes + 3 + @nullBitMapLength + 2, @noVarCols * 2)
ELSE null
END,
@varColPointer = CASE WHEN SUBSTRING(@RowLogContents, 1, 1) = 0x30 THEN
(@lenFixedBytes + 2 + @nullBitMapLength + 2 + (@noVarCols * 2))
ELSE null
END
DECLARE @byteTable TABLE
(
byte INT
)
DECLARE @cnt INT
SET @cnt = 1
WHILE (@cnt < @nullBitMapLength + 1)
BEGIN
INSERT INTO @byteTable(byte)
VALUES(@cnt)
SET @cnt = @cnt +1
END
SELECT
@nullBitMap = COALESCE(@nullBitMap, '') +
CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 128) % 2) +
CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 64) % 2) +
CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 32) % 2) +
CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 16) % 2) +
CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 8) % 2) +
CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 4) % 2) +
CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 2) % 2) +
CONVERT(NVARCHAR(1), SUBSTRING(@nullByteMap, byte, 1) % 2)
FROM @byteTable b
ORDER BY byte DESC
SELECT
SUBSTRING(@RowLogContents, 2 + 1, 2) AS lenFixedBytes,
SUBSTRING(@RowLogContents, @lenFixedBytes + 1, 2) AS noOfCols,
SUBSTRING(@RowLogContents, @lenFixedBytes + 3, @nullBitMapLength) AS nullByteMap,
SUBSTRING(@RowLogContents, @lenFixedBytes + 3 + @nullBitMapLength, 2) AS noVarCols,
SUBSTRING(@RowLogContents, @lenFixedBytes + 3 + @nullBitMapLength + 2, @noVarCols * 2) AS columnOffsetArray,
@lenFixedBytes + 2 + @nullBitMapLength + 2 + (@noVarCols * 2) AS varColStart
SELECT
@lenFixedBytes AS lenFixedBytes,
@noOfCols AS noOfCols,
@nullBitMapLength AS nullBitMapLength,
@nullByteMap AS nullByteMap,
@nullBitMap AS nullBitMap,
@noVarCols AS noVarCols,
@columnOffsetArray AS columnOffsetArray,
@varColPointer AS varColStart
DECLARE @colOffsetTable TABLE
(
colNum SMALLINT,
columnOffset VARBINARY(2),
columnOffvalue SMALLINT,
columnLength SMALLINT
)
SET @cnt = 1
WHILE (@cnt <= @noVarCols)
BEGIN
INSERT INTO @colOffsetTable(colNum, columnOffset, columnOffValue, columnLength)
VALUES(
@cnt * - 1,
SUBSTRING (@columnOffsetArray, (2 * @cnt) - 1, 2),
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING (@columnOffsetArray, (2 * @cnt) - 1, 2)))),
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING (@columnOffsetArray, (2 * @cnt) - 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING (@columnOffsetArray, (2 * (@cnt - 1)) - 1, 2)))), 0), @varColPointer)
)
SET @cnt = @cnt + 1
END
SELECT * FROM @colOffsetTable
SELECT
cols.leaf_null_bit AS nullbit,
ISNULL(syscolumns.length, cols.max_length) AS [length],
CASE
WHEN is_uniqueifier = 1 THEN 'UNIQUIFIER'
ELSE ISNULL(syscolumns.name, 'DROPPED')
END [name],
cols.system_type_id,
cols.leaf_bit_position AS bitpos,
ISNULL(syscolumns.xprec, cols.precision) AS xprec,
ISNULL(syscolumns.xscale, cols.scale) AS xscale,
cols.leaf_offset,
is_uniqueifier
FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id)
OR(allocunits.type = 2 AND partitions.partition_id =allocunits.container_id)
INNER JOIN sys.system_internals_partition_columns cols ON
cols.partition_id = partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id
AND syscolumns.colid = cols.partition_column_id
WHERE allocunits.allocation_unit_id = 72057594039828480
ORDER BY nullbit
DECLARE @schema TABLE
(
[column] INT,
[length] INT,
[name] NVARCHAR(255),
[system_type_id] INT,
[bitpos] INT,
[xprec] INT,
[xscale] INT,
[leaf_offset] INT,
[is_uniqueifier] BIT,
[is_null] BIT NULL
)
INSERT INTO @schema
SELECT
cols.leaf_null_bit AS nullbit,
ISNULL(syscolumns.length, cols.max_length) AS [length],
CASE
WHEN is_uniqueifier = 1 THEN 'UNIQUIFIER'
ELSE isnull(syscolumns.name, 'DROPPED')
END [name],
cols.system_type_id,
cols.leaf_bit_position AS bitpos,
ISNULL(syscolumns.xprec, cols.precision) AS xprec,
ISNULL(syscolumns.xscale, cols.scale) AS xscale,
cols.leaf_offset,
is_uniqueifier,
SUBSTRING(REVERSE(@nullBitMap), cols.leaf_null_bit, 1) AS is_null
FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id
AND syscolumns.colid = cols.partition_column_id
WHERE allocunits.allocation_unit_id = 72057594039828480
ORDER BY nullbit
INSERT INTO @schema
SELECT -3, 1, 'StatusBitsA', 0, 0, 0, 0, 2147483647, 0, 0
INSERT INTO @schema
SELECT -2, 1, 'StatusBitsB', 0, 0, 0, 0, 2147483647, 0, 0
INSERT INTO @schema
SELECT -1, 2, 'LenFixedBytes', 52, 0, 10, 0, 2147483647, 0, 0
SELECT
s.*,
CASE WHEN s.leaf_offset > 1 AND s.bitpos = 0 THEN
SUBSTRING
(
@RowLogContents,
ISNULL((SELECT TOP 1 SUM(x.length) FROM @schema x WHERE x.[column] < s.[column] AND x.leaf_offset > 1 AND x.bitpos = 0), 0) + 1,
s.length
)
ELSE
SUBSTRING
(
@RowLogContents,
(col.columnOffValue - col.columnLength) + 1,
col.columnLength
)
END AS hex_string
FROM @schema s
LEFT OUTER JOIN @colOffsetTable col ON col.colNum = (s.leaf_offset)
GO
마지막 결과셋을 보면 필드 속성정보 와 데이터도 hex정보로 출력 되는것을 확인 할 수 있습니다.
Optional_Data 필드의 hex값 문자열 출력
DECLARE @binary VARBINARY(100) = 0x536861726520616E6420456E6A6F79
SELECT CONVERT(VARCHAR(100),@binary)
결과값: Share and Enjoy
구조에 대한 분석이 훨씬 직관적일 것 같습니다.
참고: http://www.sqlservercentral.com/articles/Transaction+Log/71415/