maintenance plan does not delete old .bak files

I have a maintenance plan that backs up our DB every night. It's supposed t
o delete any .bak files older that a day. However, it does not delete the o
ld .bak files and after a couple of nights, the backups fail because the dis
k is out of space.
Why don't the old .bak files delete like they're supposed to?Are you trying to perform log backup? Is there a database targeted which is
in simple recovery mode? If sop,
sqlmaint will not remove old files for any database in the group.
Below KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"KimL" <kiml@.colonialsavings.com> wrote in message news:303A4869-240C-4051-9DFA-4CCAB1F78C9F
@.microsoft.com...
> I have a maintenance plan that backs up our DB every night. It's supposed to dele
te any .bak files older
that a day. However, it does not delete the old .bak files and after a coup
le of nights, the backups fail
because the disk is out of space.
> Why don't the old .bak files delete like they're supposed to?|||I found the answer....it was a problem between the chair and the keyboard.
I was examining the steps in the maintenance job and discovered that I had
selected "every one WEEK" instead of "every one day"
It seems that just the very act of posting my problem here led me to the sol
ution.
thanks for your suggestions

Maintenance Plan does not delete old .bak files

I am using the maintenance cleanup task wizard to delete old .bak files older than 4 weeks, but it is not working.

I chose:

connection: local server connection

type: backup files

search files based on an extension

path to files

ext = bak

Delete files older than 4 weeks.

I run it and it never removes any of the files--what gives.What version on SQL you are using. IF you have installed SQL2005 SP2 before march 6th, then there is a know bug on cleanup task. You can download the latest SP2 build and install.
|||

This problem has been fixed in the latest Cumulative Update. Call MS Customer Service and ask for the SQL Server cumulative update. There is no charge for this service.

jkh

sql

maintenance plan does not delete old .bak files

I have a maintenance plan that backs up our DB every night. It's supposed to delete any .bak files older that a day. However, it does not delete the old .bak files and after a couple of nights, the backups fail because the disk is out of space.
Why don't the old .bak files delete like they're supposed to?
Are you trying to perform log backup? Is there a database targeted which is in simple recovery mode? If sop,
sqlmaint will not remove old files for any database in the group.
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"KimL" <kiml@.colonialsavings.com> wrote in message news:303A4869-240C-4051-9DFA-4CCAB1F78C9F@.microsoft.com...
> I have a maintenance plan that backs up our DB every night. It's supposed to delete any .bak files older
that a day. However, it does not delete the old .bak files and after a couple of nights, the backups fail
because the disk is out of space.
> Why don't the old .bak files delete like they're supposed to?
|||I found the answer....it was a problem between the chair and the keyboard. I was examining the steps in the maintenance job and discovered that I had selected "every one WEEK" instead of "every one day"
It seems that just the very act of posting my problem here led me to the solution.
thanks for your suggestions

maintenance plan does not delete old .bak files

I have a maintenance plan that backs up our DB every night. It's supposed to delete any .bak files older that a day. However, it does not delete the old .bak files and after a couple of nights, the backups fail because the disk is out of space
Why don't the old .bak files delete like they're supposed to?Are you trying to perform log backup? Is there a database targeted which is in simple recovery mode? If sop,
sqlmaint will not remove old files for any database in the group.
Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"KimL" <kiml@.colonialsavings.com> wrote in message news:303A4869-240C-4051-9DFA-4CCAB1F78C9F@.microsoft.com...
> I have a maintenance plan that backs up our DB every night. It's supposed to delete any .bak files older
that a day. However, it does not delete the old .bak files and after a couple of nights, the backups fail
because the disk is out of space.
> Why don't the old .bak files delete like they're supposed to?|||I found the answer....it was a problem between the chair and the keyboard. I was examining the steps in the maintenance job and discovered that I had selected "every one WEEK" instead of "every one day
It seems that just the very act of posting my problem here led me to the solution. :
thanks for your suggestions

maintenance plan Cleanup Task

Hello all,

What is the latest after SP2 on the Maintenace Cleanup Task?

Has anyone been able to successfully delete old backup files(*.bak) and tran logs (*.trn) TOGETHER using maintenance plan cleanup task in SQL 2005 SP2 with all the htfixes instaled? .

In other words can EXECUTE master.dbo.xp_delete_file 0,N'D:\backups',N'bak',N'2007-07-12T10:12:03',1 be used with bak and trn at the same time

