maintenance plan glitch

I have a maintenance plan that backs up a customer
database and its transactions. The plan is supposed to
delete backup files after 1 week. The database backup
files delete fine. However, the transaction backup files
are not deleting. I tried recreating the maintenance plan
to see if that would enforce the settings, thinking that
there could be a setting corruption issue. However, this
still didn't work. Any ideas?
Thanks,
JasonBelow 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
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"jason" <anonymous@.discussions.microsoft.com> wrote in message
news:9a5d01c3ea96$9922e220$a401280a@.phx.gbl...

quote:

---

> I have a maintenance plan that backs up a customer
> database and its transactions. The plan is supposed to
> delete backup files after 1 week. The database backup
> files delete fine. However, the transaction backup files
> are not deleting. I tried recreating the maintenance plan
> to see if that would enforce the settings, thinking that
> there could be a setting corruption issue. However, this
> still didn't work. Any ideas?
> Thanks,
> Jason

---

maintenance plan glitch

I have a maintenance plan that backs up a customer
database and its transactions. The plan is supposed to
delete backup files after 1 week. The database backup
files delete fine. However, the transaction backup files
are not deleting. I tried recreating the maintenance plan
to see if that would enforce the settings, thinking that
there could be a setting corruption issue. However, this
still didn't work. Any ideas?
Thanks,
JasonBelow 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
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"jason" <anonymous@.discussions.microsoft.com> wrote in message
news:9a5d01c3ea96$9922e220$a401280a@.phx.gbl...
> I have a maintenance plan that backs up a customer
> database and its transactions. The plan is supposed to
> delete backup files after 1 week. The database backup
> files delete fine. However, the transaction backup files
> are not deleting. I tried recreating the maintenance plan
> to see if that would enforce the settings, thinking that
> there could be a setting corruption issue. However, this
> still didn't work. Any ideas?
> Thanks,
> Jasonsql

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 run

Hi - I have a strange situation where the Maintenance plan on a SQL 7, build 961 will not generate .BAK and .TRN files based on the maintenance plan (or at all). I have tried deleteing and recreating the maintenance plan, but, according to the system log, and to maintenance plan logs, the entire maintenance plan is not launching any tasks. However the application log whoes all the jobs as failing, but gives no furhter details.

However I have another server running build 623 that is working fine.

Any ideas from anyone? All help appreciated.Why use a maintenance plan - they give lots of problems.
Just schedule a backup job or jobs.
Have them call a stored procedure and put the backup statement in that.|||Here's the error from Eventlog:

SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'Webmon1'' (0x42E6C45B360CD611935D0002B330ABF9) - Status: Failed - Invoked on: 2/8/02 4:10:46 PM - Message: The job failed. The Job was invoked by User XXXXXX\Administrator. The last step to run was step 1 (Step 1).|||What happens if you try the same backup using a backup command?

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 backups fail

Hi, I have a scheduled maintenance plan that backs up my database (SQL 2000). The db is 108 Meg. Usually the backup files are around 89 Meg and the transaction log backups are around 4.5 Megs.

The problem is that every once a while the backup fails with the following error:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC SQL Server Driver][SQL Server]Write on 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\CRM_db_200312230100.BAK' failed, status = 112. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.

What usually happens is that the last backup that has failed has a size of 9 or 8 Gigs!

My guess is that a backup file gets corrupt (the size increases to 8 Gigs) and the server runs out of space and wont be able to do more backups. However I cant figure out why the backup file size increases to and gets corrupt.

Can anyone help me?What does the error log say...

did you a dbcc checkdb?|||Thanks for your reply, the error log says:

1.Source:Backup
BACKUP failed to complete the command BACKUP DATABASE [MYDB] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MYDB_db_200312230100.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

2.Source:spid54
BackupMedium::ReportIoError: write failure on backup device 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MYDB_db_200312230100.BAK'. Operating system error 112(There is not enough space on the disk.).

3.Source:spid54
Internal I/O request 0x12844D98: Op: Write, pBuffer: 0x12C40000, Size: 983040, Position: 14752256, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0xE11A00, OffsetHigh: 0x0, m_buf: 0x12C40000, m_len: 983040, m_actualBytes: 0, m_errcode: 112, BackupFile: C:\Program Files\Microsoft SQL

It looks like somthing happended to the last backup and the size increased to 8 Gigs and the disk ran out of space, but I can't figure why this happenes every once in a while.

Thanks



Originally posted by Brett Kaiser
What does the error log say...

did you a dbcc checkdb?|||You can't just assume that the backup gets corrupted without trying to restore it. Have you tried to do that?|||I havn't but there must be somthing wrong with it, since its 8 gig instead of 80 Megs, plus when I deleted it and made a backup manualy the size of the new backup was the regular 80 megs

Originally posted by rdjabarov
You can't just assume that the backup gets corrupted without trying to restore it. Have you tried to do that?

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 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

Im building a new sql server and would like to use my existing maintenance
plans. Where are the files stored and how do I use them on the new server?
Thanks,
HowardHi,
Maintenece plans will be stored in MSDB database.So if you restore the MSDB
database; then you should be able to
get all Maintenance plans and jobs.
If the SQL Server name is different from old server then you may need to
change the Originating server name in Sysjobs table.
Thanks
Hari
SQL Server MVP
"Howard" <howdy0909@.yahoo.com> wrote in message
news:uv9r$rHxGHA.3904@.TK2MSFTNGP02.phx.gbl...
> Im building a new sql server and would like to use my existing maintenance
> plans. Where are the files stored and how do I use them on the new server?
> Thanks,
> Howard
> shou

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