Si una operación permanece abierta en la base de datos, ya sea intencionalmente o no, esta operación puede bloquear otros procesos.
Para ver todas las transacciones que tienes activas en tus bases de datos puedes ejecutar:
DBCC OPENTRAN('Tu_BaseDatoa')
Podras ver el SPID (server process ID), de quien lo esta ejecutando.
Una vez que tienes esta información, si tienes transacciones activas, puedrás obtener el Transact-SQL que se está ejecutando con las Vistas DynamicManagement, tambien averiguar el tiempo que lleva corriendo..., y si es necesario, detener el proceso.
Como puedes obtener información sobre una transacción activa?, ver que es lo que esta haiendo exactamente?. Bien, pues una vez obtenido el ID del proceso con DBCC OPENTRAN, supongamos que el SPID es 38, ejecuta la consulta:
SELECT s.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) s
WHERE session_id = 38
Podras ver, como estan lanzando las consultas, si usan transacciones con begin tran...Commit o si por el contrariono no lo usan.
Nos puede ser de utilidad obtener el transaction ID, asignado a nuesto SPID, por ejemplo con la consulta:
SELECT session_id, transaction_id, is_user_transaction, is_local
FROM sys.dm_tran_session_transactions
WHERE session_id = 38
Una vez obtenido el transaction_id, gracias a la DMV sys.dm_tran_active_transactions podemos conocer el estado de la transacción y obtener mas información, con la consulta: (imaginemos que nuestro transaction_id = 195766)
SELECT transaction_begin_time,
CASE transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction'
END tran_type,
CASE transaction_state
WHEN 0 THEN 'not been completely initialized yet'
WHEN 1 THEN 'initialized but has not started'
WHEN 2 THEN 'active'
WHEN 3 THEN 'ended (read-only transaction)'
WHEN 4 THEN 'commit initiated for distributed transaction'
WHEN 5 THEN 'transaction prepared and waiting resolution'
WHEN 6 THEN 'committed'
WHEN 7 THEN 'being rolled back'
WHEN 8 THEN 'been rolled back'
END tran_state
FROM sys.dm_tran_active_transactions
WHERE transaction_id = 195766
Si locres necsario, podras finalizarla transacción (un rollback), con el comando kill, pero eso es una operación administrativa extrema, a realizar cuando no hay más remedio, ya que KILL se utiliza normalmente para terminar un proceso que está impidiendo la ejecución de otros procesos mediante bloqueos, o un proceso que está ejecutando una consulta que utiliza recursos necesarios del sistema. Para nuestro ejemplo:
KILL 38
"Kill" termina un poceso de usuario (SPID), termina internamente las transacciones asociadas al identificador de sesión especificado. Se puede dar el caso en que la instrucción KILL tarde en completarse, si tiene que deshacer muchas operaciones, especialmente cuando implique revertir una transacción larga.
Este procedimiento, la tabla sysprocesses y otros mecanismos existentes (como el uso de profiler) te permiten determinar quién bloquea, con el fin mejorar las aplicaciones para que no dejen transacciones sin confirmar.
Otra opción es Instalar el procedimiento sp_whoisactive de Adam Machanic (http://sqlblog.com/blogs/adam_machanic/archive/2011/04/27/who-is-active-v11-00-a-month-of-activity-monitoring-part-27-of-30.aspx) para ver con el mayor detalle posible lo que se está ejecutando en el servidor.
Fuentes:
Microsoft, MSDN...
Apunte y recopilación por Norman M. Pardell
Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.