Question:Killing sessions before nightly CHECKDB

[DW]

Greetings:

I have a SQL 2000 server with several databases on it, and I have a
maintenance plan that includes both a backup and a CHECKDB Integrity
Check.

The backups appear to run correctly each night, but if a user leaves a
connection open to a database all night, the CHECKDB fails.

I'd like to be able to kill all client connections before each backup
window, so that the checkdb succeeds.

Is there a way to do this?

Thanks,

DW

[Ross Presser]

On 18 Feb 2005 09:02:44 -0800, DW wrote:

Greetings:

I have a SQL 2000 server with several databases on it, and I have a
maintenance plan that includes both a backup and a CHECKDB Integrity
Check.

The backups appear to run correctly each night, but if a user leaves a
connection open to a database all night, the CHECKDB fails.

I'd like to be able to kill all client connections before each backup
window, so that the checkdb succeeds.

Is there a way to do this?

Thanks,

DW

I used to use the procedure below. It usually worked, but I was never able
to figure out what was wrong when it didn't.
create procedure mysp_kill_Spids_Using_Db
( @dbname varchar(80) )
AS

declare @spid int
declare @cmd varchar(80)

declare c cursor for
select spid from sysprocesses
inner join sysdatabases on sysprocesses.dbid = sysdatabases.dbid
where name =@dbname

open c
fetch next from c into @spid

while @@fetch_status=0
begin
select @cmd='kill ' + convert(varchar(10),@spid)
exec @cmd
fetch next from c into @spid
END

close c
deallocate c

GO
Then I'd schedule a T-SQL task to run this:

exec master.dbo.mysp_kill_spids_using_db 'FirstDB'
exec master.dbo.mysp_kill_spids_using_db 'SecondDB'

[DW]

Thanks, Ross... I'll see if I can make that work for me.

Cheers,

DW

[Simon Hayes]

"DW" <dw*******@shaw.ca> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

Greetings:

I have a SQL 2000 server with several databases on it, and I have a
maintenance plan that includes both a backup and a CHECKDB Integrity
Check.

The backups appear to run correctly each night, but if a user leaves a
connection open to a database all night, the CHECKDB fails.

I'd like to be able to kill all client connections before each backup
window, so that the checkdb succeeds.

Is there a way to do this?

Thanks,

DW

You can use ALTER DATABASE to close all connections to a database, and limit
access to sysadmin/db_owner only:

alter database MyDB set restricted_user with rollback immediate

Then after the plan completes:

alter database MyDB set multi_user

This assumes that your users don't have any special privileges in the
database, of course. If they do, you might need to set the database offline
then online again before the plan runs, to ensure that all connections are
killed, regardless of their privilege level.

Simon

[Ross Presser]

On Fri, 18 Feb 2005 19:59:09 +0100, Simon Hayes wrote:

This assumes that your users don't have any special privileges in the
database, of course. If they do, you might need to set the database offline
then online again before the plan runs, to ensure that all connections are
killed, regardless of their privilege level.

I've tried sp_dboption 'MyDatabase','offline','true' in the past when users
were in the database, and it refused to do so.

[Erland Sommarskog]

Ross Presser (rp******@imtek.com) writes:

On Fri, 18 Feb 2005 19:59:09 +0100, Simon Hayes wrote:

This assumes that your users don't have any special privileges in the
database, of course. If they do, you might need to set the database
offline then online again before the plan runs, to ensure that all
connections are killed, regardless of their privilege level.

What I always use is:

alter database MyDB set single_user with rollback immediate

(Most users in our development databases have special priviledges. That is,
they are sa.)
I've tried sp_dboption 'MyDatabase','offline','true' in the past when
users were in the database, and it refused to do so.

Yeah, but then you did not have the power of WITH ROLLBACK IMMEDIATE.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[DW]

Okay, this isn't working out quite as I'd expected. I tried the
following:

-Go into the job for "Integrity Checks Job for DB Maintenance Plan '4.
Backup Databases'
-Added a step before and a step after, labeling them 'single mode' and
'multi mode', respectively
-In the commands for these two steps, I added "alter database MyDB set
single_user with rollback immediate" and "alter database MyDB set
multi_user".
-Put the steps in the right order, set the success and failure action,
and saved it.

I went into the Maintenance Plan, and got an error that the job could
not be parsed.

Presumably standard SQL commands will not go in as a job command. Is
this correct? If so, I'd have no idea what the correct syntax would
be...

Any wisdom out there?

Thanks!

DW

[Erland Sommarskog]

DW (dw*******@shaw.ca) writes:

Okay, this isn't working out quite as I'd expected. I tried the
following:

-Go into the job for "Integrity Checks Job for DB Maintenance Plan '4.
Backup Databases'
-Added a step before and a step after, labeling them 'single mode' and
'multi mode', respectively
-In the commands for these two steps, I added "alter database MyDB set
single_user with rollback immediate" and "alter database MyDB set
multi_user".
-Put the steps in the right order, set the success and failure action,
and saved it.

I went into the Maintenance Plan, and got an error that the job could
not be parsed.

Presumably standard SQL commands will not go in as a job command. Is
this correct? If so, I'd have no idea what the correct syntax would
be...

If I understand this correctly, you got the error when you try to look
at it as a maintenance plan. As you added custom steps, the job is no
longer a maintenance plan in that sense. I don't know too much about
the maintenance plans, but I guess it reads the commands for the
various steps and matches them against know activities. So when it
finds you ALTER DATABASE commands, it's get quite confused.

You should be able to run the job anyway.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[DW]

It's very weird. I can put the SQL statement in the command window, and
hit the 'parse' button and get a '0 errors' indicator.

Then I'll go back to the maintenance plan, get a warning that I
shouldn't add steps to jobs that are created by maintenance plans, and
then a parse error. But it seems to run properly, booting all sessions,
and then re-enabling multiuser after the integrity check.

And if I exit and go back into the maintenance plan again, my EM window
bombs with an access voilation. Yeesh.

Maybe I should put these commands in separate jobs, rather than
separate tasks within the same job.

[DW]

It's very weird. I can put the SQL statement in the command window, and
hit the 'parse' button and get a '0 errors' indicator.

Then I'll go back to the maintenance plan, get a warning that I
shouldn't add steps to jobs that are created by maintenance plans, and
then a parse error. But it seems to run properly, booting all sessions,
and then re-enabling multiuser after the integrity check.

And if I exit and go back into the maintenance plan again, my EM window
bombs with an access voilation. Yeesh.

Maybe I should put these commands in separate jobs, rather than
separate tasks within the same job.