We have a SQL Server 2008 cluster with 60 databases.
Full backup runs every night, log backup runs every 15 minutes.
The MSDB
database recovery model is set to simple (default).
To be able to see the history of these jobs, we had to setup a job cleaning the history log.
But, when this job runs, the log file of MSDB
grows large (20 – 40 GB).
We regularly shrink the log file, as it has "free space" well over 80 %
The log file is set to grow as needed and no "Auto shrink"
Is there any way we can avoid this log file growing?
Should we enable "Auto shrink" or create a job to shrink the log file?
Best Answer
The problem might be that the job that you are running is not cleaning up msdb in batches. A pseudo code will be as follows :
Refer to my answer here for the script.
No. This is a bad practice. A one time shrink is OK. Presize your data and log files and adjust your autogrowth.
Refer to this answer - Why Does the Transaction Log Keep Growing or Run Out of Space?