Looking into the DBCC LOGINFO and DBCC SQLPERF commands

In this post I would like to talk a bit about Transaction Log,its various parts and its architecture. I will not go into details about what Transaction Log is because it is well documented elsewhere. I would like also to highlight a few important points. Ι will provide hands-on examples for the DBCC LOGINFO and DBCC SQLPERF commands.

I have been helping out a friend to understand various important issues regarding Transaction Logs in SQL Server databases.The transaction log records all the transactions and the modifications on the database those transactions have. Usually you have one transaction log file per database but you can have multiple files.The default size of a transaction log (if not specified) is the 25% of the total size of all data files created for the database.It is always a good idea to have the size of the transaction log planned beforehand.Bear in mind that the transaction log is zero initialized and that is a costly operation in both time and resources.The transaction log is divided into smaller parts that are called VLFs (Virtual Log Files). It is easier to manage the transaction log like that.  You have a active and inactive VLFs . One VLF must be active at any time in the database. You cannot configure the number and the size of the VLFs in the transaction log. As transaction log grows, new VLFs are created and their size is determined by SQL Server. There is a formula that determines the number and the size of the VLFs. If the transaction log grows by 64 MBytes, 4 new VLFs are created each 1/4 of the growth size.If the transaction log grows by 64 MBytes to 1Gbytes, 8 new VLFs are created each 1/8 of the growth size.If the transaction log grows by  1Gbytes or more, 16 new VLFs are created each 1/16 of the growth size.Also bear in mind that each VLF is uniquely identified by a sequence number.

Let’s have a look at the actual hands-on demos.

I will be using SQL Server 2012 Εnterprise  edition in this post but feel free to use SQL Server 2008/R2 versions and any other edition you might have installed (Standard, Express e.t.c).

1) Launch SSMS and connect to the local (or any other instance) of SQL Server

2) I will be looking firstly at the DBCC LOGINFO command which provides us with important information about the structure of the transaction log.

3) In a new query window in SSMS , type (or copy paste) the following t-sql script and then execute it

USE MASTER;
GO

CREATE DATABASE [Football] ON  PRIMARY
( NAME = N'Football_Data', FILENAME = N'D:\DATA\Football_Data.mdf' , SIZE = 180MB , MAXSIZE = UNLIMITED, FILEGROWTH = 20MB )
LOG ON
( NAME = N'Football_Log', FILENAME = N'D:\DATA\Football_log.ldf' , SIZE = 40MB , MAXSIZE = UNLIMITED, FILEGROWTH = 20MB)
GO

DBCC LOGINFO(N'Football');
GO

Let me explain what I do in this example. I create a database Football.Then I run the DBCC LOGINFO(N’Football’) to understand a bit about the log structure and the number of VLFs

Have a look at the photo below

As you can see there are 4 VLFs.Each one of them is 10 Mbytes.So we can see that the formula applies in this case. The Log file size is 40 Mbytes so we expect to have 4 VLFs.

4) Now let’s have a look at the DBCC SQLPERF(LOGSPACE) command. This command provides transaction log space usage statistics for all databases. Have a look

In a new query window type and execute the following T-Sql command.

USE MASTER;
GO

DBCC SQLPERF(LOGSPACE);
GO

Have a look at the picture below

5) Now I am going to increase the size of the transaction log by inserting sample data in the database and then check again the number and the size of the VLFs in the transaction log. I will also check the transaction log file size and the part of it that is used.

Type (copy and paste) the following T-SQL script. Execute it

USE FOOTBALL;
GO

ALTER DATABASE Football MODIFY FILE (NAME =N'Football_Log',SIZE=60MB);

DBCC LOGINFO(N'Football');
GO

DBCC SQLPERF(LOGSPACE);
GO

6) We add more space in the transaction log and then execute the DBCC LOGINFO and DBCC SQLPERF commands. Have a look at the picture below

We can see now that the size of the transaction log is more than 64Mbytes we have 8 VLFs

7) Now in a new query window type (copy paste) and execute the following script.

USE FOOTBALL;
GO

CREATE TABLE Footballer
(
FootballerID int identity(1,1),
Footballer_No varchar(20),
Footballer_lastname nvarchar(50),
Footballer_firstname nvarchar(50),
Footballer_middlename nvarchar(50)
)
GO

CREATE UNIQUE CLUSTERED INDEX cx_Foo_IX ON Footballer (FootballerID)
GO

declare @CTR INT
declare @ctrstr varchar(7)
select @ctr=0
WHILE @CTR<100.000
BEGIN
select @ctr=@ctr+1
select @ctrstr=Str(@ctr)
insert footballer(Footballer_No ,Footballer_lastname,Footballer_firstname, Footballer_middlename) values('F_No'+@ctrstr,'Footlname'+@ctrstr,'Footfname'+@ctrstr,'Footmname'+@ctrstr)
END

SELECT * FROM Footballer;
GO

I create a table in the database.Then I create a clustered index and insert 100.000 rows in it. That will increase the size of the used space in the transaction log.

Type and execute the T-SQL code below.

DBCC LOGINFO(N'Football');
GO


DBCC SQLPERF(LOGSPACE);
GO

Have a look at the picture below to see results I have got when I run the T-SQL code above.

Hope you learned about transaction log architecture and the importance of DBCC LOGINFO and DBCC SQLPERF commands.

Hope it helps!!!!