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 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 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 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 "Backup transaction Logs" running out of control

I have a miantenance plan that backs up transaction logs to another Server for the use in Log shipping. this has been running fine for months now, but the other day it has been spitting transaction logs that are 2.5 G over and over. about 20 G so far.

I tried to delete this plan and create a new one and reset Log shipping but it is still spitting out these 2.5 G files. I am really not sure what to look at next.

thanks for any help in advance.Why use a maintenance plan for this?
It is really easy to implement tsql and scheduled tasks and then you will at least know what it is doing.sql

Maintenace Plan Best Practice

My question is recommendation for setting up maintenace plans that are safe and complete. What do you recommned I do for setting up a plan that backs up, and deletes old files older than 5 days. Should I do anything else and why?

thank you!

If you want to backup the database and delete the old backup files you can create a maintenance plan to backup dbs and another task to "Cleanup task" to remove old .bak files.........refer this link,

http://www.sql-server-performance.com/articles/dba/creating_backup_jobs_p1.aspx

Thanxx

Deepak

|||

Thanks for the responce. I am pretty proficient at setting them up, however, I just am unsure about what is the best practice maintenace plan for my sql 2005 server as far as indexing and other cleanup type maintenance. Should I index and at what intervals, should I shrink the logs and databases and when and why?

Thanks!

Maintenace Plan Best Practice

My question is recommendation for setting up maintenace plans that are safe and complete. What do you recommned I do for setting up a plan that backs up, and deletes old files older than 5 days. Should I do anything else and why?

thank you!

If you want to backup the database and delete the old backup files you can create a maintenance plan to backup dbs and another task to "Cleanup task" to remove old .bak files.........refer this link,

http://www.sql-server-performance.com/articles/dba/creating_backup_jobs_p1.aspx

Thanxx

Deepak

|||

Thanks for the responce. I am pretty proficient at setting them up, however, I just am unsure about what is the best practice maintenace plan for my sql 2005 server as far as indexing and other cleanup type maintenance. Should I index and at what intervals, should I shrink the logs and databases and when and why?

Thanks!

sql