Failed With The Following Error Xp_delete_file Returned Error 2
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Asked by: SQL 2005 Maintenance Cleanup Task is not deleting files on remote file share SQL Server > SQL Server High Availability and Disaster Recovery Question 0 Sign in to vote I have the following issue with Maintenance plan backups that work for BAK DIF and TRN to a remote server share. When I try and remove the old files with a clean up task I get an error and the files don't get deleted. The version is as followsMicrosoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) The error result is as follows, Failed-1073548784) Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'\\\\EXECUTE master.dbo.xp_delete_file 0,N'\\\ABCD-A1\\BACKUPS\\ABCD_BACKUP\\ABC_DAILY\\ABCD',N'trn',N'2008-01-13T12:52:49'" failed with the following error: "xp_delete_file() returned error 2, 'The system cannot find the file specified.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.The maintenance plan seems to be adding extra "\" though when i enter the code directly in a query i get same error. Query: EXECUTE master.dbo.xp_delete_file 0,N'\\ABCD-A1\BACKUPS\ABCD_BACKUP\ABC_DAILY\ABCD',N'trn',N'2008-01-13T12:52:49'
Error: xp_delete_file() returned error 2, 'The system cannot find the file specified.' The servers belong to the same domain and are using the same Service account which has all the necessary rights to the share and the file directory location. The backups work but i get the error on the cleanup task. Trying to figure out how toRecent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2008 » SQL Server 2008 Administration » Error when executing a Job under the... Error when executing a Job under the maintainence plan Rate Topic Display Mode Topic Options Author Message mhdmehrajmhdmehraj Posted Tuesday, February 22, 2011 3:00 AM Forum Newbie Group: General Forum Members Last Login: Friday, April 13, 2012 4:15 AM Points: 6, Visits: 22 Iam trying this in sql server 2008.I have created the maintaince plan using the below script.--*-- Script generated on https://social.msdn.microsoft.com/Forums/sqlserver/en-US/510a46fb-035f-4ff4-8eeb-667cdee4d4f8/sql-2005-maintenance-cleanup-task-is-not-deleting-files-on-remote-file-share?forum=sqldisasterrecovery 3/15/2007 10:28 AM-- Optimizations JobBEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'LDR Database Maintenance') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'LDR Database Maintenance' -- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'Optimizations Job for LDR Maintenance Plan') IF (@JobID IS NOT NULL) BEGIN http://www.sqlservercentral.com/Forums/Topic1067471-1550-1.aspx -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- There is, so abort the script RAISERROR (N'Unable to import job ''Optimizations Job for LDR Maintenance Plan'' since there is already a multi-server job with this name.', 16, 1) GOTO QuitWithRollback END ELSE -- Delete the [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N'Optimizations Job for LDR Maintenance Plan' SELECT @JobID = NULL END BEGIN -- Add the job EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Optimizations Job for LDR Maintenance Plan', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'LDR Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job steps EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'EXECUTE master.dbo.xp_sqlmaint N''-Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\LDR Maintenance Plan0.txt" -DelTxtRpt 2WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 2048 10 ''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 4, @retry_attempts = 0, @retry_interval = 0, @output_file
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more http://stackoverflow.com/questions/5327282/sql-maintenance-cleanup-task-working-but-not-deleting about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users http://serverfault.com/questions/475896/windows-7-sql-server-2012-maintenanace-cleanup-task Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL Maintenance Cleanup Task Working but Not Deleting up vote 18 down vote favorite 2 I have a Maintenance Plan that is suppose to go through the BACKUP failed with folder and remove all .bak older than 5 days. When I run the job, it gives me a success message but older .bak files are still present. I've tried the step at the following question: http://serverfault.com/questions/245493/sql-maintenance-cleanup-task-success-but-not-deleting-files Result is column IsDamaged = 0 I've verified with the following question and this is not my issue: http://serverfault.com/questions/94094/maintenance-cleanup-tasks-running-successfully-but-not-deleting-back-up-files I've also tried deleting the Job and Maintenance Plan and recreating, but to no avail. Any ideas? sql-server sql-server-2008 maintenance-plan share|improve this failed with the question asked Mar 16 '11 at 15:07 Alex 52641127 add a comment| 10 Answers 10 active oldest votes up vote 27 down vote Try these checks: Use *.* for the file extension or bak without a dot, both of which I have found work if other issues are correct too. Make sure that the path is simply the path to where your backups are but with a backslash on the end. Check that verify is ticked when you create the back up in the first place. share|improve this answer edited Jan 14 at 21:07 Grant 7,052113246 answered Jul 25 '11 at 7:40 sturb 27133 9 using extension bak is what worked for me. No dots and asterisks –jao Mar 26 '13 at 13:02 We had this issue -- changed the path to end in "\" and also changed the file spec to ".". That worked. –Matt Dawdy Dec 22 '14 at 21:02 @jao this solution worked for me as well. –Dragonsdoom Apr 6 at 17:03 . is THE key.... ack! Thanks! –Jonesome Sep 1 at 22:17 add a comment| up vote 3 down vote This is often caused by permission problems. The cleanup task doesn't seem to log anything helpful when permissions prevent the account under which the step is running from deleting files. You can verify this as follows: In SQL
Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Server Fault Questions Tags Users Badges Unanswered Ask Question _ Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top windows 7 - sql server 2012 - Maintenanace Cleanup Task up vote 0 down vote favorite My Team Foundation Server creates backups. I want to clear backups that are older than 5 days. Create Maintenanace Cleanup Task in Maintenance Plans of Management of Sql Server 2012. Point there folder, file extension bak, checkbox 'Delete files based on the age of the file at task run time' is checked. Saved task. When I execute it, got error: Execution failed. See the maintenance plan and SQL Server Agent job history logs for details. In Sql Server Agent job history: Message Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 7:31:58 Progress: 2013-02-05 07:31:58.66 Source: {7F5A7CED-005D-4A73-B4D0-B09CF3B45934} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2013-02-05 07:31:58.71 Code: 0xC002F210 Source: Maintenance Cleanup Task Execute SQL Task Description: Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'C:\Users\ypa..." failed with the following error: "xp_delete_file() returned error 2, 'Can not find the file specified'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Warning: 2013-02-05 07:31:58.71 Code: 0x80019002 Source: Subplan_2 Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:31:58 Finished: 7:31:58 Elapsed: 0.687 seconds. The package execution failed. The step failed. Can not fin