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.