SQL Server 2008 Maintenance Plan Fails

243johnm
243johnm used Ask the Experts™
on
I have just created a Maintenance Plan in SQL Server 2008 Workgroup. To test it, I scheduled it to run once at 15:30 ... and it failed!

I found this in ERRORLOG:

----- -----     ----- -----     ----- -----     ----- -----     ----- -----    
2010-01-31 15:30:27.33 spid58      Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.
2010-01-31 15:30:27.33 spid58      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2010-01-31 15:38:15.71 spid60      DBCC CHECKDB (GJQArchive) WITH no_infomsgs executed by NT AUTHORITY\SYSTEM found 0 errors and repaired 0 errors. Elapsed time: 0 hours 7 minutes 48 seconds.  Internal database snapshot has split point LSN = 000154b3:0000017a:0001 and first LSN = 000154b3:00000179:0001.  This is an informational message only. No user action is required.
----- -----     ----- -----     ----- -----     ----- -----     ----- -----    

Not much to go on ... for me at least!


These two messages were in the Event Viewer:

----- -----     ----- -----     ----- -----     ----- -----     ----- -----    

Event Type:      Error
Event Source:      SQLISPackage100
Event Category:      None
Event ID:      12291
Date:            1/31/2010
Time:            3:41:06 PM
User:            NT AUTHORITY\SYSTEM
Computer:      GJQSERVEUR
Description:
Package "GJQ Archive Maintenance Plan" failed.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

----- -----     ----- -----     ----- -----     ----- -----     ----- -----    

Event Type:      Warning
Event Source:      SQLSERVERAGENT
Event Category:      Job Engine
Event ID:      208
Date:            1/31/2010
Time:            3:41:08 PM
User:            N/A
Computer:      GJQSERVEUR
Description:
SQL Server Scheduled Job 'GJQ Archive Maintenance Plan.Subplan_1' (0x66440F45D9697C4F97BBC0280DF29DAF) - Status: Failed - Invoked on: 2010-01-31 15:30:24 - Message: The job failed.  

The Job was invoked by Schedule 14 (GJQ Archive Maintenance Plan.Subplan_1).  The last step to run was step 1 (Subplan_1).

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

----- -----     ----- -----     ----- -----     ----- -----     ----- -----    

I checked out the events asp links. The warning didn't say anything ... and most of the error message link referred to issues when moving from a development environment to a production environment. It isn't applicable here as I did this on the onlyh server, a production server.

Any ideas on a possible cause ... and, even better, a solution?

Thanks very much,

John
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer

Commented:
does the sql server agent service started under network service account?
Top Expert 2008
Commented:
Do you have multiple tasks within your maintenance plan? If so try disabling them all apart from the first one. Then test run the plan. If it runs then enable the second and so on until you identify which one is causing the failure.

Author

Commented:
Thanks Reza & Eric!

Reza: the SQL Server Agent logs on under the LocalSystem account.

Eric: I'll try that!

Regards,

John
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Top Expert 2012
Commented:
>>I found this in ERRORLOG:<<
You need to look at the history for this job.  That will give you the cause.

Author

Commented:
Thanks Eric and AC.

As Eric advised, I disabled the individual components and found the problem. Not sure why it wasn't written to the ERRORLOG file...

It turns out that SQL Server 2008 Workgroup allows you to set something that it cannot do. I had set it to compress the backup files ... but that function is not available in SQL Server 2008 Workgroup!

Thanks again,

John
Top Expert 2012

Commented:
>>I had set it to compress the backup files ... but that function is not available in SQL Server 2008 Workgroup!<<
Very good point.  In fact only the Enterprise Edition supports Backup Compression.

Author

Commented:
That's what I found out when I looked up the error message!

Why Workgroup allows it to be set even though that functionality is not available remains a mystery to me. I know that my son (a UNIX security guru) would have some unfavorable comments about Microsoft ;-)

John


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial