May 24, 2005 at 8:20 am
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
May 24, 2005 at 9:05 am
search this site for usp_killusers sp, this will do what you need
May 25, 2005 at 6:15 am
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
May 25, 2005 at 8:28 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy