SQL transaction logs don't purge

[MacolaHelp]

This is a recent occurrence at one site. I have the maintenance plan set to purge the transaction logs after one week, but they don't. My data companies all have the mdf files going to the data folder & the bak going to the backup folder. These automatically purge after one week. The transaction log (ldf) is directed to the backup folder as are the transaction log backups. The trn files older than one week I am deleting manually. I can't see anything in the setup of the maintenance plan or the properties on the databases that would cause the failure to purge.

Databases that have the mdf & ldf files in the data folder (pwe & screens, for example) are not getting any trn files created. Any suggestions are welcome.

 

[johnCronin]

On the database properties select options and make sure recovery model is full and the "auto shrink" option is checked. I assume you are backing up the transaction log with the "BACKUP LOG" command.

 

[kscheeler]

You first want the database recovery model set to full. Then create a maintenance plan and for the transaction logs, check the box to delete them after X number of days. In our case, it's two days. Below is the sql statment in the transaction log backup job:

=============================================
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 965E0CF9-3223-4E56-A8FA-51BA2DA1C919 -Rpt "c:\MSSQL\LOG\Macola Live Co Maint Plan6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 2DAYS -CrBkSubDir -BkExt "TRN"'
=============================================

you can see towards the end of the statment the -DelBkUps switch is set to 2 days.

Hope this helps.

Kevin Scheeler