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 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 Fails but I can't figure out why? No log informa

I have a maintenance plan that is failing but I'm not sure why. Plan
consists of:
1 Backup Database
2 Cleanup Task
3 Reorganize Index Task
4 Rebuild Index Task
5 Shrink Database Task
6 Update Statistics Task
I have a notification message configured for errors. For some reason I
receive two emails. One states success while the other states failure. On
the success email I do not see any mention of steps 3,4 or 6 (from above). I
cannot find any additional information in the logs. Below are the two
emails. These emails are sent out back to back. Thanks in advance for your
help.
Terry
###################################################
Success Email
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047 Report
was generated on "MSSQL".
Maintenance Plan: Database Backups
Duration: 08:26:44
Status: Succeeded.
Details:
Back Up Database (Full) (MSSQL)
Task start: 9/28/2007 9:00 PM.
Task end: 9/28/2007 9:07 PM.
Success
BAK Cleanup Task (MSSQL)
Task start: 9/28/2007 9:07 PM.
Task end: 9/28/2007 9:07 PM.
Success
Shrink Database Task (MSSQL)
Task start: 9/29/2007 3:33 AM.
Task end: 9/29/2007 3:35 AM.
Success
###################################################
Failed Email
OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01 PM
DURATION: 8 hours, 26 minutes, 51 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 1
(BackupDatabaseJob). The last step to run was step 1 (Subplan).Hello Terry!
Check out Jobs' histories from the Jobs node under SQL Server Agent. There,
you will find the one which doesn't work. It stops your maintenance plan.
Besides, you better setup your maintenance plan one by one, not an "all in
one" package.
Ekrem Ã?nsoy
"Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
news:F4444EBE-47FE-4C21-804E-71CB320513D7@.microsoft.com...
>I have a maintenance plan that is failing but I'm not sure why. Plan
> consists of:
> 1 Backup Database
> 2 Cleanup Task
> 3 Reorganize Index Task
> 4 Rebuild Index Task
> 5 Shrink Database Task
> 6 Update Statistics Task
> I have a notification message configured for errors. For some reason I
> receive two emails. One states success while the other states failure.
> On
> the success email I do not see any mention of steps 3,4 or 6 (from above).
> I
> cannot find any additional information in the logs. Below are the two
> emails. These emails are sent out back to back. Thanks in advance for
> your
> help.
> Terry
> ###################################################
> Success Email
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047 Report
> was generated on "MSSQL".
> Maintenance Plan: Database Backups
> Duration: 08:26:44
> Status: Succeeded.
> Details:
> Back Up Database (Full) (MSSQL)
> Task start: 9/28/2007 9:00 PM.
> Task end: 9/28/2007 9:07 PM.
> Success
>
> BAK Cleanup Task (MSSQL)
> Task start: 9/28/2007 9:07 PM.
> Task end: 9/28/2007 9:07 PM.
> Success
>
> Shrink Database Task (MSSQL)
> Task start: 9/29/2007 3:33 AM.
> Task end: 9/29/2007 3:35 AM.
> Success
>
> ###################################################
> Failed Email
> OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01 PM
> DURATION: 8 hours, 26 minutes, 51 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 1
> (BackupDatabaseJob). The last step to run was step 1 (Subplan).
>|||Hi Ekrem,
Thank you for the reply. I checked out the history logs for the job and
what is strange is that there are no entries? I right clicked on the job and
selected 'View Histories' but no entries. Can you think of a reason why I'm
not seeing any data?
Thanks,
Terry
"Ekrem Ã?nsoy" wrote:
> Hello Terry!
>
> Check out Jobs' histories from the Jobs node under SQL Server Agent. There,
> you will find the one which doesn't work. It stops your maintenance plan.
> Besides, you better setup your maintenance plan one by one, not an "all in
> one" package.
>
> --
> Ekrem Ã?nsoy
>
> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
> news:F4444EBE-47FE-4C21-804E-71CB320513D7@.microsoft.com...
> >I have a maintenance plan that is failing but I'm not sure why. Plan
> > consists of:
> > 1 Backup Database
> > 2 Cleanup Task
> > 3 Reorganize Index Task
> > 4 Rebuild Index Task
> > 5 Shrink Database Task
> > 6 Update Statistics Task
> >
> > I have a notification message configured for errors. For some reason I
> > receive two emails. One states success while the other states failure.
> > On
> > the success email I do not see any mention of steps 3,4 or 6 (from above).
> > I
> > cannot find any additional information in the logs. Below are the two
> > emails. These emails are sent out back to back. Thanks in advance for
> > your
> > help.
> >
> > Terry
> >
> > ###################################################
> > Success Email
> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047 Report
> > was generated on "MSSQL".
> > Maintenance Plan: Database Backups
> > Duration: 08:26:44
> > Status: Succeeded.
> > Details:
> > Back Up Database (Full) (MSSQL)
> > Task start: 9/28/2007 9:00 PM.
> > Task end: 9/28/2007 9:07 PM.
> > Success
> >
> >
> > BAK Cleanup Task (MSSQL)
> > Task start: 9/28/2007 9:07 PM.
> > Task end: 9/28/2007 9:07 PM.
> > Success
> >
> >
> > Shrink Database Task (MSSQL)
> > Task start: 9/29/2007 3:33 AM.
> > Task end: 9/29/2007 3:35 AM.
> > Success
> >
> >
> > ###################################################
> > Failed Email
> > OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01 PM
> > DURATION: 8 hours, 26 minutes, 51 seconds
> > STATUS: Failed
> > MESSAGES: The job failed. The Job was invoked by Schedule 1
> > (BackupDatabaseJob). The last step to run was step 1 (Subplan).
> >
>|||Hi Terry,
Could you tell me exactly what version and edition of SQL Server do you use?
First two reasons I can think of:
1- You did not select the checkbox just at the left side of the job name
from the Log File Viewer window.
2- The Jobs have never worked (in your case, some of them worked, so this
must not be valid for you)
P.S.
You use SSMS, not SSMSE right?
Ekrem Ã?nsoy
"Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
news:BA438105-C888-45E8-8E7E-623852F82345@.microsoft.com...
> Hi Ekrem,
> Thank you for the reply. I checked out the history logs for the job and
> what is strange is that there are no entries? I right clicked on the job
> and
> selected 'View Histories' but no entries. Can you think of a reason why
> I'm
> not seeing any data?
> Thanks,
> Terry
> "Ekrem Ã?nsoy" wrote:
>> Hello Terry!
>>
>> Check out Jobs' histories from the Jobs node under SQL Server Agent.
>> There,
>> you will find the one which doesn't work. It stops your maintenance plan.
>> Besides, you better setup your maintenance plan one by one, not an "all
>> in
>> one" package.
>>
>> --
>> Ekrem Ã?nsoy
>>
>> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
>> news:F4444EBE-47FE-4C21-804E-71CB320513D7@.microsoft.com...
>> >I have a maintenance plan that is failing but I'm not sure why. Plan
>> > consists of:
>> > 1 Backup Database
>> > 2 Cleanup Task
>> > 3 Reorganize Index Task
>> > 4 Rebuild Index Task
>> > 5 Shrink Database Task
>> > 6 Update Statistics Task
>> >
>> > I have a notification message configured for errors. For some reason I
>> > receive two emails. One states success while the other states failure.
>> > On
>> > the success email I do not see any mention of steps 3,4 or 6 (from
>> > above).
>> > I
>> > cannot find any additional information in the logs. Below are the two
>> > emails. These emails are sent out back to back. Thanks in advance for
>> > your
>> > help.
>> >
>> > Terry
>> >
>> > ###################################################
>> > Success Email
>> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
>> > Report
>> > was generated on "MSSQL".
>> > Maintenance Plan: Database Backups
>> > Duration: 08:26:44
>> > Status: Succeeded.
>> > Details:
>> > Back Up Database (Full) (MSSQL)
>> > Task start: 9/28/2007 9:00 PM.
>> > Task end: 9/28/2007 9:07 PM.
>> > Success
>> >
>> >
>> > BAK Cleanup Task (MSSQL)
>> > Task start: 9/28/2007 9:07 PM.
>> > Task end: 9/28/2007 9:07 PM.
>> > Success
>> >
>> >
>> > Shrink Database Task (MSSQL)
>> > Task start: 9/29/2007 3:33 AM.
>> > Task end: 9/29/2007 3:35 AM.
>> > Success
>> >
>> >
>> > ###################################################
>> > Failed Email
>> > OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01 PM
>> > DURATION: 8 hours, 26 minutes, 51 seconds
>> > STATUS: Failed
>> > MESSAGES: The job failed. The Job was invoked by Schedule 1
>> > (BackupDatabaseJob). The last step to run was step 1 (Subplan).
>> >|||Hi Ekrem,
Using SQL Server 2005 (9.00.2047) and SQL Server Management Studio.
This is what I've did:
Expanded 'SQL Server Agent', Expanded Jobs, Right clicked on job (Database
Backups.Subplan) and selected 'View History'. When the dialog displays the
job is selected.
Thanks,
Terry
"Ekrem Ã?nsoy" wrote:
> Hi Terry,
>
> Could you tell me exactly what version and edition of SQL Server do you use?
> First two reasons I can think of:
> 1- You did not select the checkbox just at the left side of the job name
> from the Log File Viewer window.
> 2- The Jobs have never worked (in your case, some of them worked, so this
> must not be valid for you)
> P.S.
> You use SSMS, not SSMSE right?
>
> --
> Ekrem Ã?nsoy
>
> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
> news:BA438105-C888-45E8-8E7E-623852F82345@.microsoft.com...
> > Hi Ekrem,
> > Thank you for the reply. I checked out the history logs for the job and
> > what is strange is that there are no entries? I right clicked on the job
> > and
> > selected 'View Histories' but no entries. Can you think of a reason why
> > I'm
> > not seeing any data?
> > Thanks,
> > Terry
> >
> > "Ekrem Ã?nsoy" wrote:
> >
> >> Hello Terry!
> >>
> >>
> >> Check out Jobs' histories from the Jobs node under SQL Server Agent.
> >> There,
> >> you will find the one which doesn't work. It stops your maintenance plan.
> >>
> >> Besides, you better setup your maintenance plan one by one, not an "all
> >> in
> >> one" package.
> >>
> >>
> >> --
> >> Ekrem Ã?nsoy
> >>
> >>
> >>
> >> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
> >> news:F4444EBE-47FE-4C21-804E-71CB320513D7@.microsoft.com...
> >> >I have a maintenance plan that is failing but I'm not sure why. Plan
> >> > consists of:
> >> > 1 Backup Database
> >> > 2 Cleanup Task
> >> > 3 Reorganize Index Task
> >> > 4 Rebuild Index Task
> >> > 5 Shrink Database Task
> >> > 6 Update Statistics Task
> >> >
> >> > I have a notification message configured for errors. For some reason I
> >> > receive two emails. One states success while the other states failure.
> >> > On
> >> > the success email I do not see any mention of steps 3,4 or 6 (from
> >> > above).
> >> > I
> >> > cannot find any additional information in the logs. Below are the two
> >> > emails. These emails are sent out back to back. Thanks in advance for
> >> > your
> >> > help.
> >> >
> >> > Terry
> >> >
> >> > ###################################################
> >> > Success Email
> >> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> >> > Report
> >> > was generated on "MSSQL".
> >> > Maintenance Plan: Database Backups
> >> > Duration: 08:26:44
> >> > Status: Succeeded.
> >> > Details:
> >> > Back Up Database (Full) (MSSQL)
> >> > Task start: 9/28/2007 9:00 PM.
> >> > Task end: 9/28/2007 9:07 PM.
> >> > Success
> >> >
> >> >
> >> > BAK Cleanup Task (MSSQL)
> >> > Task start: 9/28/2007 9:07 PM.
> >> > Task end: 9/28/2007 9:07 PM.
> >> > Success
> >> >
> >> >
> >> > Shrink Database Task (MSSQL)
> >> > Task start: 9/29/2007 3:33 AM.
> >> > Task end: 9/29/2007 3:35 AM.
> >> > Success
> >> >
> >> >
> >> > ###################################################
> >> > Failed Email
> >> > OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01 PM
> >> > DURATION: 8 hours, 26 minutes, 51 seconds
> >> > STATUS: Failed
> >> > MESSAGES: The job failed. The Job was invoked by Schedule 1
> >> > (BackupDatabaseJob). The last step to run was step 1 (Subplan).
> >> >
> >>
>|||Hi Terry!
Can you check the Filter setting in the Log Viewer window? If it's set to
some values then click Clear button in the Filter Settings window and click
OK.
Check if other logs are clear or do they have records from the Log File
Viewer? (SQL Agent logs for example)
If it is not your situation, then can you take a look at your Instance path?
Check if log files are stored or not.
For Example: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
Note:
I once faced this problem but in that situation the person who met this
problem was using SSMSE and when he started using SSMS he started seeing
logs.
--
Ekrem Ã?nsoy
"Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
news:0725AFDB-ED8A-4F3E-8712-EDAA3F097B15@.microsoft.com...
> Hi Ekrem,
> Using SQL Server 2005 (9.00.2047) and SQL Server Management Studio.
> This is what I've did:
> Expanded 'SQL Server Agent', Expanded Jobs, Right clicked on job (Database
> Backups.Subplan) and selected 'View History'. When the dialog displays
> the
> job is selected.
> Thanks,
> Terry
>
> "Ekrem Ã?nsoy" wrote:
>> Hi Terry,
>>
>> Could you tell me exactly what version and edition of SQL Server do you
>> use?
>> First two reasons I can think of:
>> 1- You did not select the checkbox just at the left side of the job name
>> from the Log File Viewer window.
>> 2- The Jobs have never worked (in your case, some of them worked, so this
>> must not be valid for you)
>> P.S.
>> You use SSMS, not SSMSE right?
>>
>> --
>> Ekrem Ã?nsoy
>>
>> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
>> news:BA438105-C888-45E8-8E7E-623852F82345@.microsoft.com...
>> > Hi Ekrem,
>> > Thank you for the reply. I checked out the history logs for the job
>> > and
>> > what is strange is that there are no entries? I right clicked on the
>> > job
>> > and
>> > selected 'View Histories' but no entries. Can you think of a reason
>> > why
>> > I'm
>> > not seeing any data?
>> > Thanks,
>> > Terry
>> >
>> > "Ekrem Ã?nsoy" wrote:
>> >
>> >> Hello Terry!
>> >>
>> >>
>> >> Check out Jobs' histories from the Jobs node under SQL Server Agent.
>> >> There,
>> >> you will find the one which doesn't work. It stops your maintenance
>> >> plan.
>> >>
>> >> Besides, you better setup your maintenance plan one by one, not an
>> >> "all
>> >> in
>> >> one" package.
>> >>
>> >>
>> >> --
>> >> Ekrem Ã?nsoy
>> >>
>> >>
>> >>
>> >> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
>> >> news:F4444EBE-47FE-4C21-804E-71CB320513D7@.microsoft.com...
>> >> >I have a maintenance plan that is failing but I'm not sure why. Plan
>> >> > consists of:
>> >> > 1 Backup Database
>> >> > 2 Cleanup Task
>> >> > 3 Reorganize Index Task
>> >> > 4 Rebuild Index Task
>> >> > 5 Shrink Database Task
>> >> > 6 Update Statistics Task
>> >> >
>> >> > I have a notification message configured for errors. For some
>> >> > reason I
>> >> > receive two emails. One states success while the other states
>> >> > failure.
>> >> > On
>> >> > the success email I do not see any mention of steps 3,4 or 6 (from
>> >> > above).
>> >> > I
>> >> > cannot find any additional information in the logs. Below are the
>> >> > two
>> >> > emails. These emails are sent out back to back. Thanks in advance
>> >> > for
>> >> > your
>> >> > help.
>> >> >
>> >> > Terry
>> >> >
>> >> > ###################################################
>> >> > Success Email
>> >> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
>> >> > Report
>> >> > was generated on "MSSQL".
>> >> > Maintenance Plan: Database Backups
>> >> > Duration: 08:26:44
>> >> > Status: Succeeded.
>> >> > Details:
>> >> > Back Up Database (Full) (MSSQL)
>> >> > Task start: 9/28/2007 9:00 PM.
>> >> > Task end: 9/28/2007 9:07 PM.
>> >> > Success
>> >> >
>> >> >
>> >> > BAK Cleanup Task (MSSQL)
>> >> > Task start: 9/28/2007 9:07 PM.
>> >> > Task end: 9/28/2007 9:07 PM.
>> >> > Success
>> >> >
>> >> >
>> >> > Shrink Database Task (MSSQL)
>> >> > Task start: 9/29/2007 3:33 AM.
>> >> > Task end: 9/29/2007 3:35 AM.
>> >> > Success
>> >> >
>> >> >
>> >> > ###################################################
>> >> > Failed Email
>> >> > OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01
>> >> > PM
>> >> > DURATION: 8 hours, 26 minutes, 51 seconds
>> >> > STATUS: Failed
>> >> > MESSAGES: The job failed. The Job was invoked by Schedule 1
>> >> > (BackupDatabaseJob). The last step to run was step 1 (Subplan).
>> >> >
>> >>|||Ekrem Ã?nsoy wrote:
> Hi Terry!
>
> Can you check the Filter setting in the Log Viewer window? If it's set
> to some values then click Clear button in the Filter Settings window
> and click OK.
> Check if other logs are clear or do they have records from the Log
> File Viewer? (SQL Agent logs for example)
> If it is not your situation, then can you take a look at your Instance
> path? Check if log files are stored or not.
> For Example: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
> Note:
> I once faced this problem but in that situation the person who met
> this problem was using SSMSE and when he started using SSMS he started
> seeing logs.
>
Instead of viewing history on the SQL Agent job - view the history on
the maintenance plan. The history here will show each step in the
maintenance plan and should identify why the other steps are failing.
My guess as to why you are getting two messages is that the steps in the
maintenance plan are not sequenced. In the maintenance plan editor, you
need to drag the green arrow (go to next step on success) or the blue
arrow (go to next step on completion) and perform each step in the
maintenance plan in sequence. If your jobs are not sequenced - those
other steps are probably failing because they cannot obtain a lock or
are blocked by the other steps.
And finally, you do not need to perform all of those steps - and not in
the order you listed them. I generally run the following:
Integrity Checks (DBCC CHECKDB)
On Success: Rebuild/Reorganize Indexes
On Success: Backup Database
On Success: Maintenance Plan Cleanup Task
Depending upon the system, I may include a step to Update Statistics and
would include that step right before the backup. You should not shrink
the database as it will only grow again, especially when you are
rebuilding indexes. Make sure your database has been sized large enough
so that the rebuild index operation does not grow the database
(generally 20% free).
Jeff|||Hi Jeffrey,
Thanks for the reply. All the tasks are sequenced (blue connections). I
took a look at the history logs as you mentioned (thanks) but what is strange
is not all the task are contained in the log but yet the ones that are there
show success. I have turned on extended logging and I initially removed the
'Reorganize Index Task' and 'Rebuild Index Task' steps. When I did this
everything worked and all 4 entries are listed in the log. I later added
back the 'Reorganize Index Task' and the job failed. As mentioned above when
I view the log I do not see all the steps and the ones that are listed show
success. The 'Reorganize Index Task' is not listed. No failed steps are
listed. Strange deal.
Thanks,
Terry
"Jeffrey Williams" wrote:
> Ekrem Ã?nsoy wrote:
> > Hi Terry!
> >
> >
> > Can you check the Filter setting in the Log Viewer window? If it's set
> > to some values then click Clear button in the Filter Settings window
> > and click OK.
> >
> > Check if other logs are clear or do they have records from the Log
> > File Viewer? (SQL Agent logs for example)
> >
> > If it is not your situation, then can you take a look at your Instance
> > path? Check if log files are stored or not.
> > For Example: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
> >
> > Note:
> > I once faced this problem but in that situation the person who met
> > this problem was using SSMSE and when he started using SSMS he started
> > seeing logs.
> >
> Instead of viewing history on the SQL Agent job - view the history on
> the maintenance plan. The history here will show each step in the
> maintenance plan and should identify why the other steps are failing.
> My guess as to why you are getting two messages is that the steps in the
> maintenance plan are not sequenced. In the maintenance plan editor, you
> need to drag the green arrow (go to next step on success) or the blue
> arrow (go to next step on completion) and perform each step in the
> maintenance plan in sequence. If your jobs are not sequenced - those
> other steps are probably failing because they cannot obtain a lock or
> are blocked by the other steps.
> And finally, you do not need to perform all of those steps - and not in
> the order you listed them. I generally run the following:
> Integrity Checks (DBCC CHECKDB)
> On Success: Rebuild/Reorganize Indexes
> On Success: Backup Database
> On Success: Maintenance Plan Cleanup Task
> Depending upon the system, I may include a step to Update Statistics and
> would include that step right before the backup. You should not shrink
> the database as it will only grow again, especially when you are
> rebuilding indexes. Make sure your database has been sized large enough
> so that the rebuild index operation does not grow the database
> (generally 20% free).
> Jeff
>

