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 . Microsoft Data Platform MVP

"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.