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
ASL.dll