maintenance plan fails after upgrade to 2000

We upgraded to sql 2000 last night and now the maintenance
plans do not work. I'm using simple model, no tlog backup.
The error I'm working with is this:
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The
step failed.
The log looks like this:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server '1s' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'DB Maintenance Plan1' on
7/31/2003 2:00:02 AM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database
state cannot be changed while other users are using the
database 'r'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption
command failed.
[1] Database rbcs: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[2] Database t: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 2 secs **
[3] Database r: Database Backup...
The backup was not performed since data verification
errors were found.
[4] Database t: Database Backup...
Destination: [D:\mssql7\BACKUP\t\t_db_200307310200.BAK]
** Execution Time: 0 hrs, 0 mins, 14 secs **
[5] Database t: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 5 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan1' on
7/31/2003 2:00:23 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)D
You are geting this error because the maintenance plan is
trying to put the database into single user mode. It fails
because there is a user (or at least an open transaction)
in the database. It is attempting to do this so it can
perform a dbcc checkdb with repair.
If this worked before, perhaps something in the upgrade
has lead to open transactions? Or have you changed the
time the job runs?
If you change the dbcc part of the maint plan, not to do
the repair it would not try to put the database into
single user mode. Check the output from your dbcc checkdb
and if you find you do need to run with repair, schedule a
job to run at a time when you can switch to single user
mode.
Hope this helps
John|||The plan was setup under sql 7 so I guess there wasnt a
problem in that version. I will remove the repair option,
but I suppose I'll have to run dbcc later.
Thanks
>--Original Message--
>D
>You are geting this error because the maintenance plan is
>trying to put the database into single user mode. It
fails
>because there is a user (or at least an open transaction)
>in the database. It is attempting to do this so it can
>perform a dbcc checkdb with repair.
>If this worked before, perhaps something in the upgrade
>has lead to open transactions? Or have you changed the
>time the job runs?
>If you change the dbcc part of the maint plan, not to do
>the repair it would not try to put the database into
>single user mode. Check the output from your dbcc checkdb
>and if you find you do need to run with repair, schedule
a
>job to run at a time when you can switch to single user
>mode.
>Hope this helps
>John
>.
>

Maintenance Plan Failed SQL2005

I am trying to create a simple Maintenance Plan using the Wizard to
backup my databases. When I click finish everything works fine apart
from the last step, "Saving Maintenance Plan". The report I get is
this
Maintenance Plan Wizard Progress
- Creating maintenance plan "MaintenancePlan" (Success)
- Adding tasks to the maintenance plan (Success)
- Adding scheduling options (Success)
- Adding reporting options (Success)
- Saving maintenance plan "MaintenancePlan" (Error)
Messages
Saving maintenance plan failed.
--
ADDITIONAL INFORMATION:
Class not registered
--
Class not registered
I have a standard installation of SQL2005 Enterprise and have made sure
SSIS is installed and running. Any ideas on what Class is not
registered and how to fix this would be greatly appreciated.Although I haven't fixed the problem or found out which class it is
looking for I have found that this is only happening on 1 workstation.

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 bug on SQL Server 2005

Hello, everyone:

I have a strange trouble on SQL Server 2005 on XP/local and Win 2003/server. When I connect local database and create a backup plan named by "backup plan", I can see the name under "maintenance plan" and "Jobs" under SQL Server Agent. But if I connect to server database and do the samething, I cannot see the name under "maintenance plan". I can see the name under "Jobs" under SQL Server Agent. But cannot delete this job, and message:

