SQL Server Performance Forum – Threads Archive

Maintenance Cleanup Task not deleting

Hi, I’m having problems getting the Maintenance Cleanup Task to delete files. I’m on a 2 node cluster and my maintenance plan is putting my full backups files into the s:mssqlackup folder nightly. I want to create a cleanup task that deletes bak files that are older that 2 days, but for some reason the Maintenance Cleanup Task isn’t working properly. So far, I have tried the following: * Applied SP2 and all hotfixes for SP2.
* Confirmed that the local Administrators group, on both nodes, has full access to the s:mssqlackup folder.
* Confirmed that the domain user account that is used by the SQL Server service is a member of the local Administrators account on both nodes.
* Tried using both a manually created maintenance plan and a plan created by the maintenance plan wizard to create the cleanup — neither works.
* Copied the t-sql statement out of the Maintenance Cleanup Task and tried executing it in a new query window…no errors. it says it is successful, but the files still exist. Here is the statement: EXECUTE master.dbo.xp_delete_file 0,N’S:MSSQLBackup’,N’bak’,N’2007-06-30T13:42:43′ At this point, I really at my wit’s end and could use some expert help. Any ideas on other things I could check? Thanks a bunch, Eric
[B)]

Just run master..xp-cmdshell ‘dir N’S:MSSQLBackup’ and also run delete one single file using xp-cmdshell through Query Window and see what you get.. Note: Why ‘T’ in your datetime value? remove it… MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks for replying, MohammedU. I had xp_cmdshell disabled, but enabled it in the Surface Area Configuration. When I "dir S:MSSQLBackup" through it, it successfully returned the directory listing…so I know that it can "see" the files. I tried executing the xp_delete_file stored proc again, (without the "T"), and it still won’t delete the files: EXECUTE master.dbo.xp_delete_file 0,N’S:MSSQLBackup’,N’bak’,N’2007-06-30 13:42:43′ Created a new Maintenance Cleanup Task to try to delete a specific file. Here is the t-sql it generates: EXECUTE master.dbo.xp_delete_file 0,N’S:MSSQLBackupFE_001_000_USAZ_backup_200707012007.bak’ However, when I execute it it gives me the following error: ==============================================================
Date7/2/2007 5:04:19 PM
LogJob History (Cleanup – Old BAK files.Subplan_1) Step ID1
ServerMSSQL
Job NameCleanup – Old BAK files.Subplan_1
Step NameSubplan_1
Duration00:00:02
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0 Message
Executed as user: MAWCOLOservice_sql_server. …2.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:04:19 PM Progress: 2007-07-02 17:04:20.49 Source: {3890D994-EADC-4692-9236-354C0F58184C} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Error: 2007-07-02 17:04:20.60 Code: 0xC002F210 Source: Maintenance Cleanup Task Execute SQL Task Description: Executing the query "EXECUTE master.dbo.xp_delete_file 0,N’S:MSSQLBackupFE_001_000_USAZ_backup_200707012007.bak’ " failed with the following error: "Error executing xp_delete_file extended stored procedure: Specified file is not a SQL Server backup file.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:0… The package execution fa… The step failed. ==============================================================

Try using that XP without specifying .BAK extension when running as a job.
Also note that MP will not delete the files from the sub-folders, you have to write your own code to do so. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

The designer wouldn’t let me click "ok" without specifying a file extension, so I copied its sql to a new query window and manually remoted the N’bak’ from it. It still didn’t work:
EXECUTE master.dbo.xp_delete_file 0,N’S:MSSQLBackup’,N”,N’2007-07-02 07:58:55′ — << replaced N’bak’ with N” I saw that others were having problems with subdirectories early on, so I have all of my bak and trn files going into the s:mssqlackup directory. This is really frustrating because I never had this problem on SQL 2000 — it was such a nice feature. =p I have a production environment that is going live soon and I’ve got to find a solution for this. Can anyone recommend a third-party tool or something that can cleanup files based on their dates?

Better not to manage from Maintenance plan, you could write a scheduled job to drop those files using normal command line options, this time you have tomention the extension to delete. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Ok, thanks Satya. Eric

Is your directory is NTFS compressed?
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

If so I doubt the backup will not be succeeded as NTFS compression is not supported [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

]]>