Drop Connections with ROLLBACK IMMEDIATE to allow a Detach database

14 February,2013 by Jack Vamvas

If connections exist on a database , and you attempt to detach a database with EXEC master.dbo.sp_detach_db @dbname = N'database_name' the detach will fail.

Best practise is to identify the connection sources and communicate with the users, and closing the connection gracefully. Communicating with the users is not always possible .

Instead of using the KILL command ,use this command to set the database into SINGLE_USER mode. The SINGLE_USER mode indicates for only one user to access the database. The ROLLBACK IMMEDIATE option terminates ,rolling back all transactions not completed and disconnecting all other connections immediately.

 

 

USE [master]
GO
ALTER DATABASE [database_name] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'database_name'
GO

 

Read More

Find number of connection attempts since SQL Server start - @@connections

SQL Server – Database Connectivity test with Powershell

Cannot connect to SQL Server



Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Drop Connections with ROLLBACK IMMEDIATE to allow a Detach database


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer