January 13, 2009 at 9:12 pm
Hi,
I've got a bizarre issue with some of our maintenance plans which is driving me nuts - I can't figure it out - and I'm hoping someone(s) on here may be able to shed some light.
I'm going to be talking about the xp_delete_file stored proc in this post - I know you're not meant to execute this directly (which I'm doing while troubleshooting), but all of this is an attempt to solve Maintenance Plan Cleanup Tasks not working properly, which execute xp_delete_file to delete old backup files.
Scenario:
Database Server running 4 instances of SQL:
Default Instance - SQL 2008, v10.0.1600
Named Instance A - SQL 2008, v10.0.1600
Named Instance B - SQL 2008, v10.0.1600
Named Instance C - SQL 2005 SP2, v9.0.3068
All run very similar Maintenance Plans for back-ups, consisting of regular Full, Diff & Transaction Log backups to a shared folder on another server. The backup Maintenance Plans all include a Cleanup Task to delete backups older than a certain number of weeks based on file extension (.bak, .bakd and .trn).
The remote network share used for backup files is accessed via DFS (e.g \\domain.name\backups\sqlbackups), and all 4 instances share the same shared folder, with seperate sub-folders for each instance and database.
The original issue was that the Cleanup Task steps for 3 of the 4 instances were taking a long time to execute (e.g. 10 - 300 minutes), and then didn't do what they were supposed to do (i.e. no old backup files were deleted). Oddly, however, the Cleanup Tasks for 1 of the instances - the Default instance - execute completely normally.
I looked into things further and the delay is coming from the xp_delete_file call(s) being made to delete the old backup files - every other part of the Maintenance Plan executes fine, but the xp_delete_file stored proc appears to be spending 30-60 seconds processing each matching file (i.e. for all .bak files, 30-60 seconds per .bak file in the target folder), hence the delay, and then completes without error but without deleting any files.
To try and troubleshoot the root cause of the problem I have been running the xp_delete_file command to delete older .bak files from one of the failing Cleanup Tasks in SSMS with the following variations - none of which have worked:
1) Changed the target folder to bypass DFS and use the FQDN of the server (e.g. \\server.domain.name\sqlbackups\ instead of \\domain.name\backups\sqlbackups)
2) Changed the target folder to bypass DFS and use the short name of the server (e.g. \\server\sqlbackups\ instead of \\domain.name\backups\sqlbackups)
3) Changed the target folder to a local folder (e.g. folder on database server's local disk with some .bak files copied to simulate old backups)
4) Gave SQL instance's service account Full Control rights on target folder (both when trying network share & local disk folder)
5) Gave SQL instance's service account Domain Admins right (yes, I was getting desperate!)
6) Gave SQL instance's service account Administrator rights on the database server
7) Tried various combinations of file extensions, cut-off dates for deletion and whether to include sub-folders or not
None of this worked 🙁
I also:
1) Watched open files on the network share used for backups while executing xp_delete_file, and I can see the SQL instance's service account accessing each .bak file in turn for 30-60 seconds (opening it in "Read" mode) before moving on to the next file - so I don't think it's a permissions issue.
2) Tried to find permission / setting differences between the Default Instance (where xp_delete_file works) and the other instances, but to no avail - the only significant difference I can find is the fact that the working one is the Default Instance whereas the non-working ones are all Named instances.
Does anyone have any ideas on what may be causing this problem? I'm out of ideas, and also can't explain why one instance would work when the others don't 🙁
Thanks for any suggestions / help provided,
Matt
March 5, 2009 at 7:03 am
I had a problem with the cleanup not purging my .bak and .trn files. I STILL have a problem with it not deleting the .txt files that it creates during the plan.
Look to see if you have the . before the bak and trn. If you do, get rid of it - that is what solved my problem with those.
I'm still however trying to figure out why the .txt files are not purging.
I hope this works for you.
March 5, 2009 at 2:23 pm
Leslie I. Cairns (3/5/2009)
Look to see if you have the . before the bak and trn. If you do, get rid of it - that is what solved my problem with those.I'm still however trying to figure out why the .txt files are not purging.
I hope this works for you.
Thanks from the reply.
That wasn't the problem unfortunately - I found other references to the . before the filename and tried various combinations - none worked.
I never did manage to solve this problem despite trying everything I could think of - in the end I worked around it by setting up a batch file that deletes files based on a file extension & age-in-days combination, and running the script via Task Scheduler on our backup-to-disk server to cleanse the old backup files.
Not as good as solving the problem, but it works.
March 5, 2009 at 2:26 pm
Sorry it didn't help.
May I ask how you did the script file to delete files aged > certain date? Maybe I could use that to get rid of my pesky .txt files.
Leslie
March 5, 2009 at 2:44 pm
Leslie I. Cairns (3/5/2009)
Sorry it didn't help.
No probs, I appreciate that you took the time to post anyway.
May I ask how you did the script file to delete files aged > certain date? Maybe I could use that to get rid of my pesky .txt files
Sure - here is the script I put together. This works for us under Windows 2008, I think it will work fine in Windows 2003 too (I think the "forfiles" command was introduced in W2003):
delete-files.cmd
@echo off
rem File deletion script
rem
rem Usage: delete-files (path to search (including subfolders)) (file pattern to delete) (age of last file to keep, in days) (deletion logfile path and filename)
rem
rem e.g. delete-files C:\SQLBackups *.trn 7 E:\DeletionLogs\sql-delete-20090306.txt
echo Starting deletion pass at %date% %time% > %4
forfiles -p %1 -s -m %2 -d -%3 -c "cmd /c echo Deleting @path & del /q @path" >> %4
echo. >> %4
echo Deletion pass ended at %date% %time% >> %4
I then call this scripts multiple times from another batch file (which is the one actually run by the Task Scheduler, e.g.:
cleanup-sql-instancemain.cmd
@echo off
rem Script to clean-up old backups for Main SQL instance
rem Get current date and time
FOR /F "TOKENS=1,2 DELIMS=/ " %%A IN ('DATE /T') DO SET dd=%%B
FOR /F "TOKENS=2,3 DELIMS=/ " %%A IN ('DATE /T') DO SET mm=%%B
FOR /F "TOKENS=3* DELIMS=/ " %%A IN ('DATE /T') DO SET yyyy=%%B
rem Correct for extra space at end of year token
set yyyy=%yyyy:~0,4%
SET datenow=%yyyy%%mm%%dd%
rem Delete transaction logs older than 1 week
call delete-files C:\SQLBackups\General *.trn 8 "E:\DeletionLogs\Cleanup-SQL_Main_TRN-%datenow%.txt"
rem Delete differential backups older than 4 weeks
call delete-files C:\SQLBackups\General *.bakd 29 "E:\DeletionLogs\Cleanup-SQL_Main_BAKD-%datenow%.txt"
rem Delete full backups older than 8 weeks
call delete-files C:\SQLBackups\General *.bak 57 "E:\DeletionLogs\Cleanup-SQL_Main_BAK-%datenow%.txt"
Hope that helps!
--
Cheers,
Matt
March 6, 2009 at 7:23 am
Thanks so much, Matt. I didn't know about the forfiles command. My server is running 2003, but my desktop is running 2000 - maybe that's why I didn't find it.
I hope you find an answer to your problem.
Leslie
March 6, 2009 at 7:48 am
Leslie,
The History Cleanup task should get those .txt files. The Maintenance cleanup task only works with backups. I prefer doing things like Mike is with a script since it gives me more flexibility. If you can move to PowerShell, I'd do that. Otherwise, there are some good VBScripts around to manage files as well.
Mike,
Strange issue. I don't think it's permissions. Could that instance be lower on memory somehow? Shouldn't matter for Agent, but I'm reaching here. If you execute xp_delete_file through that instance, is it slower than others?
March 6, 2009 at 8:29 am
Steve,
Thanks for your comments.
I had the assumption that the History Cleanup was only to purge the information from the msdb and the Maintenance Cleanup was to purge the actual files. If the main. plan isn't supposed to purge the text files, what is the reason for the choice of Delete files of the following type 1) Backup or 2) Maintenance Plan Text Reports?
I've thought over the past month that it's some type of bug. I think I will try out Matt's scripts - it probably is the way to go.
Leslie
March 9, 2009 at 5:05 pm
Leslie I. Cairns (3/6/2009)
Thanks so much, Matt. I didn't know about the forfiles command. My server is running 2003, but my desktop is running 2000 - maybe that's why I didn't find it.I hope you find an answer to your problem.
Not a problem. I've pretty much given up on finding an answer to my problem - it's not often a software issue completely stumps me, but this one has, and my script-based workaround is doing it's job.
Steve Jones
Strange issue.
You're telling me :-S
I don't think it's permissions.
I agree - I thought it was permissions originally, but after I tried backing up to local disk and had the same problem I think that most likely rules out a permissions problem.
Could that instance be lower on memory somehow? Shouldn't matter for Agent, but I'm reaching here.
The three instances with the problem all have much lower memory than the one that works, but I've played with memory allocations and had no luck. To give you an idea, the server has 4GB of RAM, the default instance (that works fine) has an upper memory cap of 2.5GB, and the remaining 1.5GB is shared by the other 3 instances (the ones affected) and the OS. 1.5Gb should be plenty for the other 3 instances - they're all very low, and don't generally consume all the RAM available to them.
If you execute xp_delete_file through that instance, is it slower than others?
Do you mean executing xp_delete_file through the 3 instances affected? If so, yes, whenever I run xp_delete_file (manually) it exhibits the incredible slowness / failure to actually delete files.
Cheers,
Matt
April 13, 2009 at 3:35 pm
This solved my issue for the TXT files only
April 13, 2009 at 3:55 pm
Strange. I might recommend that you grab the file and process tools from sysinternals and use them to watch what happens when you execute xp_deletefile. I wonder if there are some other accesses taking place.
I assume that if you issued a "del file.txt' from xp_cmdshell (should use the same permissions as the service) that it would work OK?
April 15, 2009 at 11:26 am
Leslie I. Cairns (3/6/2009)
...what is the reason for the choice of Delete files of the following type 1) Backup or 2) Maintenance Plan Text Reports?
The purpose of that option is to specify what header the xp_delete_file command is supposed to look for in the file before deleting it. Unfortunately xp_delete_file is not able to delete files that are not SQL backup files. It actually searches each BAK, TRN, etc. for a header that specifies it was a file created by SQL. If you try to delete a specific file that is not a SQL backup or maintenance plan report file, you will get an error message that says you are not allowed to delete non-SQL files.
May 13, 2009 at 1:13 am
I run profiler to see what the "xp_delete_file" is triying to do and I noticed an error "CREATE DATABASE permission denied in database 'master'."!!
The account that the sql server service is running under need permissions to create tables in the "master" database. After giving sysadmin role to the account that my instance was running under, it started working.
Yasir
December 10, 2010 at 4:33 pm
THANKS! This worked for me.
July 13, 2015 at 7:26 am
Very old topic, but this was a bug fixed with SQL 2008 R2 CU11 / SQL Server 2012 SP 1 Cumulative Update 7 (see https://social.msdn.microsoft.com/Forums/en-US/e6b1a3c6-9405-482d-b4ef-5c788f14cd8f/xpdeletefile-extremely-slow-on-a-server-waitinfo-preemptiveosgetprocaddress?forum=sqldatabaseengine).
Regardless of this fix I'm very thankful for the script above because we have an SQL2008 server without SP3 (for compatibility reasons) where could use it.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply