October 31, 2012 at 1:05 pm
Hello,
My database is in singlemode and I can't change to multi user mode,get error message that someone in this database,how can I kill thiss process and find out who is in it?
Thank you
October 31, 2012 at 1:15 pm
I have this procedure to help me kill users in a specific database:
usage is this easy:
sp_kill DatabaseName
and the code:
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)
DECLARE @DBID INT
CREATE TABLE #TMPLOG (
SPID INT,
ECID INT,
STATUS VARCHAR(50),
LOGINAME VARCHAR(255),
HOSTNAME VARCHAR(50),
BLK INT,
DBNAME VARCHAR(30),
CMD VARCHAR(100) ,
RID INT,
)
select @DBID=db_id(@DBNAME)
IF @DBID IS NULL
PRINT 'No database exists with the name ' + @DBNAME + ', Check the Spelling of the db.'
INSERT INTO #TMPLOG EXEC SP_WHO
--do not even try to kill spids that are SQL server itself.
DELETE FROM #TMPLOG WHERE SPID < 50
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
IF @SPID = @@SPID
BEGIN
PRINT 'Cannot kill your own SPID, skipping ' + convert(varchar,@spid) + ' - ' + @HOSTNAME
END
ELSE
BEGIN
SET @STR = 'KILL ' + CONVERT(VARCHAR,@SPID)
EXEC SP_EXECUTESQL @STR
PRINT convert(varchar,@spid) + ' - ' + @HOSTNAME
END
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
October 31, 2012 at 1:52 pm
Just run:
[font="Courier New"]select * from sysprocesses where dbid = db_id('<database_name>')[/font]
Then kill the spid.
October 31, 2012 at 2:56 pm
ALTER DATABASE [YOUR DB NAME]
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
this works most of the times...
October 31, 2012 at 6:23 pm
Now really want know all the possible options that could be done when the DB is in Single user mode and you do not have the control on DB.
1)
ALTER DATABASE [YOUR DB NAME]
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
2) kill the active Spid that is active from sysprocesses.
3) ?????
4) ?????
5) ?????
will drop Database work?
November 1, 2012 at 12:54 am
The "user" probably isn't a "user"... it's probably a WebService which is notorious for trying to maintain a conection. Even if you find out which WebService has the single connection and stop that WebService, I'm sure there's another desparately seeking your single connection and it can get it much quicker than you.
You're probably going to have to stop all the WebServices that connect to the box to be able to get in long enough to set the multi user mode.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply