Cerrar conexiones Sql Server

Cuando estamos trabajando con una base de datos Microsoft Sql Server  de desarrollo en la que realizamos la pruebas, en muchas ocasiones necesitamos restaurar una copia de seguridad que contiene una versión diferente o con distintos datos de la que estamos utilizando actualmente.

Al proceder a realizar la restauración de la base de datos suele ocurrir que nos aparece el error “No se ha podido restaurar la base de datos porque está en uso”, por lo que tenemos que preguntar a nuestros compañeros si están trabajando con esa base de datos y solicitarles que se salgan para poder continuar con la restauración de la copia de seguridad.

Si después de estas acciones seguimos obteniendo el mismo mensaje de error, el siguiente paso que solemos realizar es reiniciar el servidor de la base de datos (reiniciamos el servicio, que no el equipo 😉  ) para asegurarnos que ya no hay nadie ni ningún programa conectado. El problema es que al reiniciar el servidor hemos desconectado a los usuarios de todas las bases de datos además de la base de datos objetivo y podemos haber interrumpido el trabajo de nuestros compañeros.

Para evitar tener que reiniciar el servidor e interferir en el trabajo con las otras bases de datos, yo siempre tengo a mano el siguiente script que permite cerrar todas las conexiones activas a la base de datos que indiquemos. Este script se puede utilizar siempre que necesitemos realizar una operación en la que no deba haber ningún usuario conectado, aunque casi siempre yo lo uso a la hora de restaurar un backup.

Para hacer uso de este script bastará con cambiar el nombre de la base de datos en el parámetro @DBName por el nombre de vuestra base de datos. Después ejecutamos el script y procedemos con la tarea que mostraba el error.

+ Ver script

– Ocultar script

/*
* Author: Juan Alonso Sanz
* Email: info@juanalo.com
* Url: http://www.juanalo.com
* Description: Closes all connections to the selected database
*/
USE master
GO
SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
DECLARE @sql as nvarchar(MAX)
SET @ConnKilled=0
SET @spidstr = ''
SET @DBName = 'BD_TO_DISCONNECT'

SET @sql = 'ALTER DATABASE ' + @DBName
    + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
EXEC sp_executesql @sql

IF db_id(@DBName) < 4
BEGIN
	PRINT 'Connections to system databases cannot be killed'
	RETURN
END

SELECT @spidstr=coalesce(@spidstr,',' ) + 'kill '
 + convert(varchar, spid) + '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
	EXEC(@spidstr)
	SELECT @ConnKilled = COUNT(1)
	FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END

SET @sql = 'ALTER DATABASE ' + @DBName + ' SET MULTI_USER'
EXEC sp_executesql @sql

Podéis hacer la prueba del script y dejar en los comentarios los resultados que habéis obtenido. ¡Cualquier mejora o comentario siempre es bienvenido!

Por último te recuerdo que si te ha gustado este artículo puedes suscribirte para recibir las nuevas actualizaciones

Comentarios
  1. Jos

    Gran Aporte!!
    Me sirvió para crear un job de una restauración automática mensual, pero antes había que cerrar las posibles conexiones de cualquier usuario.
    Funciono de lujo!
    Gracias.

  2. Ericks

    Una palabra: Excelente!

  3. Daniel Vargas

    Excelente query, nunca se termina de aprender, saludos desde Chile colega.

  4. Que pena la pregunta en la primera linea porque no se pone use master, no deberia ser use y el nombre de la base de datos que voy a desconectar?. Gracias

AGREGAR COMENTARIO