View Log file information in SQL Server

Scenario:

Usually, we will face log space issue in our SQL Server. Most of the time, we dont know the reason for our logfile space. I would suggest to get 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 a 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,

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

Now, am trying the option 2

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

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

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.

Cheers,

Venkatesan Prabu .J