Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 06-06-2009
Member
 
Join Date: Apr 2009
Posts: 16
set single_user with rollback immediate

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???
Reply With Quote
  #2  
Old 06-06-2009
Member
 
Join Date: Oct 2005
Posts: 2,389
Re: set single_user with rollback immediate

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.

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
Normally this script is there with all the SQL Server DBAs. But these is posted to you as you are new to this.
Reply With Quote
  #3  
Old 06-06-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
Re: set single_user with rollback immediate

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.
Reply With Quote
  #4  
Old 06-06-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: set single_user with rollback immediate

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:


Code:
GO

ALTER DATABASE <dbname> SET MULTI_USER

GO
HOPEFULLY YOUR PROBLEM IS SOLVED...!
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "set single_user with rollback immediate"
Thread Thread Starter Forum Replies Last Post
Will Rollback Rx works on Windows 7 -Deven- Windows Software 3 05-09-2011 07:57 PM
Managing Rollback segment in database Adriana_andros Software Development 4 08-02-2010 06:48 PM
moviemaker rollback Problem peuda Windows Software 3 09-05-2009 05:16 PM
How to rollback previous IE version ? Asaph Technology & Internet 3 22-04-2009 11:18 AM
Windows Vista Rollback? Fedlix Vista Setup and Install 3 23-02-2007 09:10 AM


All times are GMT +5.5. The time now is 12:09 AM.