Clearing user connections to database before restoring
-
Before I restore a database i set it Offline. But - sometimes the Set Offline command fails, and it seems to me that it is because there are active user-connections. In the Detach Database window I can clear any connection to the database - and then set it offline.
How do I clear connections from a scheduled job ? Is there a stored procedure I can use - or a script that can do it for me ? (i know I have to execute the script/sp from the Master db)...
All help appreciated,
Michael Solander
-
search this site for usp_killusers sp, this will do what you need
-
try this...the syntax is simply sp_kill dbname
--enhanced 02/04/2005 to also list hostname
CREATE PROCEDURE sp_Kill
@DBNAME VARCHAR(30)
--Stored procedure to Delete SQL Process
AS
BEGIN
SET NOCOUNT ON
DECLARE @SPID INT
DECLARE @STR NVARCHAR(50)
DECLARE @HOSTNAME NVARCHAR(50)
CREATE TABLE #TMPLOG (
SPID INT,
ECID INT,
STATUS VARCHAR(50),
LOGINAME VARCHAR(255),
HOSTNAME VARCHAR(50),
BLK INT,
DBNAME VARCHAR(30),
CMD VARCHAR(100)
 
INSERT INTO #TMPLOG EXEC SP_WHO
IF @@ERROR <> 0 GOTO Error_Handle
DECLARE CURPROCESSID CURSOR FOR SELECT SPID FROM #TMPLOG
WHERE DBNAME LIKE @DBNAME
OPEN CURPROCESSID
FETCH NEXT FROM CURPROCESSID INTO @SPID
SELECT @HOSTNAME=HOSTNAME FROM #TMPLOG WHERE SPID=@SPID
IF @SPID IS NOT NULL
PRINT 'Spid Process Kill List For database: ' + @dbName
ELSE
PRINT 'NO Processes Exist to be killed on database ' + @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STR = 'KILL ' + CONVERT(VARCHAR,@SPID)
EXEC SP_EXECUTESQL @STR
PRINT convert(varchar,@spid) + ' - ' + @HOSTNAME
IF @@ERROR <> 0 GOTO ERROR_HANDLE
FETCH NEXT FROM CURPROCESSID INTO @SPID
END
Error_Handle:
IF @@ERROR <> 0 PRINT 'Error killing process - ' + convert(varchar,@spid) + ' - ' + @HOSTNAME
drop table #tmpLog
SET NOCOUNT OFF
END
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
or try this:
ALTER DATABASE <db_name> SET OFFLINE WITH ROLLBACK IMMEDIATE
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply