MS-SQL / SQL Server

Lock(락) 자동으로 죽이기


-- 로그 테이블 생성


CREATE TABLE [dbo].[LogLockKill](
 [LogDate] [char](10) NOT NULL CONSTRAINT [DF_LogLockKill_LogDate]  DEFAULT (CONVERT([char](10),getdate(),(120))),
 [LogDateTime] [datetime] NOT NULL CONSTRAINT [DF_LogLockKill_LogDateTime]  DEFAULT (getdate()),
 [SpID] [int] NOT NULL,
 [Status] [nvarchar](30) NOT NULL,
 [LoginName] [nvarchar](128) NOT NULL,
 [HostName] [nvarchar](128) NOT NULL,
 [Blocked] [smallint] NOT NULL,
 [DbName] [varchar](30) NOT NULL,
 [Cmd] [nvarchar](16) NOT NULL,
 [WaitType] [binary](2) NOT NULL
) ON [PRIMARY]

GO

-- 인덱스 생성
CREATE NONCLUSTERED INDEX [IX_LogLockKill] ON [dbo].[LogLockKill] 
(
 [LogDate] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


CREATE NONCLUSTERED INDEX [IX_LogLockKill_1] ON [dbo].[LogLockKill] 
(
 [SpID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

 

 

 

-- 잡으로 1분 혹은 5분등 적당한 주기로 아래의 쿼리를 돌린다.

-- 아래의 쿼리 중간 정도 에 보면.. (IF (LEN(@spid) > 0 AND Rtrim(@loginame) = 'accountforweb')) 이라는 부분이 있습니다.

-- 혹시 특정 계정에 한해서 lock(락)이 걸리는걸 죽이고 싶으시다면 accountforweb 부분에 그 계정명을 적으시면 됩니다.

-- 개인적으로는 특정계정에 한해서 하는게 좋을것 같습니다.

-- 대용량 데이터베이스에서 잡에서 집계하는 데이터양이 많다거나 여러가지 이유로 끊겨버리면 안되니까요.

 

DECLARE @spid int, @status nvarchar(30), @loginame nvarchar(128), @hostname nvarchar(128), @blocked smallint, @dbname varchar(30), @cmd nvarchar(16), @waittype binary(2)
DECLARE @StrSql nvarchar(20)
DECLARE @IsOK bit

SET @IsOK = 0

DECLARE DB_Cursor CURSOR FOR 
 SELECT spid, status, loginame, hostname, blocked, db_name(dbid) dbname, cmd, waittype FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses) AND blocked = 0
OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor
INTO @spid , @status , @loginame , @hostname , @blocked , @dbname , @cmd , @waittype
WHILE @@FETCH_STATUS = 0
BEGIN

 
 
 BEGIN TRY
--  IF (LEN(@spid) > 0 AND Rtrim(@loginame) = 'accountforweb')

IF (LEN(@spid) > 0 )
  BEGIN
   
   SET @StrSql = 'KILL ' + CONVERT(VARCHAR, @spid)
   EXEC sp_executesql @StrSql    
   
   IF @@ERROR = 0
    SET @IsOK = 1
  END
 END TRY
 
 BEGIN CATCH
  SET @IsOK = 0

--  SELECT ERROR_NUMBER()
--  SELECT ERROR_MESSAGE()
 END CATCH


 IF (@IsOK = 1)
 BEGIN
  INSERT INTO dbo.LogLockKill (SpID, Status, LoginName, HostName, Blocked, DbName, Cmd, WaitType)
  VALUES (@spid , @status , Rtrim(@loginame) , Rtrim(@hostname) , @blocked , @dbname , @cmd , @waittype)

  SET @IsOK = 0
 END


 FETCH NEXT FROM DB_Cursor
 INTO @spid , @status , @loginame , @hostname , @blocked , @dbname , @cmd , @waittype

END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor

+ Recent posts