Exploring the SQL Server log with DBCC Loginfo

The SQL Server log is one of the major components of the database. This post explores what happens in the log during normal (and abnormal) operation. The tool that I use to explore the log is DBCC Loginfo. This is a tool to display information about the virtual log files (VLFs) within the physical log file.

DBCC LogInfo is described here:

http://sqlserverpedia.com/wiki/Maintenance_DBCC_Commands

First – to setup the workshop, I open SQL Server Management Studio. I then create a database, logtest, in full recovery. I then create a table, test.

create database LogTest;

ALTER DATABASE [LogTest] SET RECOVERY FULL WITH NO_WAIT

use Logtest

go

create table test (col1 int, col2 varchar(50))

At this point, there has been no back-ups of any sort. If I new execute DBCC LogInfo:

RecoveryUnitId	FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
0	2	262144	8192	33	2	64	0
0	2	262144	270336	0	0	0	0
0	2	262144	532480	0	0	0	0
0	2	270336	794624	0	0	0	0

Table 1  – VLFs after creating the database

In the above table, there are four VLFs. The first one has a status = 2 which indicates that it cannot be re-used, the others are available for use (status = 0).

I now insert rows into the test table as follows:

Insert into test (col1,col2) values (3456,’this is a test of the log use’)

go 500

and run DBCC Loginfo – the results are shown below.

RecoveryUnitId	FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
0	2	262144	8192	37	0	128	0
0	2	262144	270336	38	2	128	0
0	2	262144	532480	35	0	64	0
0	2	270336	794624	36	0	64	0

Table 2 – VLFs after inserting data

Table 2 above shows that VLFs are moving. However, at this point, the VLFs are simply moving – there is no possibility that the database can use the log to restore as once a VLF has been used it is re-cycled. this is because the database has not been backed up. If I now back up the database –
BACKUP DATABASE [LogTest]
TO  DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2\MSSQL\Backup\LogTest.bak’

and re-run the SQL to insert data, the logfile begins to behave very differently. If I run DBCC LogInfo,

RecoveryUnitId	FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
0	2	262144	8192	37	0	128	0
0	2	262144	270336	38	2	128	0
0	2	262144	532480	39	2	128	0
0	2	270336	794624	40	2	128	0
0	2	311296	1064960	41	2	64	4E+16

Table 3 – VLFs after taking a database backup

Table 3 shows that VLFs are no longer being re-used; instead, the database has added additional VLFs to the log to allow recovery of any transactions that have been run since the database was backed up.

I now take a log backup of the database –

BACKUP LOG [LogTest]
TO  DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2\MSSQL\Backup\LogTest.bak’

and examine the contents of the log file – DBCC LogInfo

RecoveryUnitId	FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
0	2	262144	8192	37	0	128	0
0	2	262144	270336	38	0	128	0
0	2	262144	532480	39	0	128	0
0	2	270336	794624	40	0	128	0
0	2	311296	1064960	41	2	64	4E+16

Table 4 – the log after a log backup

Table 4 shows that the VLFs have been marked as available for re-use (the status of all but the last VLF is =0).

The above sequence of commands should illustrate the behaviour of the log after a new database is created and data is added. Until a full backup has been taken, transactional information is not retained in the transaction log.

I will now describe the behaviour of the log under an unusual situation – after a log truncate. This is not an operation that should ever be attempted on a production system, and Microsoft have made it difficult to apply. The log truncation option was depreciated and is no longer available in SQL Server 2012. So it is no longer possible to run a Backup Log with truncate_only. In a SQL Server 2012 database, to truncate the log, I will switch the database to SIMPLE recovery.

First – using DBCC LogInfo, the log before the switch looks like this

RecoveryUnitId	FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
0	2	262144	8192	42	2	64	0
0	2	262144	270336	43	2	64	0
0	2	262144	532480	39	0	128	0
0	2	270336	794624	40	0	128	0
0	2	311296	1064960	41	2	64	4E+16

Table 5 – the log before truncating

Incidentally, Table 5 this shows the circular nature of the log file – where possible, VLFs have been re-used.

I now switch the database to SIMPLE –

ALTER DATABASE [LogTest] SET RECOVERY SIMPLE WITH NO_WAIT

and then check the log –

RecoveryUnitId	FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
0	2	262144	8192	42	0	64	0
0	2	262144	270336	43	2	64	0
0	2	262144	532480	39	0	128	0
0	2	270336	794624	40	0	128	0
0	2	311296	1064960	41	0	64	4E+16

Table 6 – the log after truncation

Table 6 (above) shows that all transactions in the log file have been lost, and the log has been – in effect – re-initialised in a way comparable to the situation where the database log was backed up. However, the critical difference is that there has been no back up of the log – the transactions have been lost.