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
>