Passionately curious about Data, Databases and Systems Complexity. Data is ubiquitous, the database universe is dichotomous (structured and unstructured), expanding and complex. Find my Database Research at SQLToolkit.co.uk

"The important thing is not to stop questioning. Curiosity has its own reason for existing" Einstein

Tuesday, 2 June 2009

Active transaction log DBCC log command

This undocumented command is used to view the transaction log

DBCC log ( {dbid|dbname}, [, type={-1|0|1|2|3|4}] )

Dbid or dbname - Enter either the dbid or the name of the database
in question.

type - is the type of output:

0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length, description)
2 - very detailed information (plus object name, index name,
page id, slot id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump
of the current transaction log's row.
-1 - full information about each operation plus hexadecimal dump
of the current transaction log's row, plus Checkpoint Begin,
DB Version, Max XDESID

by default type = 0

Here is sample data output

dbcc log (testdatabase, 2)

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,Number of Locks,Lock Information,Description 00000017:000001e3:0001,LOP_BEGIN_CKPT,LCX_NULL,0000:00000000,0x0000,96,96,00000017:000001d4:0001,0x0000,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 00000017:000001e4:0001,LOP_END_CKPT,LCX_NULL,0000:00000000,0x0000,136,136,00000017:000001e3:0001,0x0000,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 00000017:000001ed:0001,LOP_BEGIN_XACT,LCX_NULL,0000:000003f2,0x0000,48,92,00000000:00000000:0000,0x0200,NULL,NULL,NULL,NULL,NULL,NULL,NULL,user_transaction;0x01 00000017:000001ed:0002,LOP_INSERT_ROWS,LCX_HEAP,0000:000003f2,0x0000,62,104,00000017:000001ed:0001,0x1200,72057594043695104,dbo.tblmixednumbertext,0001:0000005d,10,00000017:000001e8:0003,3,ACQUIRE_LOCK_IX OBJECT: 10:21575115:0 ;ACQUIRE_LOCK_IX PAGE: 10:1:93;ACQUIRE_LOCK_X RID: 10:1:93:10, 00000017:000001ed:0003,LOP_COMMIT_XACT,LCX_NULL,0000:000003f2,0x0000,48,52,00000017:000001ed:0001,0x0200,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

No comments:

Post a Comment

Note: only a member of this blog may post a comment.