2

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?

  • 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? –  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 –  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
  • SuperCool - Infrastructure updated to SP3 this weekend, and that appears to have fixed the issue. Thanks! If you that as an answer, I'll check it as the answer –  Oct 20 '09 at 20:02

3 Answers3

2

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.

luna
  • 63
  • 5
  • 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
0

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

What is being executed in step 1?

Cory
  • 131
  • 4
  • 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. –  Oct 12 '09 at 11:57
0

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

Chirag
  • 155
  • 8