Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My Adventures as a SQL Server DBA
Browse by Tags · View All
BRH 16
#SQL Server 5
SQL server 4
realviews 3
sqlserver tools and utilities 3
sql server tools in my environment 3
TSQL 3
#DBA 3
audit 2
tools 2

Archive · View All
April 2011 4
March 2011 3
February 2011 3
November 2010 3
August 2010 2
May 2011 2
September 2010 2
May 2010 1
December 2011 1
July 2011 1

Reading the Transaction Log

Nov 22 2010 9:00AM by Malathi Mahadevan   

It is fairly known to most long term DBAs that DBCC log and ::fn_dblog function are undocumented commands used to read the transaction log. What I learnt in the PASS conference this time was that undocumented  - means unsupported AND not used in any white paper or reference material by Microsoft. (This information was given by Kalen Delaney, originally I believed undocumented only meant commands not available in books online but apparently not just so). I am just re documenting these two commands

Method 1:
DBCC Log –The syntax is as below

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

0 - Default with minimum information

1 - Information provided for 0 + flags, tags, and row length.

2 - More information than the 1 option that includes page ID and slot ID.

3 - Information about the logged event.

4 - Information about the logged event plus the hexadecimal dump.

-1 - Option 4 including Checkpoint Begin, DB Version, and Max XACTID.


The best possible usage of this command possibly is to find out if a specific LSN/transaction exists within the log get an idea of what it did. Some of the commonly known values of the field ‘operation’
Some of the possible values for 'operation', by far the most useful field
LOP_BEGIN_XACT  - Beginning of transaction
LOP_BEGIN_CKPT  - Beginning checkpoint
OP_XACT_CKPT    - End of Checkpoint
LOP_MODIFY_ROW  - Row modified
LOP_INSERT_ROW  - Row inserted
LOP_DELETE_SPLIT - Rows that are moved as a result of page split.
LOP_COMMIT_XACT  - Commit Transaction

Method 2: 

The ::fn_dblog function, which seems like a wrapper around DBCC log and returns the same results as dbcc log. Since it is an UDF it is easier to manipulate than the DBCC command.
Select * from ::fn_dblog(null, null) – the two parameters refer to start and end lsn, the default of null, null dumps the contents of the entire log.
Reading the log requires thorough knowledge of how it stores and handles every transaction. It may be interesting to play with but may be easier also to buy tools such as the log reader that comes with Quest Toad for SQL Server or Lumigent log explorer.

It is however interesting to know of these commands and how even those of us with minimal knowledge of internals can use them/perhaps experiment with them and understand more of how the transaction log works. Below are two well documented and most useful usage of these commands.

Most Useful Blog Posts
http://www.sqlskills.com/BLOGS/PAUL/post/Finding-out-who-dropped-a-table-using-the-transaction-log.aspx
http://sqlblogcasts.com/blogs/leopasta/archive/2007/08/13/page-splits.aspx

 

Tags: BRH, #SQL Server, TSQL, SQL server,


Malathi Mahadevan
74 · 2% · 772
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]