SQLServerWiki

“The Only Thing That Is Constant Is Change”

Reading the transaction log backup.

Posted by database-wiki on March 28, 2011

1. Create a database tester. (Take a full backup of it.)

CREATE

 DATABASE [tester] ON PRIMARY

(

 NAME = N’tester’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\tester.mdf’ , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB
)

LOG ON

(

 NAME = N’tester_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\tester_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10
%)

GO

2. Create a table emp in the tester database.

use tester
go

create table emp

(

empid

int not
null,

empname

varchar(10) not
null
)

3. Insert some rows into the table emp.

use tester
go

Declare @i int

set @i=1

while (@i<=100)
begin
insert into emp (empid,empname)
select @i,@i

set @i=@i+1
end
4. See if the rows are present in the emp table.

select count(*) from emp

5. Take a transaction log backup. In my case the backup file name is t2.trn. Then run the following query by provide the transaction log backup name with complete path.

use tester
go

select object_name(substring(replace([lock information],’ACQUIRE_LOCK_IX OBJECT: 0:’,”),1,charindex(‘:’,replace([lock information],’ACQUIRE_LOCK_IX OBJECT: 0:’,”))-1 ))as ‘Object’,* from sys.fn_dump_dblog(NULL,NULL,NULL,1,’C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Backup\t2.trn’, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
where [lock information] like ‘ACQUIRE_LOCK_IX OBJECT%’

 OUTPUT: ( Partial )

Object Current LSN Operation Context Transaction ID Tag Bits Log Record Fixed Length Log Record Length Previous LSN Flag Bits AllocUnitId AllocUnitName Page ID Slot ID Previous Page LSN PartitionId RowFlags Num Elements Offset in Row Checkpoint Begin CHKPT Begin DB Version Max XDESID Num Transactions Checkpoint End CHKPT End DB Version Minimum LSN Dirty Pages Oldest Replicated Begin LSN Next Replicated End LSN Last Distributed Backup End LSN Last Distributed End LSN Server UID UID SPID Beginlog Status Begin Time Transaction Name Transaction SID End Time Transaction Begin Replicated Records Oldest Active LSN Server Name Database Name Mark Name Master XDESID Master DBID Preplog Begin LSN Prepare Time Virtual Clock Previous Savepoint Savepoint Name Rowbits First Bit Rowbits Bit Count Rowbits Bit Value Number of Locks Lock Information LSN before writes Pages Written Data Pages Delta Reserved Pages Delta Used Pages Delta Data Rows Delta Command Type Publication ID Article ID Partial Status Command Byte Offset New Value Old Value New Split Page Rows Deleted Bytes Freed CI Table Id CI Index Id FileGroup ID Meta Status File Status File ID Physical Name Logical Name Format LSN RowsetId TextPtr Column Offset Flags Text Size Offset Old Size New Size Description RowLog Contents 0 RowLog Contents 1 RowLog Contents 2 RowLog Contents 3 RowLog Contents 4 Log Record

——————————————————————————————————————————– ———————– ——————————- ——————————- ————– ——– ———————– —————– ———————– ——— ——————– —————————————————————————————————————————————————————————————————————————————————————- ————– ———– ———————– ——————– ——– ———— ————- ———————— ———————- ————– —————- ———————— ——————– ———————– ———– ————————— ———————– ——————————- ———————— ———– ———– ———– ————— ———————— ——————————— —————————————————————————————————————————————————————————- ———————— ———————– —————— ———————– ——————————————————————————————————————————— ——————————————————————————————————————————— ——————————— ————– ———– ———————– ———————— ——————– ———————– ——————————— —————– —————– —————– ————— —————————————————————————————————————————————————————————————————————————————————————- ———————– ————- —————- ——————– —————- ——————– ———— ————– ———– ————– ————————– ———– ——— ——— ————– ———— ———– ———– ———– ———— ———– ———– ——- —————————————————————————————————————————————————————————————————————————————————————- ——————————————————————————————————————————— ———————– ——————– ———————————- ————- ———– ——————– ——————– ——————– ——————– —————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————— —————————————————————————————————————————————————————————————————————————————————————— —————————————————————————————————————————————————————————————————————————————————————— —————————————————————————————————————————————————————————————————————————————————————— —————————————————————————————————————————————————————————————————————————————————————— ——————————————————————————————————————————————————————————————————————————————————————

sysdbfiles 00000012:0000010c:0003 LOP_MODIFY_COLUMNS LCX_CLUSTERED 0000:0000025f 0x0000 62 228 00000012:0000010c:0002 0x0200 281474981691392 0001:00000055 0 00000012:000000c6:0002 281474981691392 0 12 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2 ACQUIRE_LOCK_IX OBJECT: 0:76:0 ;ACQUIRE_LOCK_X KEY: 0:281474981691392 (0100f3476122) NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0x460046008600860090009000A000A000 0x0100090010000600 0x160000000001000000020000 0x0101000C00004C00000000000204000A0100F3476122 0x04 0x00003E00120000000C010000020002005F0200000000060255000000010000004C00000012000000C60000000200010000004C00000001000000000000000C00100008000C00160001000100090009001000100006000600460046008600860090009000A000A00001000900100006001600000000010000000200000101000C

emp 00000012:00000112:0003 LOP_INSERT_ROWS LCX_HEAP 0000:00000260 0x0000 62 108 00000012:00000112:0001 0x1200 72057594042318848 0001:00000098 0 00000012:000000b0:0065 72057594038321152 65 3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 ACQUIRE_LOCK_IX OBJECT: 0:2073058421:0 ;ACQUIRE_LOCK_IX PAGE: 0:1:152;ACQUIRE_LOCK_X RID: 0:1:152:0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0x30000800010000000200FC0100100031 0x 0x0101000C0000755C907B00000102000402030004 0x 0x 0x00003E00120000001201000001001200600200000000020198000000010000004300000012000000B00000006500000100000600000000010000000041000300100000001400000030000800010000000200FC01001000310101000C0000755C907B00000102000402030004

emp 00000012:00000113:0002 LOP_INSERT_ROWS LCX_HEAP 0000:00000261 0x0000 62 108 00000012:00000113:0001 0x1200 72057594042318848 0001:00000098 1 00000012:00000112:0003 72057594038321152 65 3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 ACQUIRE_LOCK_IX OBJECT: 0:2073058421:0 ;ACQUIRE_LOCK_IX PAGE: 0:1:152;ACQUIRE_LOCK_X RID: 0:1:152:1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0x30000800020000000200FC0100100032 0x 0x0101000C0000755C907B00000102000402030004 0x 0x 0x00003E00120000001301000001001200610200000000020198000000010001004300000012000000120100000300000100000600000000010000000041000300100000001400120030000800020000000200FC01001000320101000C0000755C907B00000102000402030004

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: