Some insights to the transactionlog

Oopps! Upgrade your browser pretty please.Oopps! Upgrade your browser pretty please.

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