Thanks in advanced

This has been fixed in the latest cumulative update for SQL Server. Contact Customer Support and they will direct you to the fix.

jkh

maintenance plan Cleanup Task

Hello all,

What is the latest after SP2 on the Maintenace Cleanup Task?

Has anyone been able to successfully delete old backup files(*.bak) and tran logs (*.trn) TOGETHER using maintenance plan cleanup task in SQL 2005 SP2 with all the htfixes instaled? .

In other words can EXECUTE master.dbo.xp_delete_file 0,N'D:\backups',N'bak',N'2007-07-12T10:12:03',1 be used with bak and trn at the same time

Thanks in advanced

This has been fixed in the latest cumulative update for SQL Server. Contact Customer Support and they will direct you to the fix.

jkh

Maintenance Plan - Delete Old files...

I'm looking to create a Maintenance plan to backup and then delete old
backup files for All DBs on the server. (30+ DBs)
I have the Backup Option to Backup All DBs and that part works Great.
Now to delete the Backup files. The Maintenance Cleanup task only allows you
to specify a folder to search for the specified extension. Is this
Recursive? As I've placed all the DB Backups in separate subfolders for
each DB.
Do I have to create a Maintenance Cleanup task with the Specific folder for
Each DB that was backed up' (all 30+ of them)
Seems odd to have a Backup All User DBs, but the Deletion of the old files
you have to enumerate each folder?
Thanks,
Scott<-Unfortunately for this version you will need a separate cleanup task for
each folder. This is being corrected in the future.
Andrew J. Kelly SQL MVP
"Scott Townsend" <scott-i@..-N0-SPAMplease.enm.com> wrote in message
news:OoHVKE3VGHA.5808@.TK2MSFTNGP09.phx.gbl...
> I'm looking to create a Maintenance plan to backup and then delete old
> backup files for All DBs on the server. (30+ DBs)
> I have the Backup Option to Backup All DBs and that part works Great.
> Now to delete the Backup files. The Maintenance Cleanup task only allows
> you to specify a folder to search for the specified extension. Is this
> Recursive? As I've placed all the DB Backups in separate subfolders for
> each DB.
> Do I have to create a Maintenance Cleanup task with the Specific folder
> for Each DB that was backed up' (all 30+ of them)
> Seems odd to have a Backup All User DBs, but the Deletion of the old files
> you have to enumerate each folder?
> Thanks,
> Scott<-
>|||Thank you for your reply.
I guess for now I'll just not put them all in subfolders. I really don't
want to have to add/remove a clean up task each time we add/remove a DB...
Thanks again,
Scott<-
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OJf1XB4VGHA.2476@.TK2MSFTNGP11.phx.gbl...
> Unfortunately for this version you will need a separate cleanup task for
> each folder. This is being corrected in the future.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott Townsend" <scott-i@..-N0-SPAMplease.enm.com> wrote in message
> news:OoHVKE3VGHA.5808@.TK2MSFTNGP09.phx.gbl...
>

Maintenance Plan - Delete Old files...

I'm looking to create a Maintenance plan to backup and then delete old
backup files for All DBs on the server. (30+ DBs)
I have the Backup Option to Backup All DBs and that part works Great.
Now to delete the Backup files. The Maintenance Cleanup task only allows you
to specify a folder to search for the specified extension. Is this
Recursive? As I've placed all the DB Backups in separate subfolders for
each DB.
Do I have to create a Maintenance Cleanup task with the Specific folder for
Each DB that was backed up? (all 30+ of them)
Seems odd to have a Backup All User DBs, but the Deletion of the old files
you have to enumerate each folder?
Thanks,
Scott<-
Unfortunately for this version you will need a separate cleanup task for
each folder. This is being corrected in the future.
Andrew J. Kelly SQL MVP
"Scott Townsend" <scott-i@..-N0-SPAMplease.enm.com> wrote in message
news:OoHVKE3VGHA.5808@.TK2MSFTNGP09.phx.gbl...
> I'm looking to create a Maintenance plan to backup and then delete old
> backup files for All DBs on the server. (30+ DBs)
> I have the Backup Option to Backup All DBs and that part works Great.
> Now to delete the Backup files. The Maintenance Cleanup task only allows
> you to specify a folder to search for the specified extension. Is this
> Recursive? As I've placed all the DB Backups in separate subfolders for
> each DB.
> Do I have to create a Maintenance Cleanup task with the Specific folder
> for Each DB that was backed up? (all 30+ of them)
> Seems odd to have a Backup All User DBs, but the Deletion of the old files
> you have to enumerate each folder?
> Thanks,
> Scott<-
>
|||Thank you for your reply.
I guess for now I'll just not put them all in subfolders. I really don't
want to have to add/remove a clean up task each time we add/remove a DB...
Thanks again,
Scott<-
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OJf1XB4VGHA.2476@.TK2MSFTNGP11.phx.gbl...
> Unfortunately for this version you will need a separate cleanup task for
> each folder. This is being corrected in the future.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott Townsend" <scott-i@..-N0-SPAMplease.enm.com> wrote in message
> news:OoHVKE3VGHA.5808@.TK2MSFTNGP09.phx.gbl...
>

Maintenance Plan - Delete Old files...

I'm looking to create a Maintenance plan to backup and then delete old
backup files for All DBs on the server. (30+ DBs)
I have the Backup Option to Backup All DBs and that part works Great.
Now to delete the Backup files. The Maintenance Cleanup task only allows you
to specify a folder to search for the specified extension. Is this
Recursive? As I've placed all the DB Backups in separate subfolders for
each DB.
Do I have to create a Maintenance Cleanup task with the Specific folder for
Each DB that was backed up' (all 30+ of them)
Seems odd to have a Backup All User DBs, but the Deletion of the old files
you have to enumerate each folder?
Thanks,
Scott<-Unfortunately for this version you will need a separate cleanup task for
each folder. This is being corrected in the future.
--
Andrew J. Kelly SQL MVP
"Scott Townsend" <scott-i@..-N0-SPAMplease.enm.com> wrote in message
news:OoHVKE3VGHA.5808@.TK2MSFTNGP09.phx.gbl...
> I'm looking to create a Maintenance plan to backup and then delete old
> backup files for All DBs on the server. (30+ DBs)
> I have the Backup Option to Backup All DBs and that part works Great.
> Now to delete the Backup files. The Maintenance Cleanup task only allows
> you to specify a folder to search for the specified extension. Is this
> Recursive? As I've placed all the DB Backups in separate subfolders for
> each DB.
> Do I have to create a Maintenance Cleanup task with the Specific folder
> for Each DB that was backed up' (all 30+ of them)
> Seems odd to have a Backup All User DBs, but the Deletion of the old files
> you have to enumerate each folder?
> Thanks,
> Scott<-
>|||Thank you for your reply.
I guess for now I'll just not put them all in subfolders. I really don't
want to have to add/remove a clean up task each time we add/remove a DB...
Thanks again,
Scott<-
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OJf1XB4VGHA.2476@.TK2MSFTNGP11.phx.gbl...
> Unfortunately for this version you will need a separate cleanup task for
> each folder. This is being corrected in the future.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott Townsend" <scott-i@..-N0-SPAMplease.enm.com> wrote in message
> news:OoHVKE3VGHA.5808@.TK2MSFTNGP09.phx.gbl...
>> I'm looking to create a Maintenance plan to backup and then delete old
>> backup files for All DBs on the server. (30+ DBs)
>> I have the Backup Option to Backup All DBs and that part works Great.
>> Now to delete the Backup files. The Maintenance Cleanup task only allows
>> you to specify a folder to search for the specified extension. Is this
>> Recursive? As I've placed all the DB Backups in separate subfolders for
>> each DB.
>> Do I have to create a Maintenance Cleanup task with the Specific folder
>> for Each DB that was backed up' (all 30+ of them)
>> Seems odd to have a Backup All User DBs, but the Deletion of the old
>> files you have to enumerate each folder?
>> Thanks,
>> Scott<-
>

Maintenance Cleanup Task - new years bug?

I have a couple of Maintenance tasks running to clean up old files older tha
n
2 days. It has been working fine for the last week, but I checked today and
I
still have files from december in there. Is there some sort of bug with the
date passing newyears and not removing files from the previous year'TJ
As I understood (i could be wrong) this task is still in CTP SP2 and gave
not released yet.
"TJ" <TJ@.discussions.microsoft.com> wrote in message
news:2D4B9235-D132-4061-9726-C7618B1A331B@.microsoft.com...
>I have a couple of Maintenance tasks running to clean up old files older
>than
> 2 days. It has been working fine for the last week, but I checked today
> and I
> still have files from december in there. Is there some sort of bug with
> the
> date passing newyears and not removing files from the previous year'

Maintenance Cleanup Task - new years bug?

I have a couple of Maintenance tasks running to clean up old files older than
2 days. It has been working fine for the last week, but I checked today and I
still have files from december in there. Is there some sort of bug with the
date passing newyears and not removing files from the previous year?
TJ
As I understood (i could be wrong) this task is still in CTP SP2 and gave
not released yet.
"TJ" <TJ@.discussions.microsoft.com> wrote in message
news:2D4B9235-D132-4061-9726-C7618B1A331B@.microsoft.com...
>I have a couple of Maintenance tasks running to clean up old files older
>than
> 2 days. It has been working fine for the last week, but I checked today
> and I
> still have files from december in there. Is there some sort of bug with
> the
> date passing newyears and not removing files from the previous year?

Maintanance Plan and xp_delete_file

Hi there,

i have a mp that deletes old bak, trn and txt files. It works well for bak and trn files, but it never deletes any of the report files created by other MPs.

I also can't delete any of the report files by executing the following statement (as sa or with windows login as a local admin)

EXECUTE master.dbo.xp_delete_file 1,N'D:\Backup\log',N'txt'

System ist SQL Server 2005 SP1 German

Any ideas?

Thanks in advance

regards
tobias
The path needs to end with a '\'|||As that referred XP is an undocumented XP there might be still few features are not enabled properly, this has been logged under MS Connect http://connect.microsoft.com/SQLServer/Feedback and wait for the solution by referring to the link above.|||

Thanks for reporting the issue.

Please file a defect report for this issue at http://connect.microsoft.com/sqlserver. Customer reported issues are used to prioritize future work.

Thanks,

Sirish

|||

As an FYI for anyone else who is experiencing problems with this - one issue is now documented at http://support.microsoft.com/kb/938085

The solution seems to involve writing your own deletion script. (Many examples can be found online).

|||

This problem has been addressed in the June Cumulative Update Hotfix, available from Microsoft Customer Support.

I have asked the owner of the article you cite to update it to reflect the availability of a fix.

Thanks,

jkh

Maintanance Plan and xp_delete_file

Hi there,

i have a mp that deletes old bak, trn and txt files. It works well for bak and trn files, but it never deletes any of the report files created by other MPs.

I also can't delete any of the report files by executing the following statement (as sa or with windows login as a local admin)

EXECUTE master.dbo.xp_delete_file 1,N'D:\Backup\log',N'txt'

System ist SQL Server 2005 SP1 German

Any ideas?

Thanks in advance

regards
tobiasThe path needs to end with a '\'|||As that referred XP is an undocumented XP there might be still few features are not enabled properly, this has been logged under MS Connect http://connect.microsoft.com/SQLServer/Feedback and wait for the solution by referring to the link above.|||

Thanks for reporting the issue.

Please file a defect report for this issue at http://connect.microsoft.com/sqlserver. Customer reported issues are used to prioritize future work.

Thanks,

Sirish

|||

As an FYI for anyone else who is experiencing problems with this - one issue is now documented at http://support.microsoft.com/kb/938085

The solution seems to involve writing your own deletion script. (Many examples can be found online).

|||

This problem has been addressed in the June Cumulative Update Hotfix, available from Microsoft Customer Support.

I have asked the owner of the article you cite to update it to reflect the availability of a fix.

Thanks,

jkh

sql

Maintanance Plan and xp_delete_file

Hi there,

i have a mp that deletes old bak, trn and txt files. It works well for bak and trn files, but it never deletes any of the report files created by other MPs.

I also can't delete any of the report files by executing the following statement (as sa or with windows login as a local admin)

EXECUTE master.dbo.xp_delete_file 1,N'D:\Backup\log',N'txt'

System ist SQL Server 2005 SP1 German

Any ideas?

Thanks in advance

regards
tobiasThe path needs to end with a '\'|||As that referred XP is an undocumented XP there might be still few features are not enabled properly, this has been logged under MS Connect http://connect.microsoft.com/SQLServer/Feedback and wait for the solution by referring to the link above.|||

Thanks for reporting the issue.

Please file a defect report for this issue at http://connect.microsoft.com/sqlserver. Customer reported issues are used to prioritize future work.

Thanks,

Sirish

|||

As an FYI for anyone else who is experiencing problems with this - one issue is now documented at http://support.microsoft.com/kb/938085

The solution seems to involve writing your own deletion script. (Many examples can be found online).

|||

This problem has been addressed in the June Cumulative Update Hotfix, available from Microsoft Customer Support.

I have asked the owner of the article you cite to update it to reflect the availability of a fix.

Thanks,

jkh

Maintanance Plan and xp_delete_file

Hi there,

i have a mp that deletes old bak, trn and txt files. It works well for bak and trn files, but it never deletes any of the report files created by other MPs.

I also can't delete any of the report files by executing the following statement (as sa or with windows login as a local admin)

EXECUTE master.dbo.xp_delete_file 1,N'D:\Backup\log',N'txt'

System ist SQL Server 2005 SP1 German

Any ideas?

Thanks in advance

regards
tobiasThe path needs to end with a '\'|||As that referred XP is an undocumented XP there might be still few features are not enabled properly, this has been logged under MS Connect http://connect.microsoft.com/SQLServer/Feedback and wait for the solution by referring to the link above.|||

Thanks for reporting the issue.

Please file a defect report for this issue at http://connect.microsoft.com/sqlserver. Customer reported issues are used to prioritize future work.

Thanks,

Sirish

|||

As an FYI for anyone else who is experiencing problems with this - one issue is now documented at http://support.microsoft.com/kb/938085

The solution seems to involve writing your own deletion script. (Many examples can be found online).

|||

This problem has been addressed in the June Cumulative Update Hotfix, available from Microsoft Customer Support.

I have asked the owner of the article you cite to update it to reflect the availability of a fix.

Thanks,

jkh

Maint. Plan Help

I need to set up a Maintenance Plan on a 6.5 DB. I know the version is old but the customer cannot upgrade at this time. When I try and run the wizard to set it up I get a warning about running a plan on a DB that is larger that 400MB. Whats the deal? Thanks for the help!!I need to set up a Maintenance Plan on a 6.5 DB. I know the version is old but the customer cannot upgrade at this time. When I try and run the wizard to set it up I get a warning about running a plan on a DB that is larger that 400MB. Whats the deal? Thanks for the help!!

As I recall (and it has been a while), one of the things that the maintenance plan will try to set up is an index reorg and (possibly) space reclamation. You don't want to touch either of these with your database. At least, I don't think you do. I would probably skip the maintenance plan wizard altogether and set up backup jobs to make sure your database is being backed up.

When I did this, I had to set up 5 backup devices (named FULL, 0600, 1200, 1800 and 0000). I then scheduled the full backup to write to the FULL backup device and four transaction log backups to write to the other four devices (at midnight, 6:00am, noon and 6:00pm). The backups would overwrite the files (so another process would have to copy the backup files to a remote location).

SQL 6.5. Ugh. It's the reason I drink so much now.

Regards,

hmscott

Maint not deleting old backups

I use the Maintenance utility to backup the database. I have specified for
it to delete any backup older than 1 day, but it doesn't delete anything.
The old backups end of taking up all the disk space, and then the backup
doesn't complete.
Any suggestions?
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gary465" <Gary465@.discussions.microsoft.com> wrote in message
news:34112B79-046E-40AB-B489-34A1AD589C45@.microsoft.com...
>I use the Maintenance utility to backup the database. I have specified for
> it to delete any backup older than 1 day, but it doesn't delete anything.
> The old backups end of taking up all the disk space, and then the backup
> doesn't complete.
> Any suggestions?
|||Make sure the account running the SQLAgent service has delete permissions on
the backup folder
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Gary465" <Gary465@.discussions.microsoft.com> wrote in message
news:34112B79-046E-40AB-B489-34A1AD589C45@.microsoft.com...
>I use the Maintenance utility to backup the database. I have specified for
> it to delete any backup older than 1 day, but it doesn't delete anything.
> The old backups end of taking up all the disk space, and then the backup
> doesn't complete.
> Any suggestions?
|||Thanks guys for the suggestions. It was a more obvious problem, as the
backups had not been completing successfully due to a lack of space. I guess
the utility does not delete the old backups if the job does not complete
successfully.
"Kevin3NF" wrote:

> Make sure the account running the SQLAgent service has delete permissions on
> the backup folder
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Gary465" <Gary465@.discussions.microsoft.com> wrote in message
> news:34112B79-046E-40AB-B489-34A1AD589C45@.microsoft.com...
>
>