[MSSQL] Lock Log남기기

MSSQL SSMS 콘솔의 "MSSQL Server 에이전트" 항목의 "작업"에서 작업항목을 추가하여..

일정을 "매일 1분" 마다 실행을 하여,, 하단의  "저장 쿼리"로 "단계"를 만들어서 실행하면,,

tblTemp1, tblTemp2에 Lock객체의 정보가 시간별로 저장이 됩니다.

■ 테이블 만들기

/****** Object:  Table [dbo].[tblTemp1]    Script Date: 05/30/2016 19:40:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[tblTemp1](
	[SPID] [smallint] NOT NULL,
	[BLOCKED] [smallint] NOT NULL,
	[WAITTIME] [bigint] NOT NULL,
	[WAITTYPE] [binary](2) NOT NULL,
	[LASTWAITTYPE] [nchar](32) NOT NULL,
	[STATUS] [nchar](30) NOT NULL,
	[LOGINAME] [nchar](128) NOT NULL,
	[HOSTNAME] [nchar](128) NOT NULL,
	[LAST_BATCH] [datetime] NOT NULL,
	[DB] [nvarchar](128) NULL,
	[SaveDate] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


------------------------------------------------



/****** Object:  Table [dbo].[tblTemp2]    Script Date: 05/30/2016 19:41:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblTemp2](
	[SPID] [int] NULL,
	[EVENTTYPE] [nvarchar](500) NULL,
	[PARAMETERS] [int] NULL,
	[EVENTINFO] [nvarchar](500) NULL,
	[SaveDate] [datetime] NULL
) ON [PRIMARY]

GO

■ 저장 쿼리

BEGIN TRY
       DROP TABLE #TEMP
END TRY BEGIN CATCH END CATCH
GO
 
BEGIN TRY
       DROP TABLE #TEMP2
END TRY BEGIN CATCH END CATCH
GO
 
SELECT
       IDENTITY(INT, 1,1) AS NUM ,SPID, BLOCKED, WAITTIME, WAITTYPE, LASTWAITTYPE,  STATUS, LOGINAME, HOSTNAME, LAST_BATCH, DB_NAME(DBID) AS [DB] INTO #TEMP
FROM SYS.SYSPROCESSES
WHERE BLOCKED <> 0 OR (SPID IN (SELECT BLOCKED FROM SYS.SYSPROCESSES))
 
CREATE TABLE #TEMP2(NUM INT IDENTITY, SPID INT, EVENTTYPE NVARCHAR(500), PARAMETERS INT, EVENTINFO NVARCHAR(500))
 
DECLARE @MIN INT = 1
DECLARE @MAX INT
DECLARE @QUERY NVARCHAR(500)
DECLARE @SPID INT
SET @MAX = (SELECT MAX(NUM) FROM #TEMP)
 
WHILE @MIN <= @MAX
BEGIN
        SELECT @QUERY =  'DBCC INPUTBUFFER(' + CONVERT(NVARCHAR(10), SPID) + ')', @SPID = SPID FROM #TEMP WHERE NUM = @MIN
      
       INSERT #TEMP2 (EVENTTYPE, PARAMETERS, EVENTINFO)
       EXEC (@QUERY)
      
       UPDATE #TEMP2 SET SPID = @SPID WHERE NUM = @@IDENTITY
      
       SET @MIN = @MIN + 1
END

if exists(SELECT * FROM #TEMP)
begin
	insert into tblTemp1 select spid, blocked, waittime, WAITTYPE, lastwaittype, status, loginame, hostname, last_batch, DB, GETDATE() from #TEMP
	insert into tblTemp2 select SPID, EVENTTYPE, PARAMETERS, EVENTINFO, GETDATE() from #TEMP2
end

--SELECT * FROM #TEMP
--SELECT * FROM #TEMP2
--select * from tblTemp1
--select * from tblTemp2