SQL Server does not remove "stale" backup files. 2

[DeepDiverMom]

My problem: I have specified to SQL Server in the installation's Maintenance plan to remove database-backup files that are older than 24 hours. (This directive works fine on two of my four SQL Server installations.) On two of the servers, SQL Server never deletes files as I have requested. I must remove files manually to avoid filling disk.

I have compared, directive by directive, the maintenance plan specifications of each of my four servers, but I cannot identify what is causing two of my four installations to ignore the "Remove files older than 1 Day(s)" setting.

Here are my Database-Maintenance-Plan specifications (from Enterprise Manager):

General tab: Databases: "All databases"
Optimizations tab:
Reorganize data and index pages (Checked): Change free space per page percentage to 10%.
Remove unused space from database files (Checked).
Shrink database when it grows beyond: 50MB
Amount of free space to remain after shrink: 10% of the data space
Schedule: Occurs every 1 week(s) on Sunday at 1:00:00 a.m.
Integrity tab:
Check database integrity (Checked)
Include indexes: Attempt to repair any minor problems
Perform these tests before backing up the database or transaction log.
Schedule: Occurs every 1 week(s) on Sunday at 12:00:00 a.m.
Complete Backup tab:
Back up the database as part of the maintenance plan (Checked)
Verify the integrity of the backup upon completion
Disk: Use this directory: D:\MSSQL7\BACKUP
Create a sub-directory for each database (Checked)
Remove files older than: 1 Day(s)
Backup file extension: BAK
Schedule: Occurs every 1 day(s), at 9:45:00 PM.
Transaction Log Backup: No operations selected.
Reporting:
History on this server:
Write history to the table msdb.dbo.sysdbmaintplan_history (Checked)
Limit rows in the table to (Checked): 1000 rows for this plan.

What could be causing the SQL Server to fail to remove the backup files?

Thanks,

Becca

 

[DebiJo]

There's been a bit of discussion on this before. I'm not sure why it doesn't work all the time. I don't take a chance, I have a job that runs robocopy to delete them from the scheduler. It's a simple dos script if you want it just let me know.

Debi

 

[DeepDiverMom]

Debi,

Thanks so much for tossing me a life preserver. I'd love to have your script. Is it intuitive or do I need instructions, as well?

Thanks so much for your help,

Becca

 

[mrdenny]

sqlmaint.exe (which is run for the maintenance plans) is a pain. It often doesn't remove files for unknown reasons. The basic problems comes down to if there is any little problem it won't delete the old backups. There could be a file missing somewhere that you didn't know needed to be there.

Check the log for the maintenance plan. It may have more info.

I would recommend using robocopy to get rid of the files.

I would also recommend not using the maintenance plan at all, and instead using dbcc which gives you better control of what needs to be done.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[DeepDiverMom]

Debi and Denny,

Thanks so much for your responses. It is sad that the behavior of SQL Server's Maintenance Plans is so unreliable. In the face of this circumstance, I shall follow your advice to use some other mechanism at least to get rid of stale backup files.

Debi, I don't want to impose, but do you have that script handy? My boss is eager for me to resolve this problem at our facilities.

Denny, have you samples of the code you recommend that I use to get rid of stale backup files?

Thanks again,

Becca

 

[DeepDiverMom]

Debi...Denny...Anyone?...Anyone?

Who can help me out with a robocopy script to remove SQL Server backup files older than 24 hours?

Thanks,

Becca

 

[DebiJo]

Robocopy is part of the resource kit.... This one deletes after 7 days.. The robocopy moves them to the temp directory, then the DEL actually deletes them.

ROBOCOPY "\\dbserver\d$\mssql\backup" "\\dbserver\d$\mssql\backup\temp" *.bak /move /minage:7

ROBOCOPY "\\dbserver\d$\mssql\backup" "\\dbserver\d$\mssql\backup\temp" *.trn /move /minage:7

DEL "\\dbserver\d$\mssql\backup\temp\*.*" /q 2>nul

Good luck!
Debi

 

[DeepDiverMom]

Debi,

Thanks so much for your help. I'm sure that my implementing your scripts will take care of business for me.

Please forgive my lack of knowledge about "robocopy", but is Robocopy available with resource kits earlier than Windows2003. If not, will the Robocopy from Windows2003's resource kit function properly on other Win versions? My need is most accute for SQL Servers on Win2K and WinNT.

Please have a star for your helpful posts so far.

Becca

 

[mrdenny]

robocopy has been around for years. since nt4 days I believe. Yes the one from the 2003 resource kit will work back on 2k and nt.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[DeepDiverMom]

Thanks, Denny...A star for your collection.

Becca

 

[maswien]

<quote denny> I would also recommend not using the maintenance plan at all, and instead using dbcc which gives you better control of what needs to be done. </quote>

Denny, do you write maintenance job using dbcc? or regularly check everything manually? Thanks

 

[mrdenny]

I write jobs that run during my down time windows using dbcc.

(Use [ and ] not < and > for the TGML commands.)

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)