DB Maintenance Plan Failing

[DavidJA]

Hi all,

My DB Maintenance plan is failing for one of my databases (the others are OK);

In the NT Event Log I get:

SQL Server Scheduled Job 'Integrity Checks Job for DB Maintenance Plan 'DB Maintenance Plan3'' (0x6E3FD4882148BE4F9B7561823DC7115A) - Status: Failed - Invoked on: 2002-01-14 01:30:00 - Message: The job failed. The Job was invoked by Schedule 10 (Schedule 1). The last step to run was step 1 (Step 1).

In the SQL EM's Database Maintenance Plan History, I get the following:

[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.


I take it my database has some problems, but where do I start to look for fixing them?

 

[tcorrigan]

Try this if you're not doing so already. Back up the system databases (master, msdb and model) in a single step by themselves and then in a subsequent step back up the rest of your databases.

 

[Dilyias]

If this is a new plan,

Perhaps you could try manually setting the DB to single user mode before running the integrity checks, and then setting it back. (sp_dbption 'database', 'single user', 'true')

If these are old plans and all of a sudden they stopped running it looks like there are 2 or more users (for example, sa & sysdba ) connected to the database when the plan wants to run - I don't think you can set the database to single user mode when more than one SQL user is connected. Check out the process info, under management->current activity in enterprise manager after the plan runs, (or the morning after, if possible) , preferably before users start to log in (assuming you have a down time) . Sort by user, if you have more than 1 then look into why those processes aren't quitting on time.. An easy way out is stop and restart the SQL service - that disconnects everyone and makes it easy to set the database to single user mode.

You could also check the maintence plan and take the check out of 'attempt to repair any minor problems...' and see if the msg goes away.

Of course, look at the BOL for more details on the stuff I've mentioned here, (sp_dboption, process info ...) as I'm no expert.. yet.

Hope this is helpful!

-Eric