Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

First off - I'm a developer, not a DBA or server admin, so I'm feeling kinda outta my elemenet here. Anyways, I'm trying to create a Maintenance Plan on a Sql Server 2005 machine (it's the first one on this box). After failing miserably to get the plan I wanted to work, I've deleted it, and started off w/a very basic one using the wizard. Its supposed to do 1 thing, do a check database integrity task.

I've gone into the reporting options in Management Studio, and told it to generate a text file report, and to log extended information. However, after the job fails, I go and look for the log file, and nothing is there. When I look in the log viewer (in Management Studio) for "SQL Server" and "SQL Agent", I dont' see any entries for around the time I tried to execute the maintanence plan. When I look at the Application Log, I see an entry that states:

Description: SQL Server Scheduled Job 'MaintenancePlan.Subplan_1' (0xCB266AFE28CAB2449D241CB1293879B5) - Status: Failed - Invoked on: 2009-10-09 15:10:43 - Message: The job failed. The Job was invoked by User sa. The last step to run was step 1 (Subplan_1).

When I look in the Job History for the plan, I see the message:

Unable to start execution of step 1(reason: line(1): Syntax error). The step failed

I've set the plan's connection (by clicking the "Manage Connections" button) for a local server connection using Windows Authentication. Can someone please help me determine what I'm missing/where I screwed up?

share|improve this question
    
Have you applied any SQL server service packs? –  SuperCoolMoss Oct 10 '09 at 13:10
    
Is there a way to tell what service packs are installed through Management Studio, or do I need to actually be on the box to tell? –  user22539 Oct 12 '09 at 12:15
    
If you click on the new query button in Management Studio the service pack level is shown at the bottom of the window. –  SuperCoolMoss Oct 12 '09 at 12:35
    
that's cool to know. The server I'm working on says 9.0 RTM (the dev server I was working on is 9.0 SP3). I'm guessing this means there is an issue w/9.0 RTM being able to do maintenance plans, correct? I'll see if I can get infrastructure to apply the service packs –  user22539 Oct 13 '09 at 12:40
    
RTM Maintenance plans were a bit ropey but I'm not sure if that's the cause of the problem you're seeing. It's best practice to upgrade to the latest service pack anyway (provided you're application is compatible). Post back if you still have problems. –  SuperCoolMoss Oct 14 '09 at 10:00
show 1 more comment

3 Answers

I would try to just recreate the maintenance plan if you are still having problems. Otherwise, if you just want it to rebuild your indexes and backup databases, you can script them directly from Management Studio and paste them into a regular SQL Agent job.

share|improve this answer
    
I had the same thing happen to me. My maint jobs were failing for no known reason, recreated them and they work fine now. If I was a better DBA I'd move away from Maint plans...but I'm not. –  RateControl Feb 11 '10 at 21:46
add comment

Unable to start execution of step 1(reason: line(1): Syntax error). The step failed

What is being executed in step 1?

share|improve this answer
    
It doesn't seem to matter. Currently it's just doing a "Check Database Integrity Task", but I've had it doing something as simple as "select * from myTable" and it comes back with the same error. When I run the SQL interactively, it works. –  user22539 Oct 12 '09 at 11:57
add comment

SQL 2005 SP2 goes out of support in Jan 2010, so might be a good idea to either patch the Server to SP3 or if an option upgrade to SQL 2008 SP1.

If none of the above are an option in your case, I have also blogged about a similar issue where the maintenance plans failed due to a pending sp_configure option which wasn't applied correctly, see if the solution works for you, if you haven't yet managed to solve the problem.

Cheers

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.