Is your Database having Too many VLFs? (Tuning Log File Auto Growth doesnt help ?)

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