Who is in a database ?

[Jim Andersen]

Hi,

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. Thats a blatant lie though :-)

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

How do I find out who (sqlserver thinks) is using the database ? Is there a
logfile somewhere ? Or can I put a sp_who in the "integrity check" job ? Is
there a way to "kick out" the offending user ? Or maybe put a -kill_all on
the job ?

thx
/jima

[Simon Hayes]

"Jim Andersen" <jimVÆÆ[e-mail]> wrote in message news:<40**********************@news.sunsite.dk>...

Hi,

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. Thats a blatant lie though :-)

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

How do I find out who (sqlserver thinks) is using the database ? Is there a
logfile somewhere ? Or can I put a sp_who in the "integrity check" job ? Is
there a way to "kick out" the offending user ? Or maybe put a -kill_all on
the job ?

thx
/jima

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')

One possible issue with scheduled jobs is that you can block yourself
by setting the database context to 'foo' in the scheduled job
definition, so the job itself is using the database. You might want to
check this, and possibly change it to 'master'.

Simon

[Jim Andersen]

Simon Hayes wrote:

One possible issue with scheduled jobs is that you can block yourself
by setting the database context to 'foo' in the scheduled job
definition, so the job itself is using the database.

But then, wouldn't it be blocked every time ? My job runs sometimes....

/jim