Too many VLFs will cause to
slow down the operations on the database.
As the Virtual log files
grows, the time taken to read the log file will also grow.
So watch on the virtual log
files on your databases.
Analyse
Count of VLFs can be either pulled using
DBCC LOGINFO
OR
Try to shrink the log file and
it will provide you the number of files
DBCC SHRINKFILE ('MSDBLOG') -- Check the "UsedPages" value from the
resultset.
Too many virtual log files can be seen in one among the below cases
1) When there is no transaction
log backup and the database is in Full Recovery Model
2) Where the database is
acting as a replication Publisher and is either in Full or Simple Recovery
model. Frequent periodic T-log backups are still recommended
3) where there is a large /
older Open transaction on a Database
Solution:-
1) Try to take periodic
regular T-log backups
2) Issue Checkpoint
3) Shrink the log file
Try to apply the proceedure in
the given series and mostlikely issuing a continous checkpoint will also help when
the database is in Simple Recovery model.
Hope it helps
...Happy SQLing /
Thanks for Visiting and Sharing your Views
