December 6, 2007 at 10:25 am
My last two posts have gone unanswered but I'm going to try again.
Server SQL2005 Standard SP2.
I have a cleanup step in my Maintenance Plan to delete the backup report files. I give it all the correct information, it reports successful but it is not actually deleting the files.
Even if I take the TSQL
EXECUTE master.sys.xp_delete_file 0,N'L:\SQLNightlyReports',N'txt',N'2007-11-22T10:00:04',1
and run it from a query, it completes successfully but the files are still there. Running under my account which is a domain admin, it's not a permission issue.
Any help would be appreciated.
Thanks
Kelsey
December 6, 2007 at 11:40 am
December 9, 2007 at 7:05 pm
It's also an annoying bug and I haven't seen a fix for it yet. I just schedule a SQL Agent job to run a vbs script (you could use Windows Task Scheduler if you wanted).
Script:
' Script created by Scott Duncan, 04-10-2007
' This script was created to delete SQL maintenance plan log
' files, as SQL 2005 SP2 changed the format of the files,
' causing xp_delete_file to fail to delete them
' Setup environment & declare variables
Set objFSO = CreateObject("Scripting.FileSystemObject")
' One variable per folder to be deleted
Dim Folder1, Folder2
' Folders the log files are in
' Add as many folders here as you have, declare the variables above
Folder1 = "C:\0\SQL\Scratch\logs"
Folder2 = "C:\0\SQL\Scratch\logs2"
' call the subroutine once for each folder [variable]
subDeleteOldFiles Folder1
subDeleteOldFiles Folder2
Private Sub subDeleteOldFiles (FolderName)
Dim intSpaceLocation, intDateDif, dtmDate, intMaxAge
Set objFolder = objFSO.GetFolder(FolderName)
Set objFiles = objFolder.Files
' set maximum age a file can be in days
intMaxAge = 7
For Each File in objFiles
' File.DateLastModified returns date & time, separated by a space.
' We want just the date, so we need to find where the separating space is
intSpaceLocation = InStr(1, File.DateLastModified, " ", 1)
' now we grab all characters from left to right until we hit the space
' if the DateLastModified field happens to be date only (no time), then just
' grab the whole DateLastModified field
if intSpaceLocation > 0 then
dtmDate = Left(File.DateLastModified, (intSpaceLocation - 1))
else
dtmDate = File.DateLastModified
end if
' now we work out the difference in days between date modified & today
intDateDif = DateDiff("d", dtmDate, Date)
' if the difference is greater than or equal to the maximum age allowed,
' we delete the file.
if intDateDif >= intMaxAge then
objFSO.DeleteFile(objFolder.Path & "\" & File.Name)
end if
Next
End Sub
Save with extension .vbs.
If running from SQL Agent, set the step to CmdExec & run 'cscript [path to vbs file] //b //nologo'. Make sure the account running SQL Agent has permission to get to the path you put the vbs file in (I put it in the SQL Server LOG folder myself, easy to find, no need to muck about with additional permissions).
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
December 10, 2007 at 8:25 am
You can fix it with the following hotfix:
http://support.microsoft.com/kb/936305/
I have applied it and it works fine.
December 10, 2007 at 7:11 pm
Missed that. Guess I shouldn't have been looking in the KB article for a reference to the fix. 😛
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply