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.
