xp_delete_file isn't deleting report logs

[Database Administrator]

Bev,

Have you seen this: - http://support.microsoft.com:80/kb/938085 ?

Chris

"Bev Kaufman" wrote in message
news:72E945B2-F875-47C7-80F9-4B8DF8CD00B9@microsoft.com...
> Management Studio version says 9.00.3042.00.
>
> "Chris Wood" wrote:
>
>> Sorry bev. I did not notice the reference to deleting the .BAK files
>> working.
>>
>> What build are you on?
>>
>> Chris
>>
>> "Bev Kaufman" wrote in message
>> news:4AEA0F3B-63FA-409B-845F-E6750586B553@microsoft.com...
>> > But my problem is the exact opposite. It deletes the BAK files but not
>> > the
>> > TXT. If xp_delete_file is undocumented and unpredicable, then I'd
>> > better
>> > write my own job and not rely on the maintenance plans for cleanup
>> > purposes.
>> >
>> > "Chris Wood" wrote:
>> >
>> >> Bev,
>> >>
>> >> From my own dealings with MS on this stored proc, internally it checks
>> >> if
>> >> you are a sysadmin. I am hoping that this restriction will be changed
>> >> in
>> >> the
>> >> future. My issue was with the actual backup file delete.
>> >>
>> >> My workaround was to raise an alert that fires a job, run by a
>> >> sysadmin,
>> >> to
>> >> delete the backup files. The maintenance Cleanup Task will allow you
>> >> to
>> >> delete report files. See if this will work for you.
>> >>
>> >> Chris
>> >>
>> >> "Bev Kaufman" wrote in message
>> >> news:6F1C60A2-53E6-4E03-AAB5-2F05AD7B46D5@microsoft.com...
>> >> > The report logs for my daily and weekly backups do not get deleted,
>> >> > even
>> >> > though I have Cleanup Maintenance tasks that specify the correct
>> >> > directory
>> >> > and extension. The old bak files do get deleted after the specified
>> >> > time.
>> >> >
>> >> > I have extracted the sql statements that run these commands:
>> >> > EXECUTE master.dbo.xp_delete_file
>> >> > 1,N'\\BIGPETE\E\data\Backups\Daily',N'txt',N'2007-08-27T11:14:53'
>> >> > EXECUTE master.dbo.xp_delete_file
>> >> > 0,N'\\BIGPETE\E\data\Backups\Daily',N'bak',N'2007-08-27T10:52:41',1
>> >> >
>> >> >
>> >> > The first statement doesn't work and the second one does. There is
>> >> > a
>> >> > difference in the first parameter, the extension (3rd parameter),
>> >> > and
>> >> > the
>> >> > second command has a fifth parameter while the first does not.
>> >> >
>> >> > In an attempt to figure out why one works and the other doesn't, I
>> >> > have
>> >> > also
>> >> > tried:
>> >> > EXECUTE master.dbo.xp_delete_file
>> >> > 0,N'\\BIGPETE\E\data\Backups\Daily',N'txt',N'2007-08-27T11:14:53'
>> >> > EXECUTE master.dbo.xp_delete_file
>> >> > 1,N'\\BIGPETE\E\data\Backups\Daily',N'txt',N'2007-08-27T11:14:53',1
>> >> > EXECUTE master.dbo.xp_delete_file
>> >> > 0,N'\\BIGPETE\E\data\Backups\Daily',N'txt',N'2007-08-27T11:14:53',1
>> >> >
>> >> > And of course I have checked the properties of the report files and
>> >> > manually
>> >> > deleted one of them to confirm that it's not a rights issue. And
>> >> > I've
>> >> > played
>> >> > with having the reports loaded into the same directory as the bak
>> >> > files.
>> >> > They still don't get deleted.
>> >> >
>> >> > So why does this procedure work on some files and not others? Is
>> >> > there
>> >> > any
>> >> > documentation on the parameters?
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

[Database Administrator]

I always have the problem of maintenance plan (SS 2005) not purging text
report files. I have confirmed it's a bug in SQL 2005. While it does delete
backup files property for the most time, it does show me one time that it
did not delete backup files based on a three day retention. These are all on
SS 2005 sp2.

Therefore, I've come up with my own script to deal with "deleting files",
appended it wherever appropriate. :-)


"Bev Kaufman" wrote in message
news:4AEA0F3B-63FA-409B-845F-E6750586B553@microsoft.com...
> But my problem is the exact opposite. It deletes the BAK files but not
> the
> TXT. If xp_delete_file is undocumented and unpredicable, then I'd better
> write my own job and not rely on the maintenance plans for cleanup
> purposes.
>
> "Chris Wood" wrote:
>
>> Bev,
>>
>> From my own dealings with MS on this stored proc, internally it checks if
>> you are a sysadmin. I am hoping that this restriction will be changed in
>> the
>> future. My issue was with the actual backup file delete.
>>
>> My workaround was to raise an alert that fires a job, run by a sysadmin,
>> to
>> delete the backup files. The maintenance Cleanup Task will allow you to
>> delete report files. See if this will work for you.
>>
>> Chris
>>
>> "Bev Kaufman" wrote in message
>> news:6F1C60A2-53E6-4E03-AAB5-2F05AD7B46D5@microsoft.com...
>> > The report logs for my daily and weekly backups do not get deleted,
>> > even
>> > though I have Cleanup Maintenance tasks that specify the correct
>> > directory
>> > and extension. The old bak files do get deleted after the specified
>> > time.
>> >
>> > I have extracted the sql statements that run these commands:
>> > EXECUTE master.dbo.xp_delete_file
>> > 1,N'\\BIGPETE\E\data\Backups\Daily',N'txt',N'2007-08-27T11:14:53'
>> > EXECUTE master.dbo.xp_delete_file
>> > 0,N'\\BIGPETE\E\data\Backups\Daily',N'bak',N'2007-08-27T10:52:41',1
>> >
>> >
>> > The first statement doesn't work and the second one does. There is a
>> > difference in the first parameter, the extension (3rd parameter), and
>> > the
>> > second command has a fifth parameter while the first does not.
>> >
>> > In an attempt to figure out why one works and the other doesn't, I have
>> > also
>> > tried:
>> > EXECUTE master.dbo.xp_delete_file
>> > 0,N'\\BIGPETE\E\data\Backups\Daily',N'txt',N'2007-08-27T11:14:53'
>> > EXECUTE master.dbo.xp_delete_file
>> > 1,N'\\BIGPETE\E\data\Backups\Daily',N'txt',N'2007-08-27T11:14:53',1
>> > EXECUTE master.dbo.xp_delete_file
>> > 0,N'\\BIGPETE\E\data\Backups\Daily',N'txt',N'2007-08-27T11:14:53',1
>> >
>> > And of course I have checked the properties of the report files and
>> > manually
>> > deleted one of them to confirm that it's not a rights issue. And I've
>> > played
>> > with having the reports loaded into the same directory as the bak
>> > files.
>> > They still don't get deleted.
>> >
>> > So why does this procedure work on some files and not others? Is there
>> > any
>> > documentation on the parameters?
>> >
>>
>>
>>

[triplebbb]

See this....

http://support.microsoft.com/kb/938085