Posted by Jim Andersen on 06/14/05 13:45

Hi there,

Used the wizard to create a maintenance plan for a db. But it fails most of
the time. I checked the log-files.

At 1.00 AM, it runs the "Optimizations" job for 6-9 seconds. It succeeds
always.

At 1.05 AM, for 1 second, it runs "integrity check", but most of the time it
fails, and says that it couldn't switch to single-user because other users
are using the database.

And when "integrity check" fails the "backup" job won't run at 1.10 AM. When
it DOES run it takes 2 seconds.

How do I find out who is using the database ? How do I put a sp_who (or
similar) in the "integrity check" job ?
I got this tip:
----------
Assuming you have SQL2000, then you can use something like this to
kick out all the users:

ALTER DATABASE foo SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

If you want to know who is using the database, then this is one way:

select suser_sname(sid)
from master..sysprocesses
where dbid = db_id('foo')
---------

But how do I actually do it ? How do I edit a maintenance plan ?

thx
/jim

[Back to original message]