kill more then 1 hour session in logingshipping and session logs
--- 有过logshipping 维护的dba 应该很清楚.
1 logshipping 主要为了数据查询部门操作 进而避免影响主库.
2 有一定的容灾作用
但是 一旦一直有session 占着超过几小时 就很郁闷了 一旦 log被删除 就要重做.
重做影响数据查询 恶性循环.
故 做了个超过一小时 干掉session 并记录在案（避免扯皮 有问题可以找他们）
----step config
USE [DBCenter]
GO
/****** 对象: Table [dbo].[dm_kill_sp] 脚本日期: 03/22/2010 13:15:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dm_kill_sp](
[spid] [int] NULL,
[dbname] [sysname] NOT NULL,
[text] [varchar](max) NULL,
[cmd] [sysname] NOT NULL,
[loginame] [sysname] NOT NULL,
[status] [tinyint] NULL DEFAULT ((1)),
[numcount] [tinyint] NULL DEFAULT ((0)),
[date] [char](10) NULL DEFAULT (CONVERT([char](10),getdate(),(120)))
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [DBCenter]
GO
/****** 对象: Table [dbo].[dm_kill_sp_bak] 脚本日期: 03/22/2010 13:41:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dm_kill_sp_bak](
[spid] [int] NULL,
[dbname] [sysname] NOT NULL,
[text] [varchar](max) NULL,
[cmd] [sysname] NOT NULL,
[loginame] [sysname] NOT NULL,
[status] [tinyint] NULL,
[numcount] [tinyint] NULL,
[date] [char](10) NULL DEFAULT (CONVERT([char](10),getdate(),(120)))
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
use dbcenter
CREATE procedure usp_dm_killspid
as
declare
@spid int
,@dbname sysname
,@text varchar(max)
,@cmd sysname
,@loginame sysname
,@sql varchar(50)
while exists(select 1 from dm_kill_sp where numcount>=6 and [status]<>2)
begin
select top(1) @spid=spid,@dbname=dbname,@text=[text] from dm_kill_sp where numcount>=5 and [status]<>2
select @sql='kill '+ cast(@spid as varchar(20));
execute(@sql);
update dm_kill_sp set [status]=2 where spid=@spid and dbname=@dbname and [text]=@text
insert into dm_kill_sp_bak select * from dm_kill_sp where [status]=2
delete from dm_kill_sp where [status]=2
end
---job
-- job step1
use dbcenter
;
with xwj
as
(
select spid,dbname,[text],cmd,loginame from dm_kill_sp where cmd<>N'RESTORE LOG' and [status]<>2
intersect
select spid,db_name(a.dbid) as dbname,b.text,cmd,loginame from master..sysprocesses as a
cross apply sys.dm_exec_sql_text(sql_handle) as b
inner join master..sysdatabases as c
on a.dbid=c.dbid and db_name(a.dbid) in (N'bkoffer','bk5173','consignment','Need','complainservice') and cmd<>N'RESTORE LOG' and databaseproperty(name,'isreadonly')=1
)
update a set numcount=numcount+1
from dm_kill_sp as a where exists(select 1 from xwj as b where a.spid=b.spid and a.dbname=b.dbname and a.[text]=b.[text] and a.cmd=b.cmd and a.loginame=b.loginame) and a.[status]<>2
;
insert into dm_kill_sp(spid,dbname,[text],cmd,loginame)
select spid,db_name(a.dbid) as dbname,b.text,cmd,loginame from master..sysprocesses as a
cross apply sys.dm_exec_sql_text(sql_handle) as b
inner join master..sysdatabases as c
on a.dbid=c.dbid and db_name(a.dbid) in (N'bkoffer','bk5173','consignment','Need','complainservice') and cmd<>N'RESTORE LOG' and databaseproperty(name,'isreadonly')=1
except
select spid,dbname,[text],cmd,loginame from dm_kill_sp where cmd<>N'RESTORE LOG' and [status]<>2;
--job step2
use dbcenter
if datepart(hour,getdate()) not in(2,3,4)
begin
execute usp_dm_killspid
end
