As a DBA you've gotta love Kimberly L. Tripp. Here is what she says.
8) Check/fix your internal fragmentation. OK, so this is the one thatreally helped the customer in Turkey. Often, when transaction logs arenot pre-allocated and/or when there's been a lot of autogrowths, thetransaction log can become internally fragmented. Internally yourtransaction logs are broken down into smaller more granular chunkscalled VLFs (Virtual Log Files). The size and number of VLFs you'llhave depends largely on the size that the chunk is when it's added toyou transaction log. If you add a new chunk to the transaction logwhich is 20MB (through autogrowth or through manual growth) then thenumber of VLFs that are added is 4. If you add a chunk which is greaterthan 64MB but less than or equal to 1GB, you'll add 8 VLFs. If you addmore than 1GB then you'll add 16VLFs. In general, most transaction logswill only have 20 or 30 VLFs – even 50 could be reasonable depending onthe total size of the transaction log. However, in many cases whathappens is that excessive autogrowths can cause an excessive number ofVLFs to be added – sometimes resulting in hundreds of VLFs. Having anexcessive number of VLFs can negatively impact all transaction logrelated activities and you may even see degradation in performance whentransaction log backups occur. To see how many VLFs you have solelylook at the number of rows returned by DBCC LOGINFO. The number of rowsreturned equals the number of VLFs your transaction log file has. Ifyou have more than 50, I would recommend fixing it and adjusting yourautogrowth so that it doesn't occur as fequently. To get rid of all ofthe execessive VLFs, follow these easy steps to shrink off thefragmented chunk and add a new, clean chunk to your transaction log:
1. Wait for an inactive time of day (ideally, it would be best toput the database into single user mode first) and then clear alltransaction log activity through a regular transaction log backup. Ifyou're using the simple recovery model then you don't need to do a logbackup… Instead, just clear the transaction log by running acheckpoint.
BACKUP LOG databasename TO devicename
2. Shrink the log to as small a size as possible (truncateonly)
DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)
NOTE: if you don't know the logical filename of your transaction log use sp_helpfile to list all of your database files.
3. Alter the database to modify the transaction log file to the appropriate size – in one step
ALTER DATABASE databasename
MODIFY FILE
(
NAME = transactionloglogicalfilename
, SIZE = newtotalsize
)
And, that should do it!
–klt
Given that, here is some of the code you might use to optimize your database log file internals. (VLF). I use my own sp__who2 but you just want to see who's in the database. The main feature of this script is it calculates the ALTER DATABASE log file recreate script — as mentioned above — so when you're done all the previous file growths have been beautifully reduced to this one growth. Instead of CHECKPOINT on FULL recovery databases you'll need to run a LOG backup.
SELECT 'USE ' + DB_NAME() + ';' + CHAR(13)+CHAR(10) +
'EXEC sp__who2 1,null,null,' + DB_NAME() + ';' + CHAR(13)+CHAR(10) +
'SELECT DATABASEPROPERTYEX(''' + DB_NAME() + ''',''Recovery'');' + CHAR(13)+CHAR(10) +
'DBCC LOGINFO;' + CHAR(13)+CHAR(10) +
'CHECKPOINT;' + CHAR(13)+CHAR(10) +
'DBCC SHRINKFILE(' + RTRIM(name) + ', TRUNCATEONLY);' + CHAR(13)+CHAR(10) +
'ALTER DATABASE ' + DB_NAME() + ' MODIFY FILE (NAME = ' + RTRIM(name) +
', SIZE = ' + CAST(size/1024*8 AS VARCHAR(25)) + ');'+ CHAR(13)+CHAR(10) +
'DBCC LOGINFO;'
FROM dbo.sysfiles
WHERE FILEPROPERTY(name,'IsLogFile') = 1