Problems happen when you have fragmentation in the t-log. You can find this by running DBCC LOGINFO. This usually happens when autogrow kicks in and the database is set at the defaults… which are very small.
In the ERRORLOG, you start to see this:
Database x has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.
More info here:
http://support.microsoft.com/kb/2653893
To get rid of it (from the above link):
If you have more than 50, I would recommend fixing it and adjusting your autogrowth so that it doesn’t occur as fequently. To get rid of all of the execessive VLFs, follow these easy steps to shrink off the fragmented chunk and add a new, clean chunk to your transaction log:
1. Wait for an inactive time of day (ideally, it would be best to put the database into single user mode first) and then clear all transaction log activity through a regular transaction log backup. If you’re using the simple recovery model then you don’t need to do a log backup… Instead, just clear the transaction log by running a checkpoint.
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
)
Mission Critical Computing 