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?

Improve this question
1
  • 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
    Commented Dec 13, 2015 at 17:17

2 Answers 2

Reset to default
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?

Improve this answer
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

Improve this answer
3
  • 2
    How is shrinking a log file going to cause fragmentation or harm an index?
    – Aaron Bertrand
    Commented Dec 13, 2015 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
    Commented Dec 14, 2015 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
    Commented Dec 14, 2015 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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