Find out why a maint plan failed

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Find out why a maint plan failed

Find out why a maint plan failed

Rate Topic

Display Mode

Topic Options

Author

Message

n00bDBA

Posted Tuesday, December 11, 2012 2:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:05 PM
Points: 117, Visits: 418

Hi all,

a maintence plan failed last night and im trying to find out why. now apart from the fact that maintenance plans are horrible and this one is rather horrid... is there really no way to find out what went wrong...

The plan is:

DBCC CHECKDB All Databases
FULL BACKUP All Databases
CLEAN UP Delete old files .bak
CLEAN UP delete old files .trn

Job history :

Date 10/12/2012 19:30:04
Log Job History (Daily Backup.Subplan_1)

Step ID 1
Server
Job Name Daily Backup.Subplan_1
Step Name Subplan_1
Duration 00:13:43
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: . ...9.00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 19:30:38 Progress: 2012-12-10 19:30:44.90 Source: {00CD2D5A-118F-4D19-96AB-FE9631F02CD5}
Executing query "DECLARE @Guid UNIQUEIDENTIFIER
EXECUTE msdb..sp".: 100% complete
End Progress
Progress: 2012-12-10 19:30:49.37 Source: Check Database Integrity Task
Executing query "USE [master] ".: 50% complete
End Progress
Progress: 2012-12-10 19:31:38.83 Source: Check Database Integrity Task
Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete
End Progress
Progress: 2012-12-10 19:31:38.94 Source: Check Database Integrity Task
Executing query "USE [model] ".: 50% complete
End Progress
Progress: 2012-12-10 19:32:00.05 Source: Check Database Integrity Task
Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete
End Progress
Error: 2012-12-10 19:32:... The package execution fa... The step failed.

Looking at the SQL Server Logs: (Note it shows running DBCC CHCEKDB on ALL the databases. so should have started on the backups)

3795 2012-12-10 19:43:43.350 spid56 DBCC CHECKDB (db4) WITH no_infomsgs executed by found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
3794 2012-12-10 19:43:41.190 spid56 DBCC CHECKDB (db3) WITH no_infomsgs executed by found 0 errors and repaired 0 errors. Elapsed time: 0 hours 2 minutes 34 seconds.
3793 2012-12-10 19:41:06.680 spid56 DBCC CHECKDB (db2) WITH no_infomsgs executed by found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 23 seconds.
3792 2012-12-10 19:40:42.290 spid60 DBCC CHECKDB (db1) WITH no_infomsgs executed by found 0 errors and repaired 0 errors. Elapsed time: 0 hours 8 minutes 20 seconds.
3791 2012-12-10 19:31:58.200 spid60 DBCC CHECKDB (model) WITH no_infomsgs executed by found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 19 seconds.
3790 2012-12-10 19:31:38.830 spid60 DBCC CHECKDB (mssqlsystemresource) WITH no_infomsgs executed by found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 4 seconds.
3789 2012-12-10 19:31:28.210 spid60 DBCC CHECKDB (master) WITH no_infomsgs executed by found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 38 seconds.
3788 2012-12-10 19:30:45.430 spid58 Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.

I tried looking though the default trace but couldnt see anything there.

Any ideas where else to look?

Post #1394956

Bhuvnesh

Posted Wednesday, December 12, 2012 4:31 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

see if you can find something in this table

sysdbmaintplan_hist

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1395588

T_Peters

Posted Thursday, December 13, 2012 10:55 AM

Right there with Babe

Group: General Forum Members
Last Login: Monday, May 20, 2013 10:58 AM
Points: 727, Visits: 1,086

Try the log files, there is a text file for each maintenance plan written. The file path may vary, mine is at C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log

Tim

Post #1396306

« Prev Topic | Next Topic »

Permissions