트랜잭션 걸려있는 것들 찾기 transaction

1. EXEC SP_LOCK2

CREATE PROC sp_lock2
(
@dbname sysname = NULL,
@spid int = NULL
)
AS
/************************************************************************************
		Copyright © 2001 Narayana Vyas Kondreddi. All rights reserved.
                                          
Purpose:	To display detailed lock information

Written by:	Narayana Vyas Kondreddi
		
Tested on: 	SQL Server 7.0 and SQL Server 2000

Date modified:	August-13-2001 12:00 AM

Examples:

To see all the locks:
EXEC sp_lock2

To see all the locks in a particular database, say 'pubs':
EXEC sp_lock2 pubs

To see all the locks held by a particular spid, say 53:
EXEC sp_lock2 @spid = 53

To see all the locks held by a particular spid (23), in a particular database (pubs):
EXEC sp_lock2 pubs, 23
***********************************************************************************/

BEGIN
SET NOCOUNT ON
CREATE TABLE #lock
(
	spid int,
	dbid int,
	ObjId int,
	IndId int,
	Type char(5),
	Resource char(20),
	Mode char(10),
	Status char(10)
)

INSERT INTO #lock EXEC sp_lock

IF @dbname IS NULL
BEGIN
	IF @spid IS NULL
	BEGIN
		SELECT a.spid AS SPID, 
		(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],
		db_name(dbid) AS [Database Name], ISNULL(object_name(ObjId),'') AS [Object Name],IndId, Type, Resource, Mode, Status
		FROM #lock a
	END
	ELSE
	BEGIN
		SELECT a.spid AS SPID, 
		(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],	
		db_name(dbid) AS [Database Name], ISNULL(object_name(ObjId),'') AS [Object Name],IndId, Type, Resource, Mode, Status
		FROM #lock a
		WHERE spid = @spid
	END
END
ELSE
BEGIN
	IF @spid IS NULL 
	BEGIN
		SELECT a.spid AS SPID,
		(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],		
		ISNULL(object_name(a.ObjId),'') AS [Object Name],a.IndId, 
		ISNULL((SELECT name FROM sysindexes WHERE id = a.objid and indid = a.indid ),'') AS [Index Name],
		a.Type, a.Resource, a.Mode, a.Status
		FROM #lock a
		WHERE dbid = db_id(@dbname)
	END
	ELSE
	BEGIN
		SELECT a.spid AS SPID,
		(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],
		ISNULL(object_name(a.ObjId),'') AS [Object Name],a.IndId, 
		ISNULL((SELECT name FROM sysindexes WHERE id = a.objid and indid = a.indid ),'') AS [Index Name],
		a.Type, a.Resource, a.Mode, a.Status
		FROM #lock a
		WHERE dbid = db_id(@dbname) AND spid = @spid			
	END
END

DROP TABLE #lock

END

2.

DECLARE @cTime INT
DECLARE @checkTime DATETIME
SET @cTime = 1
SET @checkTime = DATEADD(MINUTE, -@cTime, GETDATE())

select spid, kpid, blocked, a.dbid, login_time, last_batch, open_tran, status, hostname, program_name, sql_handle, b.text
from master.dbo.sysprocesses A cross apply SYS.DM_EXEC_SQL_TEXT(a.sql_handle) B
where a.open_tran > 0 and A.last_batch < @checkTime


3.
select st.session_id, datediff(minute, last_request_end_time, getdate()), text, last_read, last_write, getdate(), *
from sys.dm_tran_session_transactions st
    inner join sys.dm_exec_sessions es
        on es.session_id = st.session_id
    inner join sys.dm_exec_connections ec
        on ec.session_id = es.session_id
    cross apply sys.dm_exec_sql_text(ec.most_recent_sql_handle)
where datediff(minute, last_request_end_time, getdate()) > 1