Database Maintenance Plans - Not removing Files Older than x days

I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
database on the server and have created a database maintenance plan that
backups up all the database nightly. I have also configured the maintenance
plan to delete back files older than 3 days old but this isn't currently
happening.
I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
seen this problem first. Or if anyone can confirm SP4 fixes this issue.
You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
SP4 on another system to make sure your apps work correctly first, of course.
Have you checked the various logs SQL Server uses? There's a SQL Server Log,
a SQL Server Agent Log, and the Windows Application Event Log. They might
point out what is happening.
That being said, there is an extended stored procedure that Microsoft uses
to delete the files. If the Agent service doesn't have the proper rights to
the subdirectory you might find that to be the issue.
"Gary S" wrote:

> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>
|||What is the extended store procedure that's used so that I can check it
hasn't been removed.
"Buck Woody - Microsoft SQL Server Team" wrote:
[vbcol=seagreen]
> You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> SP4 on another system to make sure your apps work correctly first, of course.
> Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> a SQL Server Agent Log, and the Windows Application Event Log. They might
> point out what is happening.
> That being said, there is an extended stored procedure that Microsoft uses
> to delete the files. If the Agent service doesn't have the proper rights to
> the subdirectory you might find that to be the issue.
> "Gary S" wrote:
|||It's sp_delete_file - and it's undocumented, meaning unsupported for direct
use by users. Check this thread:
https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
"Gary S" wrote:
[vbcol=seagreen]
> What is the extended store procedure that's used so that I can check it
> hasn't been removed.
> "Buck Woody - Microsoft SQL Server Team" wrote:
|||Slight correction - that's XP_delete_file. Had an S in there!
"Buck Woody - Microsoft SQL Server Team" wrote:
[vbcol=seagreen]
> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
|||On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
There are a couple of common causes for this:
1. The maintenance plan is configured to do transaction log backups,
but one of the databases included is in Simple mode. The plan fails
when it hits this database, and never reaches the "cleanup" step.
2. One of the files to be deleted is "in use" by another process,
such as a tape backup, causing the cleanup process to fail.
Right-click on the maintenance plan itself (not the Agent job), look
at the history, you should be able to get an actual error message from
there.
|||You have to be a sysadmin to run this stored proc, even in SP2 CTP. I have
been told a fix will be raised for a later service pack.
Chris
"Buck Woody - Microsoft SQL Server Team"
<BuckWoodyMicrosoftSQLServerTeam@.discussions.micro soft.com> wrote in message
news:0751636B-0F16-40EA-BAE8-8ADE57217570@.microsoft.com...[vbcol=seagreen]
> It's sp_delete_file - and it's undocumented, meaning unsupported for
> direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
|||Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
big fan of database mainatenance plans (perfer setting up the jobs
individual) so as I'm not 100% certain why the databases are set to simple
I'll create seperate backup jobs.
"Tracy McKibben" wrote:

> On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> There are a couple of common causes for this:
> 1. The maintenance plan is configured to do transaction log backups,
> but one of the databases included is in Simple mode. The plan fails
> when it hits this database, and never reaches the "cleanup" step.
> 2. One of the files to be deleted is "in use" by another process,
> such as a tape backup, causing the cleanup process to fail.
> Right-click on the maintenance plan itself (not the Agent job), look
> at the history, you should be able to get an actual error message from
> there.
>
|||On Feb 15, 5:13 am, Gary S <G...@.discussions.microsoft.com> wrote:
> Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
> big fan of database mainatenance plans (perfer setting up the jobs
> individual) so as I'm not 100% certain why the databases are set to simple
> I'll create seperate backup jobs.
>
Here's a script of mine that might help you out:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html