Bueno les dejo este post que me pareció muy interesante y de mucha ayuda a si que lo replique en mi blog, les dejo la URL fuente para no llevarme los créditos.
Cuando trabajamos con SQL server, muchas veces nos damos cuenta que las operaciones que se van a realizar requieren acceso exclusivo. En lo personal, me ocurre generalmente cuando trato de restaurar una base de datos sobre una base de datos que actualmente está en uso, pero puede ocurrir con otro tipo de tareas.
La verdad es frustrante encontrar el aviso que indica que la base de
datos está en uso:
Bueno, pues para este problema existen varias soluciones; en este post
vamos a revisar 2 de ellas.
Para cualquiera de las 2 soluciones que planteo, hay que tener en cuenta
varios elementos:
1.
El proceso de cierre de conexiones puede
tardar un poco dependiendo de las transacciones que se encuentren activas en el
momento del cierre de las conexiones.
2.
Al cerrar las conexiones activas,
existe una gran posibilidad de que el trabajo de otros usuarios se pierda, lo
anterior debido a que el motor de base de datos debe reversar las transacciones
que estén en curso en el momento del cierre de las conexiones.
Teniendo en cuenta lo anterior, ya podemos revisar los 2 métodos que
propongo:
1.
Por un lado, se puede pensar en poner
temporalmente la base de datos en modo “Single User”; mediante esta acción (por
defecto las bases de datos en SQL Server se crean en modo “MultiUser”) el motor
de base de datos cierra todas las conexiones existentes a la base de datos.
2.
El otro método, consiste en “Matar” los
procesos de usuario que tienen conexiones abiertas en la base de datos.
Para el caso del método A, lo podemos llevar a cabo desde el Microsoft®
SQL Server Management Studio (MSSMS) cambiando las opciones de la base de
datos:
1.
Clic derecho sobre la base de datos
donde deseamos cerrar las conexiones
2.
En la página de “Opciones” de la
ventana de propiedades, buscamos la entrada “Restrict Access”; por defecto
estará en “MULTI_USER”
3.
Seteamos la opción “Restrict Access” a
“SINGLE_USER” y damos “Ok”
4.
Una vez damos clic en “Ok”, el motor de
base de datos nos va a indicar que para aplicar el cambio sobre la base de
datos deberá cerrar todas las demás (excepto la nuestra) conexiones a dicha
base de datos, genial, esto es lo que queremos que haga. Damos clic en “Yes”
(para los que lo tengan en español, esto equivale a dar clic en “Si” jejeje. )
5.
Una vez aceptamos el motor cerrará
todas las conexiones y colocará un icono especial sobre la base de datos en la
que acabamos de aplicar los cambios
Este icono nos indica que la base de datos esta en modo “Single User”.
Ahora ya podemos realizar las tareas que deseemos en la base de datos;
es importante tener en cuenta que mientras no se reverse la acción que acabamos
de realizar ningún usuario (salvo tú) va a poder tener acceso a la base de
datos. Para volver a dejar la base de datos en el estado original, basta con
realizar nuevamente los pasos descritos atrás y poner en “Restrict Access” en
modo “MULTI_USER”.
Para quienes prefieran el código en vez de la interfaz gráfica, todos
los pasos anteriores tienen su equivalente en código T-SQL;
en lo personal, siempre prefiero el código ya que me obliga a estar aprendiendo
nuevas sentencias día a día. He aquí el código equivalente para pasar de “MULTI
USER” A “SINGLE USER” y viceversa:
Para pasar de “MULTI USER” a “SINGLE USER”:
USE [master]
GOALTERDATABASE [{NOMBRE_DE_TU_BASE_DE_DATOS}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Para pasar de “SINGLE USER” a “MULTI USER”:
USE [master]
GO
ALTERDATABASE [{NOMBRE_DE_TU_BASE_DE_DATOS}] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
Para el caso del método B (la opción de “matar” los procesos), consiste
en unas sencillas sentencias T-SQL y hacer uso de la instrucción “Kill”, que
como su nombre lo indica (y como lo indica también la documentación de MSDN en:
(http://msdn.microsoft.com/es-es/library/ms173730.aspx)
se encarga de “Matar” o terminar los procesos y/o las conexiones “normales”.
Dado que la instrucción “Kill” debe recibir como parámetro los ID’s de
las sesiones de cuyos procesos se van a cerrar, podemos hacer una consulta a la
tabla “SysProcesses” de la base de datos “Master” para tener la lista de
procesos activos asociados a la base de datos que necesitamos; para filtrar los
resultados de la consulta a fin de matar solamente los procesos de nuestra base
de datos, vamos a utilizar el campo “DBId” (el id de la base de datos). Para
extraer el nombre como tal de la base de datos, podemos hacer uso de la función
DB_NAME() que recibe como parámetro el Id de la base de datos. Como para matar
los procesos solo necesitamos el Id del proceso, este se encuentra en el campo
spid de la tabla “SysProcesses”. Al final, la lista de procesos a “matar” la
obtendremos del siguiente query:
select spid from master..SysProcesses WHERE DB_NAME(DBId)=‘{NOMBRE_DE_TU_BASE_DE_DATOS}’
Ahora, para evitarnos la tarea de matar uno por uno los procesos,
podemos crear una instrucción que se encargue de matar los procesos del query
por nosotros, lo anterior, lo logramos creando una instrucción “kill” por cada
registro resultante y luego concatenar todos los “kill” en una cadena de texto
que al final ejecutaremos con el comando “exec” así:
USE[Master]
GODECLARE @KillCommandSQL VARCHAR(max)
SELECT @KillCommandSQL = COALESCE(@KillCommandSQL,”) + ‘ KILL ‘+convert(VARCHAR(10),spid)+' ‘
FROM
master..SysProcesses
WHERE DB_NAME(DBId)=‘{NOMBRE_DE_TU_BASE_DE_DATOS}’
PRINT @KillCommandSQL
–EXEC (@KillCommandSQL)
Dónde @KillCommandSQL es la variable donde almacenaremos los comandos
“Kill”; la función “COALESCE” la utilicé para evitar errores por valores nulos
(http://technet.microsoft.com/es-es/library/ms190349.aspx)
. Nótese que incluí un “Print”, esto, para que antes de ejecutar el comando,
veamos como queda finalmente, la revisemos y evitemos errores. Ten en cuenta
que el comando “EXEC” esta como comentario, precisamente para evitar que accidentalmente
lo ejecutemos, es decir que el query no funcionará hasta tanto no le quitemos
la marca de comentario.
Bueno amigos, esto es todo, espero esta entrada pueda ser de ayuda en
algún momento y que los pueda sacar de apuros en alguna situación que así lo
requiera.
Si alguno de ustedes encuentra un error en algo que haya mencionado en
esta entrada, o si alguno tiene una idea alternativa para forzar el cierre de
conexiones en SQL Server o si quiere comentar como se haría en otras
plataformas, por favor no dude en colocar un comentario al final de esta
entrada.
Como siempre, un gran abrazo para tod@s y… “HAPPY CODING”
Saludos desde Melmac ...!!! 