Some insights to the transactionlog

When managing your Microsoft SQL Server and its databases the transaction log is really important. If you fail set the log to appropriate size, and potential autogrowth to adecvate size you will jeopardize the integrity and function of the database. You need to have a view of how the log is constructed. On monday, if you’re close by the Microsoft HQ in Kista I’m talking about the log, and some about the do’s and don’t’s about the log.

Alot of the demos will use DBCC LOGINFO(), an undocumented good dbcc feature that exposes great intel to the logfile. the transaction log file is just a wrapper for Virtual Log Files, VLFs. They are created based on a calculation of the size the log grows. When the growth chunk is up to 64MB there are four (4) VLFs, up to 1GB there are eight (8) VLFs, and larger than 1GB there are sixteen (16) VLFs. You want to have them tuned based on your database need.

Another important thing that is worth mentioning is that the growth of the log cannot utilize the Instant File Initialization as the new chunk of the log must be zeroed out.

There is also a bug around log growth, if the chunk of growth are a multiple of 4GB (4096MB/4196304kB/4294967296B, the first autogrowth will fail and just create a small chunk, the second will work. 4000MB is a better choice than 4096MB or 4GB. This goes for 8GB, 12GB, 16GB and so on as well.

You must also remember that when the log grows all changes to the database is locked out as the file is being zeroed out. Test out the most optimal size based on your environment.

So, if you want some insight on your environment this script will get all user-databases and dump the info about the transaction logs into a table variable.

USE master 

DECLARE @loginfo TABLE 

 ( 

 db sysname NULL, -- The name of the database 

 RecoverUnitId numeric(34,0), -- The Recover Unit ID 

 FileID numeric(34,0), -- The File ID of the transaction log 

 FileSize numeric(34,0), -- The size of the VLF 

 StartOffset numeric(34,0), -- Where in the log file is the VLF starting 

 FSeqNo numeric(34,0), -- Sequencial ID for the VLFs 

 Status numeric(34,0), -- 0 = not in use, 2 = contains transactions 

 Parity numeric(34,0), -- 0 = not in use, 64/128 = contains transactions/current transactions 

 CreateLSN numeric(34,0) -- The Log Sequence Number when the VLF was created 

 ) 

DECLARE 

 @db sysname, 

 @stmt nvarchar(max) = N'DBCC LOGINFO(''{db}'')', 

 @exec nvarchar(max) 

DECLARE loop CURSOR 

 FOR 

 SELECT name FROM sys.databases 

 WHERE name NOT IN ('master','msdb','tempdb','model') 

OPEN loop 

FETCH NEXT FROM loop INTO @db 

WHILE @@FETCH_STATUS = 0 

 BEGIN 

 SET @exec = REPLACE(@stmt, N'{db}', @db) 

 INSERT INTO @loginfo

 (

 RecoverUnitId, 

 FileID, 

 FileSize, 

 StartOffset, 

 FSeqNo, 

 Status, 

 Parity, 

 CreateLSN 

) 

 EXEC(@exec) 

 UPDATE @loginfo 

 SET db = @db 

 WHERE db IS NULL 

 FETCH NEXT FROM loop INTO @db 

 END 

CLOSE loop 

DEALLOCATE loop 

SELECT * FROM @loginfo ORDER BY db ASC, StartOffset ASC