Maintenance Plan won't Run - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Maintenance Plan won't Run

Author

Topic

dbthj
Posting Yak Master

143 Posts

Posted - 2007-10-30 : 10:29:13

SQL 2000
I have a maintenance plan that only runs stats and shrinks a database. It hasn't been running. I saw nothing wrong with the setup so I created another one yesterday. It was created by and owned by sa, scheduled to run at 11:59 PM Monday Night. Schedule enabled. 11:59 PM came and went and there is nothing at all in the Plan history or job history. Nothing in sysdbmainplan_history and nothing in sysjobhistory. I'll post plan properties and job properties below.

PLAN PROPERTIES
General Tab:
These Databases: (one database checked)

Optimizations Tab:
Update statistics/sample 20%
Remove unused space from database/shrink when grows beyond 50 MB
Schedule Occurs every 1 week(s) on Monday, at 11:59:00 PM.
Enabled
Start date: 10/29/2007
End date: 11/6/2007

Integrity tab: nothing checked

Complete Backup tab: nothing checked

Transaction Log Backup tab: nothing checked

Reporting tab:
Write report to text file
Delete reports older than 4 wks
Write history to table/limit table to 1000 rows for this plan
==============================================================

JOB PROPERTIES
General Tab:
Enabled
Target local server
Owner: sa

Steps Tab:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID BE50AB70-E39A-4C47-97EF-EFF296848F61 -Rpt "D:\Program Files\Microsoft SQL Server\MSSQL\LOG\Test Monday ShrinkJob0.txt" -DelTxtRpt 4WEEKS -WriteHistory -UpdOptiStats 20 -RmUnusedSpace 50 10 '
On success action: Quit the job reporting success
Retry attempts: 0
On failure action: Quit the job reporting failure
Run as user: self

Schedule Tab:
Recurring: Occurs every 1 week(s) on Monday at 11:59:00 PM.
Start date: 10/29/2007
End date 11/6/2007

Notifications Tab:
Write to Windows application log When the job succeeds.
=============================================
This thing doesn't fail. It just plain doesn't run.

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 11:33:55

Has the report file:

D:\Program Files\Microsoft SQL Server\MSSQL\LOG\Test Monday ShrinkJob0.txt

got anything in it?

Its generally a bad idea to shrink a database. For more details see: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Database%20Shrinking,Shrinking,Shrink

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-30 : 23:34:03

Is sql agent service running?

dbthj
Posting Yak Master

143 Posts

Posted - 2007-11-01 : 14:19:33

Duh!!.....
The Agent is set to start automatically.
Didn't do it - or somebody stopped it.
And I didn't think to check.

Anyhow, problem solved. Thanks.

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 15:08:55

There's a "Restart SQL Agent if stops unexpectedly" option

I'm not sure its bullet-proof, but worth ticking that box!

We have a NET START xxx for all critical services that runs every 10 minutes - so if anything stops and doesn't automatically restart the scheduled BATch file "tickles" it.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources