set single_user with rollback immediate

[Abalakin]

I working on SQL Server Database & want to set it to single user with rollback so can be restored by me. But the problem with me is that it is hanging up with spid-2. Can any rid me out of this???

[Reegan]

KILL spids :

For disconnecting all the non admin i use the following codes :

ALTER DATABASE Database1 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

To restore the database. i need to kick developers in which Alter database statement just works on non non admins.

DECLARE @spid varchar(10)

SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE dbid IN (DB_ID('Database1'), DB_ID('Database2'))

WHILE @@ROWCOUNT <> 0
BEGIN
 EXEC('KILL ' + @spid)

 SELECT @spid = spid
 FROM master.sys.sysprocesses
 WHERE
  dbid IN (DB_ID('Database1'), DB_ID('Database2')) AND
  spid > @spid
END

Normally this script is there with all the SQL Server DBAs. But these is posted to you as you are new to this.

[Praetor]

To kill the SPID is the process which must be made before the delivery of DBCC CHECKDB the database or d' to carry out the process of restoration. To kill the SPID can be accomplished by adding another stage of your Agent SQL Server or employment in your script \ stored procedure inviting the code below to carry out the process of KILL preliminary to code execution which needs exclusive use of the database.

[kelfro]

Trying doing this first, but be sure that you need to be amin for applying this code :

GO

ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

Also make sure after the restore make it multi user again by following the below code:

GO

ALTER DATABASE <dbname> SET MULTI_USER

GO

HOPEFULLY YOUR PROBLEM IS SOLVED...!