Maintenance Plan File Deletion Bug

I have a maintenance plan set to delete files after 1 week. Instead it is deleting everthing older then 1 day. When I view the tsql in the designer, the xp_delete_file string looks correct. Is there anyway to see the exact xp_delete_file string that is actually being executed at run time?

I found the problem.

There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

The patching procedure is a little complicated. Read this:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

What service pack you are on?

There were maintenane related bugs in SP2 so that MS released hot fix on 03/05/2007.

Check the hot fix if you are already on SP2 applied before the hot fix release. If you are on SP1 apply the SP2 down loaded after 03/06/2007 which is hot fix included.

|||

I found out what the problem is.

There is an "off by one" bug where the wrong time unit of measure is being used.

"Hours" are being converted to "Days", Days" are being converted to "Hours", and "Hours" will blow with an invalid index.

This problem exists for "Maintenance Cleanup" tasks and "Clean Up History" tasks.

The fix is a little complicated because there are two versions of sp2 out there.

Read this to figure out the proper fix:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

Maintenance Plan Failure

I have a maintenance plan setup that among other tasks is supposed to backup
Transaction Logs every 3 hours and then delete Log files that are older than
2 days. However, this job keeps failing when it tries to delete the older
files. It makes the 3 hour backups ok, but then shows a failure for the
delete action inside Enterprise Manager > Server > Maintenance > Jobs. Any
idea what could be happening here? It used to work fine. The only error I
get is posted below (though probably not much help).
*************
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 12/17/2004
Time: 2:00:06 PM
User: N/A
Computer: SERVER
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'Backup Maintenance Plan'' (0x205B61B1C1A7D54BA426DF0C810C8F05) - Status:
Failed - Invoked on: 2004-12-17 14:00:00 - Message: The job failed. The Job
was invoked by Schedule 43 (Schedule 1). The last step to run was step 1
(Step 1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Brandon
IT Director
Presentations Direct - Office Equipment & Supplies
http://www.presentationsdirect.com
"Brandon S." wrote:
I don't know if this is what is causing your problem, but we had our backup
directory shared and that was enough to stop the deletes.
|||Thanks, but that's not the case here.
Brandon
IT Director
Presentations Direct - Office Equipment & Supplies
http://www.presentationsdirect.com
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:4C8DBE2F-99D8-4FCD-85DB-13609E06EC84@.microsoft.com...
> "Brandon S." wrote:
> I don't know if this is what is causing your problem, but we had our
> backup
> directory shared and that was enough to stop the deletes.
|||It is usually due to some other process locking the file or dir such as a
tape device. But this blurb from Bill at MS does a pretty good job of
outlining the issues:
http://support.microsoft.com/default...;en-us;Q303292
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
Andrew J. Kelly SQL MVP
"Brandon S." <bsmith@.presentationsdirect.nospam.com> wrote in message
news:uE0Ol3H5EHA.2804@.TK2MSFTNGP15.phx.gbl...
>I have a maintenance plan setup that among other tasks is supposed to
>backup Transaction Logs every 3 hours and then delete Log files that are
>older than 2 days. However, this job keeps failing when it tries to delete
>the older files. It makes the 3 hour backups ok, but then shows a failure
>for the delete action inside Enterprise Manager > Server > Maintenance >
>Jobs. Any idea what could be happening here? It used to work fine. The
>only error I get is posted below (though probably not much help).
> *************
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 12/17/2004
> Time: 2:00:06 PM
> User: N/A
> Computer: SERVER
> Description:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan 'Backup Maintenance Plan'' (0x205B61B1C1A7D54BA426DF0C810C8F05) -
> Status: Failed - Invoked on: 2004-12-17 14:00:00 - Message: The job
> failed. The Job was invoked by Schedule 43 (Schedule 1). The last step
> to run was step 1 (Step 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> --
> Brandon
> IT Director
> Presentations Direct - Office Equipment & Supplies
> http://www.presentationsdirect.com
>

Maintenance Plan Failure

I have a maintenance plan setup that among other tasks is supposed to backup
Transaction Logs every 3 hours and then delete Log files that are older than
2 days. However, this job keeps failing when it tries to delete the older
files. It makes the 3 hour backups ok, but then shows a failure for the
delete action inside Enterprise Manager > Server > Maintenance > Jobs. Any
idea what could be happening here? It used to work fine. The only error I
get is posted below (though probably not much help).
*************
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 12/17/2004
Time: 2:00:06 PM
User: N/A
Computer: SERVER
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'Backup Maintenance Plan'' (0x205B61B1C1A7D54BA426DF0C810C8F05) - Status:
Failed - Invoked on: 2004-12-17 14:00:00 - Message: The job failed. The Job
was invoked by Schedule 43 (Schedule 1). The last step to run was step 1
(Step 1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
--
Brandon
IT Director
Presentations Direct - Office Equipment & Supplies
http://www.presentationsdirect.com"Brandon S." wrote:
I don't know if this is what is causing your problem, but we had our backup
directory shared and that was enough to stop the deletes.|||Thanks, but that's not the case here.
--
Brandon
IT Director
Presentations Direct - Office Equipment & Supplies
http://www.presentationsdirect.com
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:4C8DBE2F-99D8-4FCD-85DB-13609E06EC84@.microsoft.com...
> "Brandon S." wrote:
> I don't know if this is what is causing your problem, but we had our
> backup
> directory shared and that was enough to stop the deletes.|||It is usually due to some other process locking the file or dir such as a
tape device. But this blurb from Bill at MS does a pretty good job of
outlining the issues:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
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
Andrew J. Kelly SQL MVP
"Brandon S." <bsmith@.presentationsdirect.nospam.com> wrote in message
news:uE0Ol3H5EHA.2804@.TK2MSFTNGP15.phx.gbl...
>I have a maintenance plan setup that among other tasks is supposed to
>backup Transaction Logs every 3 hours and then delete Log files that are
>older than 2 days. However, this job keeps failing when it tries to delete
>the older files. It makes the 3 hour backups ok, but then shows a failure
>for the delete action inside Enterprise Manager > Server > Maintenance >
>Jobs. Any idea what could be happening here? It used to work fine. The
>only error I get is posted below (though probably not much help).
> *************
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 12/17/2004
> Time: 2:00:06 PM
> User: N/A
> Computer: SERVER
> Description:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan 'Backup Maintenance Plan'' (0x205B61B1C1A7D54BA426DF0C810C8F05) -
> Status: Failed - Invoked on: 2004-12-17 14:00:00 - Message: The job
> failed. The Job was invoked by Schedule 43 (Schedule 1). The last step
> to run was step 1 (Step 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> --
> Brandon
> IT Director
> Presentations Direct - Office Equipment & Supplies
> http://www.presentationsdirect.com
>

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

I have a maintenance plan that is setup to delete backup files older than x
days. And it was deleting the files just fine until a few days ago . . .
now it will not delete older files. I even setup a new maintenance plan and
it still won't delete older files. Any idea as to why?
Brandon
==================
Presentations Direct - http://www.presentationsdirect.com
> Binding Machines
http://www.presentationsdirect.com/...ng-machines.asp
> Laminators
http://www.presentationsdirect.com/...-laminators.asp
> Paper Shredders
http://www.presentationsdirect.com/...r-shredders.asp
==================Check all the databases in the plan. If any of them are in Simple Recovery
model, this may be the issue.
2000, or 2005?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
http://kevin3nf.blogspot.com
"Brandon" <bsmith@.presentationsdirect.nospam.com> wrote in message
news:OZkIa2c%23GHA.2180@.TK2MSFTNGP05.phx.gbl...
>I have a maintenance plan that is setup to delete backup files older than x
>days. And it was deleting the files just fine until a few days ago . . .
>now it will not delete older files. I even setup a new maintenance plan
>and it still won't delete older files. Any idea as to why?
> --
> Brandon
> ==================
> Presentations Direct - http://www.presentationsdirect.com
> http://www.presentationsdirect.com/...-laminators.asp
> http://www.presentationsdirect.com/...r-shredders.asp
> ==================
>|||SQL 2000 . . . none are in simple mode.
Brandon
==================
Presentations Direct - http://www.presentationsdirect.com
> Binding Machines
http://www.presentationsdirect.com/...ng-machines.asp
> Laminators
http://www.presentationsdirect.com/...-laminators.asp
> Paper Shredders
http://www.presentationsdirect.com/...r-shredders.asp
==================
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%23SsIogd%23GHA.1220@.TK2MSFTNGP05.phx.gbl...
> Check all the databases in the plan. If any of them are in Simple
> Recovery model, this may be the issue.
> 2000, or 2005?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> http://kevin3nf.blogspot.com
>
> "Brandon" <bsmith@.presentationsdirect.nospam.com> wrote in message
> news:OZkIa2c%23GHA.2180@.TK2MSFTNGP05.phx.gbl...
>

Maintenance plan

From my maintenance plan backup, i ve setup MY BACKUP and the transaction lo
g
backup to remove files (BAK and TRN) older than 1 days but it doesn't delete
them anymore. It
usedjto work fine until 2 weeks . Now , ive to remove them manually as it
fills the space quickly
Im running SQL2000 on windows server 2003
any ideas how to fix this '
RickSee if this blurb from Bill and the associated link help:
-- Log files don't delete --
http://support.microsoft.com/defaul...b;en-us;Q303292
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
Andrew J. Kelly SQL MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:FF1D6886-671E-4162-8E2C-7894E5345642@.microsoft.com...
> From my maintenance plan backup, i ve setup MY BACKUP and the transaction
> log
> backup to remove files (BAK and TRN) older than 1 days but it doesn't
> delete
> them anymore. It
> usedjto work fine until 2 weeks . Now , ive to remove them manually as
> it
> fills the space quickly
> Im running SQL2000 on windows server 2003
> any ideas how to fix this '
> --
> Rick|||Bill, this was a very good troubleshooting list. Thanks.
"Andrew J. Kelly" wrote:

> See if this blurb from Bill and the associated link help:
>
> -- Log files don't delete --
> http://support.microsoft.com/defaul...b;en-us;Q303292
> 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). Thi
s
> 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 t
o
> 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 conside
r
> 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 woul
d
> 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 t
o
> local drive), then you will need to modify the above (with respect to wher
e
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Rick" <Rick@.discussions.microsoft.com> wrote in message
> news:FF1D6886-671E-4162-8E2C-7894E5345642@.microsoft.com...
>
>sql

Maintenance plan

From my maintenance plan backup, i ve setup MY BACKUP and the transaction log
backup to remove files (BAK and TRN) older than 1 days but it doesn't delete
them anymore. It
usedjto work fine until 2 weeks . Now , ive to remove them manually as it
fills the space quickly
Im running SQL2000 on windows server 2003
any ideas how to fix this ?
Rick
See if this blurb from Bill and the associated link help:
-- Log files don't delete --
http://support.microsoft.com/default...;en-us;Q303292
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
Andrew J. Kelly SQL MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:FF1D6886-671E-4162-8E2C-7894E5345642@.microsoft.com...
> From my maintenance plan backup, i ve setup MY BACKUP and the transaction
> log
> backup to remove files (BAK and TRN) older than 1 days but it doesn't
> delete
> them anymore. It
> usedjto work fine until 2 weeks . Now , ive to remove them manually as
> it
> fills the space quickly
> Im running SQL2000 on windows server 2003
> any ideas how to fix this ?
> --
> Rick
|||Bill, this was a very good troubleshooting list. Thanks.
"Andrew J. Kelly" wrote:

> See if this blurb from Bill and the associated link help:
>
> -- Log files don't delete --
> http://support.microsoft.com/default...;en-us;Q303292
> 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
>
> --
> Andrew J. Kelly SQL MVP
>
> "Rick" <Rick@.discussions.microsoft.com> wrote in message
> news:FF1D6886-671E-4162-8E2C-7894E5345642@.microsoft.com...
>
>
sql

Maintenance Plan

Using SQL2000
I run a daily back up for DB and Logs as part of maintenance plan. I set the
option to delete files older than 7 days for both Dats and Logs. It does this
for the Dat file but not for the log. Is this a bug or is there something
that I am neglecting to do or is there a reason why SQL keeps old transaction
logs?
Thanks in advance.
Are you trying to backup log for databases that are in simple recovery mode?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AndyB" <AndyB@.discussions.microsoft.com> wrote in message
news:C9D4CCD0-57B7-4AC8-87AD-2D7AE5C49697@.microsoft.com...
> Using SQL2000
> I run a daily back up for DB and Logs as part of maintenance plan. I set the
> option to delete files older than 7 days for both Dats and Logs. It does this
> for the Dat file but not for the log. Is this a bug or is there something
> that I am neglecting to do or is there a reason why SQL keeps old transaction
> logs?
> Thanks in advance.
|||I am using Full recovery mode.
"Tibor Karaszi" wrote:

> Are you trying to backup log for databases that are in simple recovery mode?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AndyB" <AndyB@.discussions.microsoft.com> wrote in message
> news:C9D4CCD0-57B7-4AC8-87AD-2D7AE5C49697@.microsoft.com...
>
>
|||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/
"AndyB" <AndyB@.discussions.microsoft.com> wrote in message
news:92030856-752D-4733-9F66-9FCF71E3B009@.microsoft.com...[vbcol=seagreen]
>I am using Full recovery mode.
> "Tibor Karaszi" wrote:

Maintenance Plan

When setting up a maintenance plan in SQL 2000, there is a "...remove files
older than...", option. I don't see it in SQL 2005 when creating a
maintenance plan.
Does this option exist inf SQL 2005? If it does, where can I find it? Thanks
to everyone for being there to help.
SQL 2005 ServerHi
It was completely changed. What does the MP do?
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:91D15026-DBAA-4F8F-B126-B2BE60AF3B8B@.microsoft.com...
> When setting up a maintenance plan in SQL 2000, there is a "...remove
> files
> older than...", option. I don't see it in SQL 2005 when creating a
> maintenance plan.
> Does this option exist inf SQL 2005? If it does, where can I find it?
> Thanks
> to everyone for being there to help.
> SQL 2005 Server|||You don't find this in the wizard. Open the plan in the designer (FROM SSMS), and add that task.
Note that sp1 is required to traverse subfolders.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:91D15026-DBAA-4F8F-B126-B2BE60AF3B8B@.microsoft.com...
> When setting up a maintenance plan in SQL 2000, there is a "...remove files
> older than...", option. I don't see it in SQL 2005 when creating a
> maintenance plan.
> Does this option exist inf SQL 2005? If it does, where can I find it? Thanks
> to everyone for being there to help.
> SQL 2005 Server|||Three separate plans; Full, Differential, aand transaction log backups.
"Uri Dimant" wrote:
> Hi
> It was completely changed. What does the MP do?
>
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:91D15026-DBAA-4F8F-B126-B2BE60AF3B8B@.microsoft.com...
> > When setting up a maintenance plan in SQL 2000, there is a "...remove
> > files
> > older than...", option. I don't see it in SQL 2005 when creating a
> > maintenance plan.
> > Does this option exist inf SQL 2005? If it does, where can I find it?
> > Thanks
> > to everyone for being there to help.
> >
> > SQL 2005 Server
>
>|||Found it. It's in the Maintenance Plan Tasks as Maintenance Cleanup Task.
"Uri Dimant" wrote:
> Hi
> It was completely changed. What does the MP do?
>
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:91D15026-DBAA-4F8F-B126-B2BE60AF3B8B@.microsoft.com...
> > When setting up a maintenance plan in SQL 2000, there is a "...remove
> > files
> > older than...", option. I don't see it in SQL 2005 when creating a
> > maintenance plan.
> > Does this option exist inf SQL 2005? If it does, where can I find it?
> > Thanks
> > to everyone for being there to help.
> >
> > SQL 2005 Server
>
>

Maintenance Plan

I have a maintenance plan that is setup to delete backup files older than x
days. And it was deleting the files just fine until a few days ago . . .
now it will not delete older files. I even setup a new maintenance plan and
it still won't delete older files. Any idea as to why?
--
Brandon
================== Presentations Direct - http://www.presentationsdirect.com
> Binding Machines
http://www.presentationsdirect.com/binding-machines/gbc-binding-machines.asp
> Laminators
http://www.presentationsdirect.com/laminators/gbc-laminators.asp
> Paper Shredders
http://www.presentationsdirect.com/paper-shredders/gbc-paper-shredders.asp
==================Check all the databases in the plan. If any of them are in Simple Recovery
model, this may be the issue.
2000, or 2005?
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
http://kevin3nf.blogspot.com
"Brandon" <bsmith@.presentationsdirect.nospam.com> wrote in message
news:OZkIa2c%23GHA.2180@.TK2MSFTNGP05.phx.gbl...
>I have a maintenance plan that is setup to delete backup files older than x
>days. And it was deleting the files just fine until a few days ago . . .
>now it will not delete older files. I even setup a new maintenance plan
>and it still won't delete older files. Any idea as to why?
> --
> Brandon
> ==================> Presentations Direct - http://www.presentationsdirect.com
> > Binding Machines
> http://www.presentationsdirect.com/binding-machines/gbc-binding-machines.asp
> > Laminators
> http://www.presentationsdirect.com/laminators/gbc-laminators.asp
> > Paper Shredders
> http://www.presentationsdirect.com/paper-shredders/gbc-paper-shredders.asp
> ==================>|||SQL 2000 . . . none are in simple mode.
--
Brandon
==================Presentations Direct - http://www.presentationsdirect.com
> Binding Machines
http://www.presentationsdirect.com/binding-machines/gbc-binding-machines.asp
> Laminators
http://www.presentationsdirect.com/laminators/gbc-laminators.asp
> Paper Shredders
http://www.presentationsdirect.com/paper-shredders/gbc-paper-shredders.asp
=================="Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%23SsIogd%23GHA.1220@.TK2MSFTNGP05.phx.gbl...
> Check all the databases in the plan. If any of them are in Simple
> Recovery model, this may be the issue.
> 2000, or 2005?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> http://kevin3nf.blogspot.com
>
> "Brandon" <bsmith@.presentationsdirect.nospam.com> wrote in message
> news:OZkIa2c%23GHA.2180@.TK2MSFTNGP05.phx.gbl...
>>I have a maintenance plan that is setup to delete backup files older than
>>x days. And it was deleting the files just fine until a few days ago . .
>>. now it will not delete older files. I even setup a new maintenance plan
>>and it still won't delete older files. Any idea as to why?
>> --
>> Brandon
>> ==================>> Presentations Direct - http://www.presentationsdirect.com
>> > Binding Machines
>> http://www.presentationsdirect.com/binding-machines/gbc-binding-machines.asp
>> > Laminators
>> http://www.presentationsdirect.com/laminators/gbc-laminators.asp
>> > Paper Shredders
>> http://www.presentationsdirect.com/paper-shredders/gbc-paper-shredders.asp
>> ==================>

Maintenance plan

From my maintenance plan backup, i ve setup MY BACKUP and the transaction log
backup to remove files (BAK and TRN) older than 1 days but it doesn't delete
them anymore. It
usedjto work fine until 2 weeks . Now , ive to remove them manually as it
fills the space quickly
Im running SQL2000 on windows server 2003
any ideas how to fix this '
--
RickSee if this blurb from Bill and the associated link help:
-- Log files don't delete --
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
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
Andrew J. Kelly SQL MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:FF1D6886-671E-4162-8E2C-7894E5345642@.microsoft.com...
> From my maintenance plan backup, i ve setup MY BACKUP and the transaction
> log
> backup to remove files (BAK and TRN) older than 1 days but it doesn't
> delete
> them anymore. It
> usedjto work fine until 2 weeks . Now , ive to remove them manually as
> it
> fills the space quickly
> Im running SQL2000 on windows server 2003
> any ideas how to fix this '
> --
> Rick

Maintenance Cleanup task not functioning properly?

Using SQL Server 2005 Management Studio and backing up databases. When I add the task Maintenance Cleanup to remove database backups older then 1 day the task fails?
What part of this TSQL code does SQL Server 2005 Management Studio not understand?

EXECUTE master.dbo.xp_delete_file 0,N'\\pdxsql03\SQL Backup\POSSQL_Server',N'.bak',N'05/15/2006 08:09:37'

The database file backup extension is .bak and the file is older than one day which was specified.
It is just not deleting the file.
We have deleted the maintenance task and recreated it with no effect and this has been happening for over a month.
Any help here would be greatly appreciated.

Carl
carlt@.gicw.org

I've been having the same problem for over three months now and keep checking back here to see if there's been a fix....

|||

http://blogs.msdn.com/sanchan/archive/2006/04/23/581640.aspx

This is addressed in SP1. Please look at the above blog for more information.

Gops Dwarak

|||

The trick is to replace .bak with *.*

It definitely works. :-)

|||

I tried to replace the extension "bak" with "*.*", "*" and ".*" I know this looks like the meeting of the disabled Smilie group, but you know what I mean. It definitely _DOESN′T_ delete any backups. However, when I execute the t-sql created by the maintenance Cleanup Task, it deletes the files.

Any suggestions?

|||Not working for me either. SP1 applied and all. Subdirectories for backups. .. trying to delete archived backups, in the form Filename.bak.7z. No worky worky!
Please advise? (Read: How hard is it to implement a functioning XSP?!)|||

My solution was to take the T-SQL created by the cleanup task and modifying it slightly:

declare @.dt datetime

select @.dt=getdate()-5
EXECUTE master.dbo.xp_delete_file 0,N'E:\Backups',N'BAK',@.dt,1

The variable is used to check the age of the backup-files. In this case, all backups older than 5 days are deleted. The last Number (=1) gives the number of subdirectories to be searched in for .bak-files. AFAIK a maximum of 5 subdirs is supported.

Then you can take it and put it into an "execute SQL"-Task, take the package and schedule it or directly schedule it in the Job Scheduler of Management Studio as SQL-Statement

|||

The workaround above may or may not work, depending on what the problem is.

To get to the bottom of the problem - what is the exact error that you are getting? We can then search if this is a known problem. There were number of issues with Cleanup task. A few were fixed in SP1, like Gops has mentioned, more were fixed in SP2 which is in the CTP release right now. If you can, please install SQL Server 2005 SP2 CTP December from here: http://www.microsoft.com/sql/ctp.mspx and let us know if the problem sitll exists.

Regards,

Maciek Sarnowicz

|||

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

Thanks,

-lance

|||

There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

The patching procedure is a little complicated. Read this:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

|||

-lance wrote:

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

You are running SQL Server 2005 Service Pack 2 (9.00.3054.00).

You are running Windows Server 2003 Service Pack 1.

Hope this clears up your confusion.

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

|||

I was also having a similar issue. First the cleanup task would just not delete any files no matter where I pointed it to. I originally had put “.trn” in the file extension box. I then removed the . and everything deleted correctly. The problem we then ran into was that it would only delete from the top level folder that we pointed to. In another blog I read that I must check the Include first-level subfolders box to delete from these sub folders. The problem is that this checkbox is nowhere to be found on the maintenance cleanup task dialog box. We do have service pack 2 loaded and most of the updates but….?

I finally just got the T-SQL statement and created my own job.

This is the original statement:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18'

Is simply added a 1 to the end of the statement and made it:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18',1

This job runs great and deletes all the files on level below the top level folder. I did try adding a 2 to see if it would let me delete further levels but it errors out. Hope this helps somebody!

Maintenance Cleanup task not functioning properly?

Using SQL Server 2005 Management Studio and backing up databases. When I add the task Maintenance Cleanup to remove database backups older then 1 day the task fails?
What part of this TSQL code does SQL Server 2005 Management Studio not understand?

EXECUTE master.dbo.xp_delete_file 0,N'\\pdxsql03\SQL Backup\POSSQL_Server',N'.bak',N'05/15/2006 08:09:37'

The database file backup extension is .bak and the file is older than one day which was specified.
It is just not deleting the file.
We have deleted the maintenance task and recreated it with no effect and this has been happening for over a month.
Any help here would be greatly appreciated.

Carl
carlt@.gicw.org

I've been having the same problem for over three months now and keep checking back here to see if there's been a fix....

|||

http://blogs.msdn.com/sanchan/archive/2006/04/23/581640.aspx

This is addressed in SP1. Please look at the above blog for more information.

Gops Dwarak

|||

The trick is to replace .bak with *.*

It definitely works. :-)

|||

I tried to replace the extension "bak" with "*.*", "*" and ".*" I know this looks like the meeting of the disabled Smilie group, but you know what I mean. It definitely _DOESN′T_ delete any backups. However, when I execute the t-sql created by the maintenance Cleanup Task, it deletes the files.

Any suggestions?

|||Not working for me either. SP1 applied and all. Subdirectories for backups. .. trying to delete archived backups, in the form Filename.bak.7z. No worky worky!
Please advise? (Read: How hard is it to implement a functioning XSP?!)
|||

My solution was to take the T-SQL created by the cleanup task and modifying it slightly:

declare @.dt datetime

select @.dt=getdate()-5
EXECUTE master.dbo.xp_delete_file 0,N'E:\Backups',N'BAK',@.dt,1

The variable is used to check the age of the backup-files. In this case, all backups older than 5 days are deleted. The last Number (=1) gives the number of subdirectories to be searched in for .bak-files. AFAIK a maximum of 5 subdirs is supported.

Then you can take it and put it into an "execute SQL"-Task, take the package and schedule it or directly schedule it in the Job Scheduler of Management Studio as SQL-Statement

|||

The workaround above may or may not work, depending on what the problem is.

To get to the bottom of the problem - what is the exact error that you are getting? We can then search if this is a known problem. There were number of issues with Cleanup task. A few were fixed in SP1, like Gops has mentioned, more were fixed in SP2 which is in the CTP release right now. If you can, please install SQL Server 2005 SP2 CTP December from here: http://www.microsoft.com/sql/ctp.mspx and let us know if the problem sitll exists.

Regards,

Maciek Sarnowicz

|||

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

Thanks,

-lance

|||

There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

The patching procedure is a little complicated. Read this:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

|||

-lance wrote:

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

You are running SQL Server 2005 Service Pack 2 (9.00.3054.00).

You are running Windows Server 2003 Service Pack 1.

Hope this clears up your confusion.

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

|||

I was also having a similar issue. First the cleanup task would just not delete any files no matter where I pointed it to. I originally had put “.trn” in the file extension box. I then removed the . and everything deleted correctly. The problem we then ran into was that it would only delete from the top level folder that we pointed to. In another blog I read that I must check the Include first-level subfolders box to delete from these sub folders. The problem is that this checkbox is nowhere to be found on the maintenance cleanup task dialog box. We do have service pack 2 loaded and most of the updates but….?

I finally just got the T-SQL statement and created my own job.

This is the original statement:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18'

Is simply added a 1 to the end of the statement and made it:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18',1

This job runs great and deletes all the files on level below the top level folder. I did try adding a 2 to see if it would let me delete further levels but it errors out. Hope this helps somebody!

Maintenance Cleanup task not functioning properly?

Using SQL Server 2005 Management Studio and backing up databases. When I add the task Maintenance Cleanup to remove database backups older then 1 day the task fails?
What part of this TSQL code does SQL Server 2005 Management Studio not understand?

EXECUTE master.dbo.xp_delete_file 0,N'\\pdxsql03\SQL Backup\POSSQL_Server',N'.bak',N'05/15/2006 08:09:37'

The database file backup extension is .bak and the file is older than one day which was specified.
It is just not deleting the file.
We have deleted the maintenance task and recreated it with no effect and this has been happening for over a month.
Any help here would be greatly appreciated.

Carl
carlt@.gicw.org

I've been having the same problem for over three months now and keep checking back here to see if there's been a fix....

|||

http://blogs.msdn.com/sanchan/archive/2006/04/23/581640.aspx

This is addressed in SP1. Please look at the above blog for more information.

Gops Dwarak

|||

The trick is to replace .bak with *.*

It definitely works. :-)

|||

I tried to replace the extension "bak" with "*.*", "*" and ".*" I know this looks like the meeting of the disabled Smilie group, but you know what I mean. It definitely _DOESN′T_ delete any backups. However, when I execute the t-sql created by the maintenance Cleanup Task, it deletes the files.

Any suggestions?

|||Not working for me either. SP1 applied and all. Subdirectories for backups. .. trying to delete archived backups, in the form Filename.bak.7z. No worky worky!
Please advise? (Read: How hard is it to implement a functioning XSP?!)|||

My solution was to take the T-SQL created by the cleanup task and modifying it slightly:

declare @.dt datetime

select @.dt=getdate()-5
EXECUTE master.dbo.xp_delete_file 0,N'E:\Backups',N'BAK',@.dt,1

The variable is used to check the age of the backup-files. In this case, all backups older than 5 days are deleted. The last Number (=1) gives the number of subdirectories to be searched in for .bak-files. AFAIK a maximum of 5 subdirs is supported.

Then you can take it and put it into an "execute SQL"-Task, take the package and schedule it or directly schedule it in the Job Scheduler of Management Studio as SQL-Statement

|||

The workaround above may or may not work, depending on what the problem is.

To get to the bottom of the problem - what is the exact error that you are getting? We can then search if this is a known problem. There were number of issues with Cleanup task. A few were fixed in SP1, like Gops has mentioned, more were fixed in SP2 which is in the CTP release right now. If you can, please install SQL Server 2005 SP2 CTP December from here: http://www.microsoft.com/sql/ctp.mspx and let us know if the problem sitll exists.

Regards,

Maciek Sarnowicz

|||

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

Thanks,

-lance

|||

There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

The patching procedure is a little complicated. Read this:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

|||

-lance wrote:

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

You are running SQL Server 2005 Service Pack 2 (9.00.3054.00).

You are running Windows Server 2003 Service Pack 1.

Hope this clears up your confusion.

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

|||

I was also having a similar issue. First the cleanup task would just not delete any files no matter where I pointed it to. I originally had put “.trn” in the file extension box. I then removed the . and everything deleted correctly. The problem we then ran into was that it would only delete from the top level folder that we pointed to. In another blog I read that I must check the Include first-level subfolders box to delete from these sub folders. The problem is that this checkbox is nowhere to be found on the maintenance cleanup task dialog box. We do have service pack 2 loaded and most of the updates but….?

I finally just got the T-SQL statement and created my own job.

This is the original statement:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18'

Is simply added a 1 to the end of the statement and made it:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18',1

This job runs great and deletes all the files on level below the top level folder. I did try adding a 2 to see if it would let me delete further levels but it errors out. Hope this helps somebody!

Maintenance Cleanup task not functioning properly?

Using SQL Server 2005 Management Studio and backing up databases. When I add the task Maintenance Cleanup to remove database backups older then 1 day the task fails?
What part of this TSQL code does SQL Server 2005 Management Studio not understand?

EXECUTE master.dbo.xp_delete_file 0,N'\\pdxsql03\SQL Backup\POSSQL_Server',N'.bak',N'05/15/2006 08:09:37'

The database file backup extension is .bak and the file is older than one day which was specified.
It is just not deleting the file.
We have deleted the maintenance task and recreated it with no effect and this has been happening for over a month.
Any help here would be greatly appreciated.

Carl
carlt@.gicw.org

I've been having the same problem for over three months now and keep checking back here to see if there's been a fix....

|||

http://blogs.msdn.com/sanchan/archive/2006/04/23/581640.aspx

This is addressed in SP1. Please look at the above blog for more information.

Gops Dwarak

|||

The trick is to replace .bak with *.*

It definitely works. :-)

|||

I tried to replace the extension "bak" with "*.*", "*" and ".*" I know this looks like the meeting of the disabled Smilie group, but you know what I mean. It definitely _DOESN′T_ delete any backups. However, when I execute the t-sql created by the maintenance Cleanup Task, it deletes the files.

Any suggestions?

|||Not working for me either. SP1 applied and all. Subdirectories for backups. .. trying to delete archived backups, in the form Filename.bak.7z. No worky worky!
Please advise? (Read: How hard is it to implement a functioning XSP?!)
|||

My solution was to take the T-SQL created by the cleanup task and modifying it slightly:

declare @.dt datetime

select @.dt=getdate()-5
EXECUTE master.dbo.xp_delete_file 0,N'E:\Backups',N'BAK',@.dt,1

The variable is used to check the age of the backup-files. In this case, all backups older than 5 days are deleted. The last Number (=1) gives the number of subdirectories to be searched in for .bak-files. AFAIK a maximum of 5 subdirs is supported.

Then you can take it and put it into an "execute SQL"-Task, take the package and schedule it or directly schedule it in the Job Scheduler of Management Studio as SQL-Statement

|||

The workaround above may or may not work, depending on what the problem is.

To get to the bottom of the problem - what is the exact error that you are getting? We can then search if this is a known problem. There were number of issues with Cleanup task. A few were fixed in SP1, like Gops has mentioned, more were fixed in SP2 which is in the CTP release right now. If you can, please install SQL Server 2005 SP2 CTP December from here: http://www.microsoft.com/sql/ctp.mspx and let us know if the problem sitll exists.

Regards,

Maciek Sarnowicz

|||

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

Thanks,

-lance

|||

There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

The patching procedure is a little complicated. Read this:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

|||

-lance wrote:

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

You are running SQL Server 2005 Service Pack 2 (9.00.3054.00).

You are running Windows Server 2003 Service Pack 1.