Drop failed for job "Backup Plan".(Microsoft.SqlServer.Smo).

Did someone meet this bug and have suggestions?

Thanks

ZYTPermissions? Do you have the same permissions to the server as you do to your local SQL Server?|||Hi, Pootle:

Thanks for reply.

I have sysadmin permissions to the server.

ZYT

Permissions? Do you have the same permissions to the server as you do to your local SQL Server?|||Hi, Pootle:

The server is the remote one that our company just got from someone else. I login by "Remote Desktop Connection" and check SQL Server. From Start - Programs - SQL Server 2005, there is only "Configuration Tools" available, no other componences like "SQL Server Management Stadio". This is first time I sam and confused. What was happened?

When I connect server from SQL Server, I can all tables and stored procedrues.

Thanks

ZYT

Permissions? Do you have the same permissions to the server as you do to your local SQL Server?|||As I remember, there was a bug in SQL 2005 SP2 that affected Maintenance plans. Check to see what hotfixes are available.

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 fails for large databases

Hi everyone,
I have 3 different SQL Servers, all running SQL Server 2000 SP3a. They
are all hosted on Windows 2000 Advanced Server. I have about 20
databases in all spread out among the 3 servers, ranging from 10MB to
100GB in size. I've had the same maintenance plans in place for at
least a year, and I'm having some trouble now.
In my maintenance plans, I do a nightly full backup of each database to
a network location. All 3 SQL servers back up to the same network
fileserver, via UNC. Until recently, everything was working fine, but
now it seems my 3 largest databases (all 10GB or larger) are showing a
failure in the maintenance plan history. The backups themselves are
completing fine, though, and I did a test restore from them to confirm
that. But the maintenance plan is returning the following errors:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3013: [Microsoft][ODBC
SQL Server Driver][SQL Server]BACKUP DATABASE is terminating
abnormally.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
SQL Server Driver][SQL Server]Write on '\\backup-server\Data-Backup\SQL
Backups\LW1SQL1\MyDatabase_db_200508090449.BAK' failed, status = 64.
See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
Obviously, because this is affecting all 3 of my SQL servers, it most
likely has something to do with the destination server. From what I
can tell, somewhere near the end of the backup of the large databases,
the destination server becomes unavailable to the SQL server. I
disabled all other services on that server (Antivirus, Tape Backup
services, etc) and did another test, but with the same results. I
installed updated NIC drivers and RAID controller drivers as well. I
can manually copy large files over the network to this location with no
problems.
Does anyone have any suggestions? I am baffled. Thank you.
george sarlas
gsarlas@.yahoo.com
A few more details I forgot to post...
All SQL Server services are running under a Windows account with full
permissions to the UNC. Also, all databases in question are set to
FULL recovery mode. We are successfully doing transaction log backups
via log shipping plans. Thanks.
george sarlas
gsarlas@.yahoo.com
|||C:\>net helpmsg 64
The specified network name is no longer available.
I have found it mostly to be one of 2 issues:
1. Network communication problems with switches/routers.
2. The destination server is not able to contact the domain controller, as
all of it's bandwidth is being used by the servers backing up to it, so it
aborts the connection.
#2 is solved by having a dedicated network over which backups are copied,
and not over the network interface that is used by SQL Clients and general
domain chatter.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<gsarlas@.yahoo.com> wrote in message
news:1123704481.634460.129430@.z14g2000cwz.googlegr oups.com...
> Hi everyone,
> I have 3 different SQL Servers, all running SQL Server 2000 SP3a. They
> are all hosted on Windows 2000 Advanced Server. I have about 20
> databases in all spread out among the 3 servers, ranging from 10MB to
> 100GB in size. I've had the same maintenance plans in place for at
> least a year, and I'm having some trouble now.
> In my maintenance plans, I do a nightly full backup of each database to
> a network location. All 3 SQL servers back up to the same network
> fileserver, via UNC. Until recently, everything was working fine, but
> now it seems my 3 largest databases (all 10GB or larger) are showing a
> failure in the maintenance plan history. The backups themselves are
> completing fine, though, and I did a test restore from them to confirm
> that. But the maintenance plan is returning the following errors:
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3013: [Microsoft][ODBC
> SQL Server Driver][SQL Server]BACKUP DATABASE is terminating
> abnormally.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Write on '\\backup-server\Data-Backup\SQL
> Backups\LW1SQL1\MyDatabase_db_200508090449.BAK' failed, status = 64.
> See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
>
> Obviously, because this is affecting all 3 of my SQL servers, it most
> likely has something to do with the destination server. From what I
> can tell, somewhere near the end of the backup of the large databases,
> the destination server becomes unavailable to the SQL server. I
> disabled all other services on that server (Antivirus, Tape Backup
> services, etc) and did another test, but with the same results. I
> installed updated NIC drivers and RAID controller drivers as well. I
> can manually copy large files over the network to this location with no
> problems.
> Does anyone have any suggestions? I am baffled. Thank you.
> --
> george sarlas
> gsarlas@.yahoo.com
>
sql

