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???
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???
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.
Normally this script is there with all the SQL Server DBAs. But these is posted to you as you are new to this.Code: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
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.
Trying doing this first, but be sure that you need to be amin for applying this code :
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:
HOPEFULLY YOUR PROBLEM IS SOLVED...!Code:GO ALTER DATABASE <dbname> SET MULTI_USER GO
Bookmarks