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
'연구개발 > DBA' 카테고리의 다른 글
테이블 정보확인 (0) | 2012.07.18 |
---|---|
대기분석용 프로시저 (0) | 2012.05.30 |
데드락 경고 시스템 감지 방법 DeadLock (0) | 2012.05.30 |
파티션 카탈로그 뷰 (0) | 2012.05.22 |
슬라이딩 윈도우 기법을 이용한 파티션 관리 (0) | 2012.05.22 |