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?

marc_s
  • 9,052
  • 6
  • 46
  • 52
Lars
  • 61
  • 1
  • 2

2 Answers2

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?

Community
  • 1
Kin Shah
  • 62,545
  • 6
  • 124
  • 245
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

Ahmad Abuhasna
  • 2,718
  • 4
  • 25
  • 36