Virtual logfile (VLF) hell…

At one point in my career I inherited a database that had suffered from neglect (to be kind).  The logfiles had be set to autogrow 1MB at a time, and this had happened ALOT! Interestingly this rather small database was taking a surprisingly long time to restore, Knowing that a high number (>1000) of VLFs can cause insert/update and restore performance penalties, I decided to check the VLF counts across the instance, since I had not done so to that point:


DECLARE @SQLtext varchar(500),
 @dbname varchar(500),
 @count int

SET NOCOUNT ON

DECLARE crsr CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT name
FROM sys.databases

CREATE TABLE ##tblinfo
(
 dbname varchar(100),
 row_count int)

OPEN crsr

FETCH NEXT FROM crsr INTO @dbname

WHILE (@@fetch_status <> -1)
BEGIN

CREATE TABLE #log_info
(
 RecoveryUnitId tinyint,
 fileid tinyint,
 file_size bigint,
 start_offset bigint,
 FSeqNo int,
[status] tinyint,
 parity tinyint,
 create_lsn numeric(25,0)
)

SET @SQLtext = 'DBCC loginfo (' + '''' + @dbname + ''') '

INSERT INTO #log_info
EXEC (@SQLText)

SET @count = @@rowcount

DROP TABLE #log_info

INSERT INTO ##tblinfo
VALUES(@dbname, @count)

FETCH NEXT FROM crsr INTO @dbname

END

CLOSE crsr
DEALLOCATE crsr

SELECT dbname,
 row_count
FROM ##tblinfo
ORDER BY dbname

DROP TABLE ##tblinfo

After running this, I was able to identify the problem database and resolve the issue by shrinking the current TLOG file and setting the starting size and autogrow to more appropriate sizes.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s