Have you tried to restore the development database when suddenly such an error occurred?
In the MS SQL Server with a connection to ColdFusion, this is a common problem. ColdFusion is not closing connections to speed up future queries, but it also causes such issues when trying to restore the development (or – in fact – any) database.
Checking active connections
To check what are the active connections, I’m using sys.dm_exec_sessions (for SQL Server 2012 and above) or master.sysprocesses (for older databases).
-- OLD SQL SERVER SELECT spid FROM master..sysprocesses where dbid = db_id('my-database-name') -- SQL SERVER 2012 and above SELECT session_id FROM sys.dm_exec_sessions WHERE database_id = db_id('my-database-name')
If there are in fact some connections to close, I can simply kill such connections by providing the session id (spid) in this command:
kill [spid]; -- example: kill 45;
Automate the task
Sometimes there is no one, but many connections. In such a case it is faster to use the query which will build the query string and execute all kill commands at once:
-- OLD SQL SERVER USE master; DECLARE @kill_query varchar(max); SET @kill = ''; SELECT @kill_query = @kill_query + 'kill ' + CONVERT(varchar(10), spid) + '; ' FROM master..sysprocesses WHERE dbid = db_id('my-database-name') EXEC(@kill_query); -- SQL SERVER 2012 and above USE [master]; DECLARE @kill_query varchar(max) = ''; SELECT @kill_query = @kill_query + 'kill ' + CONVERT(varchar(10), session_id) + '; ' FROM sys.dm_exec_sessions WHERE database_id = db_id('my-database-name') EXEC(@kill_query);
Here is the example displaying contents of the ‘kill query’:
In the example above I changed ‘exec’ to ‘select’ in order to display the contents of the query built by the script.