DB MaintenancePlan Fails - must be in single user mode?

[rboyd]

I have a SQL 2000 server with a weekly maintenance plan for all user databases that does optimization, checks data integrity, etc. It is scheduled for every Sunday. But if fails, and the error log says the database must be in 'single user mode'. How do I get these jobs to run? I can't be here to manually put the db's in single user mode every weekend. Please help!

[rmiao]

In fact, the maintenance plan tried to set db in single user mode but failed because someone was still connecting to the db. You may add a step in the job to find out those connections and kill them before running maintenance plan.

[skhanal]

You have to write a script to get the list of users currently using the database and run kill for each connection.

[dim]

Hi, ... or you have an option to clean the "Attempt to repair any minor problems" checkbox in your "Database Maintenance Plan" wizard, on the "Integrity" tab. In this case the Maintenance Job will not try to put your db in a single user mode and will only report if there is any problem with your database without attempting to fix it on the fly (which is better from my point).

Dim

[rboyd]

Thank you all for your help. One of the things that confused me about this was that my SQL7 server does the same jobs every week, and I never get this error. The difference is that the SQL7 server has all odbc connections to the dbs, and users are always logged out by Sunday. But my SQL2k server has application services/daemons running against the databases, and they never disconnect. So I have begun scheduling the stop and start of those application services during the optimization jobs. I've tested this and it works fine. Thanks again for the help, have a great Easter - Rob