Read Log file in SQL Server

Scenario:

Usually, we will face log space issue in our SQL Server. Most of the time, we don't know the reason for our logfile space. I would suggest getting a third party tool to check it. By reading through some of the forums. I found there is some undocumented DBCC command which is used to dig the log file and get some information to us.

This command won't give very great information at least we can get some high level information. Let's see the command,

DBCC Log(Databasename, Option number)

Default option number is 0

For instance, 

dbcc log ('master',0) - 0 indicates a very minimal information.

The above command will provide the below details, 

1.gif

The above output won't give more information let's try with some other option values.
 
Now, am trying the option 1 
 
dbcc log ('master',1) - This will provide some moreinformation like log description, Log record length, log reserver etc., 

2.gif
  
Now, am trying the option 2 

dbcc log ('master',2) - This will provide some more information like PageId, slot ID, Lock details etc.., 

3.gif
  
Now, am trying the option 3

dbcc log('master',3) - This will provide lot of information to us like object name, database name, transaction details like transaction start time /end time/transaction id etc..,

4.gif

Now, am trying the option 4 

dbcc log ('master',4) - This provides very less information when compared to 1,2,3 option. It's just an enhanced version of option type 0.
 
5.gif

Up Next
    Ebook Download
    View all
    Learn
    View all