Hope this clears up your confusion.

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

|||

I was also having a similar issue. First the cleanup task would just not delete any files no matter where I pointed it to. I originally had put “.trn” in the file extension box. I then removed the . and everything deleted correctly. The problem we then ran into was that it would only delete from the top level folder that we pointed to. In another blog I read that I must check the Include first-level subfolders box to delete from these sub folders. The problem is that this checkbox is nowhere to be found on the maintenance cleanup task dialog box. We do have service pack 2 loaded and most of the updates but….?

I finally just got the T-SQL statement and created my own job.

This is the original statement:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18'

Is simply added a 1 to the end of the statement and made it:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18',1

This job runs great and deletes all the files on level below the top level folder. I did try adding a 2 to see if it would let me delete further levels but it errors out. Hope this helps somebody!

sql

Maintenance Cleanup task not functioning properly?

Using SQL Server 2005 Management Studio and backing up databases. When I add the task Maintenance Cleanup to remove database backups older then 1 day the task fails?
What part of this TSQL code does SQL Server 2005 Management Studio not understand?

EXECUTE master.dbo.xp_delete_file 0,N'\\pdxsql03\SQL Backup\POSSQL_Server',N'.bak',N'05/15/2006 08:09:37'

The database file backup extension is .bak and the file is older than one day which was specified.
It is just not deleting the file.
We have deleted the maintenance task and recreated it with no effect and this has been happening for over a month.
Any help here would be greatly appreciated.

Carl
carlt@.gicw.org

I've been having the same problem for over three months now and keep checking back here to see if there's been a fix....

|||

http://blogs.msdn.com/sanchan/archive/2006/04/23/581640.aspx

This is addressed in SP1. Please look at the above blog for more information.

Gops Dwarak

|||

The trick is to replace .bak with *.*

It definitely works. :-)

|||

I tried to replace the extension "bak" with "*.*", "*" and ".*" I know this looks like the meeting of the disabled Smilie group, but you know what I mean. It definitely _DOESN′T_ delete any backups. However, when I execute the t-sql created by the maintenance Cleanup Task, it deletes the files.

Any suggestions?

|||Not working for me either. SP1 applied and all. Subdirectories for backups. .. trying to delete archived backups, in the form Filename.bak.7z. No worky worky!
Please advise? (Read: How hard is it to implement a functioning XSP?!)|||

My solution was to take the T-SQL created by the cleanup task and modifying it slightly:

declare @.dt datetime

select @.dt=getdate()-5
EXECUTE master.dbo.xp_delete_file 0,N'E:\Backups',N'BAK',@.dt,1

The variable is used to check the age of the backup-files. In this case, all backups older than 5 days are deleted. The last Number (=1) gives the number of subdirectories to be searched in for .bak-files. AFAIK a maximum of 5 subdirs is supported.

