1

A job is backing up an LDF with the following command:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID <the id> -WriteHistory  -VrfyBackup -BkUpMedia DISK -BkUpLog "\\server\sqlbak$" -DelBkUps 6HOURS -BkExt "TRN"'

I would like the job to truncate the LDF after backing it up.

I have looked at the xp_sqlmaint documentation and it seems that the flag -RmUnusedSpace may do the trick, but I'm not sure as it seems to only remove unused space rather than truncating.

I read that using dbcc shrinkfile('logfile' , destination size) can break the backup chain, as you cannot then backup to a specific point in time.

How can I go about truncating the LDF after the backup?

rhughes
  • 325
  • 3
  • 5
  • 14

2 Answers2

4

How can I go about truncating the LDF after the backup?

Don't. A maintenance plan has no business truncating files, LDF nor MDF. There is no point to shrink something only to have it grown back overnite, at (huge) runtime cost. If the file is large, it means it has to be large. Truncating files is just another way of asking for downtime because it gives a false impressions of free disk, where the disk is reclaimed back during normal operations, but potentially can fail (grinding everything to a halt) because the needed space was meanwhile taken by other files.

So just don't.

On special occasions after the file has grown due to a one-time incident, a manual one-time truncation of LDF is OK. But not as part of regular maintenance, that is absolutely not OK.

Remus Rusanu
  • 52,054
  • 4
  • 96
  • 172
2

It is much easier to modify maintenance plans directly than try to add additional flags within the SQL Agent job step. As well the flag you are referring to is regarding the space within the database itself, not just the physical log file. It is intended to shrink or release the unused space to the specified percentage. Not something you should be doing if you don't have to.

Log truncation occurs upon completion of a full or log backup, or whenever SQL Server issues a checkpoint. You can read more on that process specific to SQL Server 2000 here. If you are trying to keep the log file under size due to disk space issues you need to increase the frequency of log backups that are occurring. If that is not something that can be done you either need to look at adding additional drive space, or find out specifically what is causing the log file to grow out of control.

Shrinking the log file does not cause any breaks in the backup chain. It is however not something that is advised to automate. If the log file is growing out of control so much that it requires you to constantly shrink it down then all you are doing is covering up the bigger problem. You need to determine what is causing the log growth and take corrective action from there.