SQL Script to Stop a db or take it offline 2

[GrandMauler]

Hello.

I'm looking for an SQL command to either stop a db or take it offline or drop all the current users so that I can detach a db.


Any help?

Thanks in advance

 

[ca8msm]

You could try:

[COLOR=blue]ALTER[/color] [COLOR=blue]DATABASE[/color] [databasename] [COLOR=blue]SET[/color] [COLOR=blue]OFFLINE[/color] [COLOR=blue]WITH[/color] [COLOR=blue]ROLLBACK[/color] [COLOR=blue]IMMEDIATE[/color]

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[ptheriault]

This will kill all the users in a database. I wouldn't take a database off-line just to kill user connections.

DECLARE @strSQL varchar(255)
PRINT 'Killing Users'
PRINT '-----------------'

CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30),
requestid int)

INSERT INTO #tmpUsers EXEC SP_WHO


DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = '[b]<yourdb>[/b]'

DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
        PRINT 'Killing ' + @spid
        SET @strSQL = 'KILL ' + @spid
        EXEC (@strSQL)
        END
        FETCH NEXT FROM LoginCursor INTO  @spid, @dbname2
END

CLOSE LoginCursor
DEALLOCATE LoginCursor

DROP table #tmpUsers


GO

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[GrandMauler]

Both very attractive suggestions which I will explore fully.


Thanks, guys.


Gave both of you a star.


Cheers!

 

[GrandMauler]

Just out of curiosity, Paul, why would you advice against the "Offline" option?

 

[SQLDenis]

Paul, even though you kill all the users they can still connect by the time you kill the last one the someone else might be connecting,you have to take it off line or as I do, put it in single user mode

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[ptheriault]

I don't use the off-line method because it can take a long time to set the database off line. I run the kill command then I set the db in single user. (same as Denis.)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[GrandMauler]

Good points.



Thanks, fellas.