Then you can take it and put it into an "execute SQL"-Task, take the package and schedule it or directly schedule it in the Job Scheduler of Management Studio as SQL-Statement

|||

The workaround above may or may not work, depending on what the problem is.

To get to the bottom of the problem - what is the exact error that you are getting? We can then search if this is a known problem. There were number of issues with Cleanup task. A few were fixed in SP1, like Gops has mentioned, more were fixed in SP2 which is in the CTP release right now. If you can, please install SQL Server 2005 SP2 CTP December from here: http://www.microsoft.com/sql/ctp.mspx and let us know if the problem sitll exists.

Regards,

Maciek Sarnowicz

|||

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

Thanks,

-lance

|||

There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

The patching procedure is a little complicated. Read this:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

|||

-lance wrote:

I tried to load up SP2 (thought I had already) to test this with it...

well, I show I am running SP1 and though I tried to update I could not get everything to update. Either it had a newer version (according to the updater) or I was told that components must be updated together.

SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

select @.@.version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

So now the question is how do I even get it to upgrade to SP2?

You are running SQL Server 2005 Service Pack 2 (9.00.3054.00).

You are running Windows Server 2003 Service Pack 1.

Hope this clears up your confusion.

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

|||

I was also having a similar issue. First the cleanup task would just not delete any files no matter where I pointed it to. I originally had put “.trn” in the file extension box. I then removed the . and everything deleted correctly. The problem we then ran into was that it would only delete from the top level folder that we pointed to. In another blog I read that I must check the Include first-level subfolders box to delete from these sub folders. The problem is that this checkbox is nowhere to be found on the maintenance cleanup task dialog box. We do have service pack 2 loaded and most of the updates but….?

I finally just got the T-SQL statement and created my own job.

This is the original statement:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18'

Is simply added a 1 to the end of the statement and made it:

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18',1

This job runs great and deletes all the files on level below the top level folder. I did try adding a 2 to see if it would let me delete further levels but it errors out. Hope this helps somebody!