Maintenance Plan backup fails for large databases

Hi everyone,
I have 3 different SQL Servers, all running SQL Server 2000 SP3a. They
are all hosted on Windows 2000 Advanced Server. I have about 20
databases in all spread out among the 3 servers, ranging from 10MB to
100GB in size. I've had the same maintenance plans in place for at
least a year, and I'm having some trouble now.
In my maintenance plans, I do a nightly full backup of each database to
a network location. All 3 SQL servers back up to the same network
fileserver, via UNC. Until recently, everything was working fine, but
now it seems my 3 largest databases (all 10GB or larger) are showing a
failure in the maintenance plan history. The backups themselves are
completing fine, though, and I did a test restore from them to confirm
that. But the maintenance plan is returning the following errors:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3013: [Microsoft][ODBC
SQL Server Driver][SQL Server]BACKUP DATABASE is terminating
abnormally.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
SQL Server Driver][SQL Server]Write on '\\backup-server\Data-Backup\SQL
Backups\LW1SQL1\MyDatabase_db_200508090449.BAK' failed, status = 64.
See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
Obviously, because this is affecting all 3 of my SQL servers, it most
likely has something to do with the destination server. From what I
can tell, somewhere near the end of the backup of the large databases,
the destination server becomes unavailable to the SQL server. I
disabled all other services on that server (Antivirus, Tape Backup
services, etc) and did another test, but with the same results. I
installed updated NIC drivers and RAID controller drivers as well. I
can manually copy large files over the network to this location with no
problems.
Does anyone have any suggestions? I am baffled. Thank you.
--
george sarlas
gsarlas@.yahoo.comA few more details I forgot to post...
All SQL Server services are running under a Windows account with full
permissions to the UNC. Also, all databases in question are set to
FULL recovery mode. We are successfully doing transaction log backups
via log shipping plans. Thanks.
--
george sarlas
gsarlas@.yahoo.com|||C:\>net helpmsg 64
The specified network name is no longer available.
I have found it mostly to be one of 2 issues:
1. Network communication problems with switches/routers.
2. The destination server is not able to contact the domain controller, as
all of it's bandwidth is being used by the servers backing up to it, so it
aborts the connection.
#2 is solved by having a dedicated network over which backups are copied,
and not over the network interface that is used by SQL Clients and general
domain chatter.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<gsarlas@.yahoo.com> wrote in message
news:1123704481.634460.129430@.z14g2000cwz.googlegroups.com...
> Hi everyone,
> I have 3 different SQL Servers, all running SQL Server 2000 SP3a. They
> are all hosted on Windows 2000 Advanced Server. I have about 20
> databases in all spread out among the 3 servers, ranging from 10MB to
> 100GB in size. I've had the same maintenance plans in place for at
> least a year, and I'm having some trouble now.
> In my maintenance plans, I do a nightly full backup of each database to
> a network location. All 3 SQL servers back up to the same network
> fileserver, via UNC. Until recently, everything was working fine, but
> now it seems my 3 largest databases (all 10GB or larger) are showing a
> failure in the maintenance plan history. The backups themselves are
> completing fine, though, and I did a test restore from them to confirm
> that. But the maintenance plan is returning the following errors:
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3013: [Microsoft][ODBC
> SQL Server Driver][SQL Server]BACKUP DATABASE is terminating
> abnormally.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Write on '\\backup-server\Data-Backup\SQL
> Backups\LW1SQL1\MyDatabase_db_200508090449.BAK' failed, status = 64.
> See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
>
> Obviously, because this is affecting all 3 of my SQL servers, it most
> likely has something to do with the destination server. From what I
> can tell, somewhere near the end of the backup of the large databases,
> the destination server becomes unavailable to the SQL server. I
> disabled all other services on that server (Antivirus, Tape Backup
> services, etc) and did another test, but with the same results. I
> installed updated NIC drivers and RAID controller drivers as well. I
> can manually copy large files over the network to this location with no
> problems.
> Does anyone have any suggestions? I am baffled. Thank you.
> --
> george sarlas
> gsarlas@.yahoo.com
>

Maintenance Plan backup fails for large databases

Hi everyone,
I have 3 different SQL Servers, all running SQL Server 2000 SP3a. They
are all hosted on Windows 2000 Advanced Server. I have about 20
databases in all spread out among the 3 servers, ranging from 10MB to
100GB in size. I've had the same maintenance plans in place for at
least a year, and I'm having some trouble now.
In my maintenance plans, I do a nightly full backup of each database to
a network location. All 3 SQL servers back up to the same network
fileserver, via UNC. Until recently, everything was working fine, but
now it seems my 3 largest databases (all 10GB or larger) are showing a
failure in the maintenance plan history. The backups themselves are
completing fine, though, and I did a test restore from them to confirm
that. But the maintenance plan is returning the following errors:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3013: [Microsoft]&#
91;ODBC
SQL Server Driver][SQL Server]BACKUP DATABASE is terminating
abnormally.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft]&#
91;ODBC
SQL Server Driver][SQL Server]Write on '\\backup-server\Data-Backup\SQL
Backups\LW1SQL1\MyDatabase_db_2005080904
49.BAK' failed, status = 64.
See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE i
s
terminating abnormally.
Obviously, because this is affecting all 3 of my SQL servers, it most
likely has something to do with the destination server. From what I
can tell, somewhere near the end of the backup of the large databases,
the destination server becomes unavailable to the SQL server. I
disabled all other services on that server (Antivirus, Tape Backup
services, etc) and did another test, but with the same results. I
installed updated NIC drivers and RAID controller drivers as well. I
can manually copy large files over the network to this location with no
problems.
Does anyone have any suggestions? I am baffled. Thank you.
george sarlas
gsarlas@.yahoo.comA few more details I forgot to post...
All SQL Server services are running under a Windows account with full
permissions to the UNC. Also, all databases in question are set to
FULL recovery mode. We are successfully doing transaction log backups
via log shipping plans. Thanks.
george sarlas
gsarlas@.yahoo.com|||C:\>net helpmsg 64
The specified network name is no longer available.
I have found it mostly to be one of 2 issues:
1. Network communication problems with switches/routers.
2. The destination server is not able to contact the domain controller, as
all of it's bandwidth is being used by the servers backing up to it, so it
aborts the connection.
#2 is solved by having a dedicated network over which backups are copied,
and not over the network interface that is used by SQL Clients and general
domain chatter.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<gsarlas@.yahoo.com> wrote in message
news:1123704481.634460.129430@.z14g2000cwz.googlegroups.com...
> Hi everyone,
> I have 3 different SQL Servers, all running SQL Server 2000 SP3a. They
> are all hosted on Windows 2000 Advanced Server. I have about 20
> databases in all spread out among the 3 servers, ranging from 10MB to
> 100GB in size. I've had the same maintenance plans in place for at
> least a year, and I'm having some trouble now.
> In my maintenance plans, I do a nightly full backup of each database to
> a network location. All 3 SQL servers back up to the same network
> fileserver, via UNC. Until recently, everything was working fine, but
> now it seems my 3 largest databases (all 10GB or larger) are showing a
> failure in the maintenance plan history. The backups themselves are
> completing fine, though, and I did a test restore from them to confirm
> that. But the maintenance plan is returning the following errors:
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3013: [Microsoft]
[ODBC
> SQL Server Driver][SQL Server]BACKUP DATABASE is terminating
> abnormally.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft]
[ODBC
> SQL Server Driver][SQL Server]Write on '\\backup-server\Data-Backup\SQ
L
> Backups\LW1SQL1\MyDatabase_db_2005080904
49.BAK' failed, status = 64.
> See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE
is
> terminating abnormally.
>
> Obviously, because this is affecting all 3 of my SQL servers, it most
> likely has something to do with the destination server. From what I
> can tell, somewhere near the end of the backup of the large databases,
> the destination server becomes unavailable to the SQL server. I
> disabled all other services on that server (Antivirus, Tape Backup
> services, etc) and did another test, but with the same results. I
> installed updated NIC drivers and RAID controller drivers as well. I
> can manually copy large files over the network to this location with no
> problems.
> Does anyone have any suggestions? I am baffled. Thank you.
> --
> george sarlas
> gsarlas@.yahoo.com
>

maintenance plan and sub-directory

Hello there,
We have a maintenance plan to backup five user databases, we select "create
a sub-directory" and backup file to "d:\sql\backup", one of database backup
files did go to subdirectory of "d:\sql\backup", system put backup files in
"d:\sql\backup",
Anybody has idea? I appreciate your reply
Thanks very much
LionelHi
It's not clear if these are different database in the same job or different
locations for the same database!!
I would expect the location to be consistent for each database within a
given job, therefore all full backups are either in one directory or each
sub-directory and the transaction log backups are either in one directory or
one sub-directory for each database.
You may also want to check the dates and times one the files.
John
"lionel" wrote:

> Hello there,
> We have a maintenance plan to backup five user databases, we select "creat
e
> a sub-directory" and backup file to "d:\sql\backup", one of database backu
p
> files did go to subdirectory of "d:\sql\backup", system put backup files i
n
> "d:\sql\backup",
> Anybody has idea? I appreciate your reply
> Thanks very much
> Lionel
>|||Does the folder exist for that specific db? I have seen issues where when
the MP can not get access to the specified folder it uses the next level up
instead. Another reason why I hate MP's.
Andrew J. Kelly SQL MVP
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:CFB6734A-EEC3-4707-A47E-F4F399E12746@.microsoft.com...
> Hello there,
> We have a maintenance plan to backup five user databases, we select
> "create
> a sub-directory" and backup file to "d:\sql\backup", one of database
> backup
> files did go to subdirectory of "d:\sql\backup", system put backup files
> in
> "d:\sql\backup",
> Anybody has idea? I appreciate your reply
> Thanks very much
> Lionel
>

maintenance plan and sub-directory

Hello there,
We have a maintenance plan to backup five user databases, we select "create
a sub-directory" and backup file to "d:\sql\backup", one of database backup
files did go to subdirectory of "d:\sql\backup", system put backup files in
"d:\sql\backup",
Anybody has idea? I appreciate your reply
Thanks very much
Lionel
Hi
It's not clear if these are different database in the same job or different
locations for the same database!!
I would expect the location to be consistent for each database within a
given job, therefore all full backups are either in one directory or each
sub-directory and the transaction log backups are either in one directory or
one sub-directory for each database.
You may also want to check the dates and times one the files.
John
"lionel" wrote:

> Hello there,
> We have a maintenance plan to backup five user databases, we select "create
> a sub-directory" and backup file to "d:\sql\backup", one of database backup
> files did go to subdirectory of "d:\sql\backup", system put backup files in
> "d:\sql\backup",
> Anybody has idea? I appreciate your reply
> Thanks very much
> Lionel
>
|||Does the folder exist for that specific db? I have seen issues where when
the MP can not get access to the specified folder it uses the next level up
instead. Another reason why I hate MP's.
Andrew J. Kelly SQL MVP
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:CFB6734A-EEC3-4707-A47E-F4F399E12746@.microsoft.com...
> Hello there,
> We have a maintenance plan to backup five user databases, we select
> "create
> a sub-directory" and backup file to "d:\sql\backup", one of database
> backup
> files did go to subdirectory of "d:\sql\backup", system put backup files
> in
> "d:\sql\backup",
> Anybody has idea? I appreciate your reply
> Thanks very much
> Lionel
>

maintenance plan and sub-directory

Hello there,
We have a maintenance plan to backup five user databases, we select "create
a sub-directory" and backup file to "d:\sql\backup", one of database backup
files did go to subdirectory of "d:\sql\backup", system put backup files in
"d:\sql\backup",
Anybody has idea? I appreciate your reply
Thanks very much
LionelHi
It's not clear if these are different database in the same job or different
locations for the same database!!
I would expect the location to be consistent for each database within a
given job, therefore all full backups are either in one directory or each
sub-directory and the transaction log backups are either in one directory or
one sub-directory for each database.
You may also want to check the dates and times one the files.
John
"lionel" wrote:
> Hello there,
> We have a maintenance plan to backup five user databases, we select "create
> a sub-directory" and backup file to "d:\sql\backup", one of database backup
> files did go to subdirectory of "d:\sql\backup", system put backup files in
> "d:\sql\backup",
> Anybody has idea? I appreciate your reply
> Thanks very much
> Lionel
>|||Does the folder exist for that specific db? I have seen issues where when
the MP can not get access to the specified folder it uses the next level up
instead. Another reason why I hate MP's.
--
Andrew J. Kelly SQL MVP
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:CFB6734A-EEC3-4707-A47E-F4F399E12746@.microsoft.com...
> Hello there,
> We have a maintenance plan to backup five user databases, we select
> "create
> a sub-directory" and backup file to "d:\sql\backup", one of database
> backup
> files did go to subdirectory of "d:\sql\backup", system put backup files
> in
> "d:\sql\backup",
> Anybody has idea? I appreciate your reply
> Thanks very much
> Lionel
>sql

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

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