6

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?

  • No, since the log file grows every time, what did you gain by shrinking it? Being able to say you had some free space for a while? Rent that space out short term? Just leave the log file as big as it needs to be (if you can't improve the things that are causing it to grow in the first place). Shrinking just to grow again is a waste of everyone's time, and can actually make things worse. – Aaron Bertrand Dec 13 '15 at 17:17
3

But, when this job runs, the log file of MSDB grows large (20 - 40 GB).

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 :

-- select top size as per batch to delete
-- since you are in simple recovery, perform `CHECKPOINT`
-- repeat until all the delete is complete.

Refer to my answer here for the script.

Should we enable "Auto shrink" or create a job to shrink the log file?

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?

0

I strongly recommend not to enable "Auto shrink" feature. since it will cause the following harm:

1) take a lot of resources on file system will level start fragmentation and other tasks. as well as high CPU usage and I/O since since it will start pagination.

2) its not controllable.

3) it may harm your database index.


how to avoid this log file growing?

1) Convert the Recovery Model to Simple Recovery

2) Start Taking Transaction Log Backup

  • 2
    How is shrinking a log file going to cause fragmentation or harm an index? – Aaron Bertrand Dec 13 '15 at 17:23
  • @AaronBertrand "auto shrink" is database parameter. that runs periodically to detect when the database has more than 25% free disk space allocated to it. The database will be shrunk leaving it with 25% free disk space while the rest of the previously allocated disk space is released back to the Windows operating system. also it require a index defrag – Ahmad Abuhasna Dec 14 '15 at 16:33
  • Yes, thank you for that, however the OP is having issues with their MSDB log file specifically, and are looking for a resolution to that problem. – Aaron Bertrand Dec 14 '15 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.