Re: Drop all the connections to the DataBase
From: Uri Dimant (urid_at_iscar.co.il)
Date: 03/30/04
- Next message: Tom Moreau: "Re: GetDate"
- Previous message: markmccoid: "Indexing for a Dimensional Model"
- In reply to: Ram: "Drop all the connections to the DataBase"
- Next in thread: Van Jones: "Re: Drop all the connections to the DataBase"
- Reply: Van Jones: "Re: Drop all the connections to the DataBase"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 30 Mar 2004 17:26:50 +0200
Ram
This procedure written by Narayana Vyas Kondreddi
CREATE PROC sp_dboption2
(
@dbname sysname = NULL, --Database name
@optname varchar(35) = NULL, --Option name
@optvalue varchar(5) = NULL, --Option value, either 'true' or 'false'
@wait int = NULL --Seconds to wait, before killing the existing
connections
)
AS
BEGIN
/***************************************************************************
********************************
Copyright © 2001 Narayana Vyas Kondreddi. All rights reserved.
Purpose: The system stored procedure sp_dboption fails to set databases in
'read only'/'single user'/'offline'
modes if the database is in use. This procedure works as a wrapper around
sp_dboption and overcomes that
limitation by killing all the active connections. You can configure it to
kill the connections immediately,
or after waiting for a specified interval. This procedure simulates the
new ALTER TABLE syntax of SQL Server
2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options along with
OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
SINGLE_USER, RESTRICTED_USER, MULTI_USER).
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Tested on: SQL Server 7.0, Service Pack 3
Date created: October-29-2001 1:30 AM Indian Standard Time
Date modified: October-29-2001 1:30 AM Indian Standard Time
Email: vyaskn@hotmail.com
Usage: Just run this complete script in the master database to create this
stored procedure. As far as syntax is
concerned, this procedure works very similar to the system stored
procedure sp_dboption. It has an additional
parameter @wait, which can be used, to wait for a specified number of
seconds, before killing the connections.
The settable database option names need to be specified in full. For
example, the option name 'single' is
considered invalid and 'single user' is considered valid.
To bring pubs database into single user mode:
EXEC sp_dboption2 'pubs', 'single user', 'true'
To bring pubs database into single user mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'single user', 'true', 30
To bring pubs database into read/write mode:
EXEC sp_dboption2 'pubs', 'read only', 'false'
To bring pubs database into read/write mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'read only', 'false', 30
****************************************************************************
*******************************/
DECLARE @dbid int, @spid int, @execstr varchar(15), @waittime varchar(15),
@final_chk int
--Only the following options require that, no other connections should
access the database
IF (LOWER(@optname) IN ('offline', 'read only', 'single user')) AND
(LOWER(@optvalue) IN('true', 'false'))
BEGIN
--Determining whether to wait, before killing the existing connections
IF @wait > 0
BEGIN
SET @waittime = (SELECT CONVERT(varchar, DATEADD(s, @wait, GETDATE()),
14))
WAITFOR TIME @waittime --Wait the specified number of seconds
END
SET @dbid = DB_ID(@dbname) --Getting the database_id for the specified
database
--Get the lowest spid
TryAgain:
SET @spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@dbid)
WHILE @spid IS NOT NULL
BEGIN
IF @spid <> @@SPID --To avoid the KILL attempt on own connection
BEGIN
SET @execstr = 'KILL ' + LTRIM(STR(@spid))
EXEC(@execstr) --Killing the connection
END
--Get the spid higher than the last spid
SET @spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@dbid AND spid > @spid)
END
END
SET @final_chk = (SELECT COUNT(spid) FROM master..sysprocesses WHERE dbid =
@dbid)
IF (@final_chk = 0) OR (@final_chk = 1 AND DB_NAME() = @dbname)
BEGIN
EXEC sp_dboption @dbname, @optname, @optvalue --Calling sp_dboption to
complete the job
END
ELSE
BEGIN
GOTO TryAgain --New connections popped up, or killed connections aren't
cleaned up yet, so try killing them again
END
END
"Ram" <anonymous@discussions.microsoft.com> wrote in message
news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@microsoft.com...
> Hi ,
> I am using the following code in a batch file to take the back up of a
database. but some times it fails with the message
>
> "Cannot access the database becuase it is being used by another process."
Is there any way i can force all the connections
> to the database to be dropped using code. Any help will be greatly
appreciated.
>
> isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
>
> copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
>
> copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
>
> isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @dbname =
'JMS', @filename1 = 'C:\JJMSdb\JMS_Data.MDF', @filename2 =
'C:\JJMSdb\JMS_Log.LDF'"
>
> Thanks,
> Ram
>
>
>
- Next message: Tom Moreau: "Re: GetDate"
- Previous message: markmccoid: "Indexing for a Dimensional Model"
- In reply to: Ram: "Drop all the connections to the DataBase"
- Next in thread: Van Jones: "Re: Drop all the connections to the DataBase"
- Reply: Van Jones: "Re: Drop all the connections to the DataBase"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
