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