· 10 years ago · Sep 20, 2016, 05:44 PM
1CREATE DATABASE DBADMIN
2GO
3
4
5USE [dbadmin]
6GO
7
8/****** Object: Table [dbo].[BlockingProcesses] Script Date: 09/20/2016 13:32:00 ******/
9SET ANSI_NULLS ON
10GO
11
12SET QUOTED_IDENTIFIER ON
13GO
14
15SET ANSI_PADDING ON
16GO
17
18CREATE TABLE [dbo].[BlockingProcesses](
19 [PK] [int] IDENTITY(1,1) NOT NULL,
20 [last_batch] [datetime] NULL,
21 [spid] [int] NULL,
22 [BlockedTotal] [int] NULL,
23 [LoginName] [varchar](128) NULL,
24 [DBName] [varchar](128) NULL,
25 [HostName] [varchar](128) NULL,
26 [Program_Name] [varchar](255) NULL,
27 [CPU] [bigint] NULL,
28 [Physical_IO] [bigint] NULL,
29 [Memusage] [bigint] NULL,
30 [Open_tran] [int] NULL,
31 [EventInfo] [varchar](max) NULL,
32 [InsertTime] [smalldatetime] NULL,
33 [Params] [varchar](500) NULL
34) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
35
36GO
37
38SET ANSI_PADDING OFF
39GO
40
41ALTER TABLE [dbo].[BlockingProcesses] ADD DEFAULT (getdate()) FOR [InsertTime]
42GO
43
44
45
46USE [master]
47GO
48
49/****** Object: StoredProcedure [dbo].[sp__Maint_BlockWatch] Script Date: 09/20/2016 13:30:45 ******/
50SET ANSI_NULLS ON
51GO
52
53SET QUOTED_IDENTIFIER OFF
54GO
55
56
57create Procedure [dbo].[sp__Maint_BlockWatch]
58as
59declare @blocker smallint
60DECLARE blocker_cursor CURSOR FOR select distinct blocked from sysprocesses where blocked !=0
61OPEN blocker_cursor
62 FETCH NEXT FROM blocker_cursor INTO @blocker
63 WHILE (@@fetch_status <> -1)
64
65 BEGIN
66 IF (@@fetch_status = -2)
67 BEGIN
68 FETCH NEXT FROM blocker_cursor INTO @blocker
69 CONTINUE
70 END
71
72 exec sp__Maint_Blockingprocesses @blocker
73
74 FETCH NEXT FROM blocker_cursor INTO @blocker
75 END
76DEALLOCATE blocker_cursor
77
78
79GO
80
81USE [master]
82GO
83
84/****** Object: StoredProcedure [dbo].[sp__Maint_BlockingProcesses] Script Date: 09/20/2016 13:31:03 ******/
85SET ANSI_NULLS ON
86GO
87
88SET QUOTED_IDENTIFIER ON
89GO
90
91
92
93CREATE procedure [dbo].[sp__Maint_BlockingProcesses]
94@spid smallint
95as
96declare
97@blockedTotal int,
98@loginame varchar(128),
99@dbid int,
100@hostname varchar(128),
101@program_name varchar(128),
102@cpu bigint,
103@physical_io bigint,
104@memusage bigint,
105@open_tran int,
106@dbname varchar(128),
107@EventInfo varchar(255),
108@Last_batch datetime
109create table #temp
110(
111 EventType varchar(50),
112 Parameters int,
113 EventInfo varchar(255)
114)
115
116insert into #temp
117exec ('dbcc inputbuffer('+@spid+')')
118
119select @EventInfo=eventinfo from #temp
120select @blockedTotal= count(*) from sysprocesses where blocked = @spid
121select @loginame=loginame, @dbid=dbid, @hostname=hostname, @program_name=program_name, @cpu=cpu,
122@physical_io = physical_io, @memusage=[memusage], @open_tran=open_tran, @Last_batch=last_batch
123from sysprocesses where spid=@spid
124select @dbname = name from sysdatabases where dbid=@dbid
125insert into dbadmin..blockingprocesses(last_batch,spid, BlockedTotal, LoginName, DBName, HostName, Program_Name, CPU, Physical_IO, Memusage, Open_tran, EventInfo)
126values(@last_batch, @spid, @BlockedTotal, @LogiName, @DBName, @HostName, @Program_Name, @CPU, @Physical_IO, @Memusage, @Open_tran, @EventInfo)
127drop table #temp
128
129
130
131GO
132
133
134
135USE [msdb]
136GO
137
138/****** Object: Job [_Monitor Blocks] Script Date: 09/20/2016 13:29:34 ******/
139BEGIN TRANSACTION
140
141DECLARE @ReturnCode INT
142SELECT @ReturnCode = 0
143/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 09/20/2016 13:29:34 ******/
144IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
145BEGIN
146EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
147IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
148
149END
150
151DECLARE @jobId BINARY(16)
152EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'_Monitor Blocks',
153 @enabled=1,
154 @notify_level_eventlog=2,
155 @notify_level_email=0,
156 @notify_level_netsend=0,
157 @notify_level_page=0,
158 @delete_level=0,
159 @description=N'No description available.',
160 @category_name=N'[Uncategorized (Local)]',
161 @owner_login_name=N'sa', @job_id = @jobId OUTPUT
162IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
163/****** Object: Step [Look For Blocking] Script Date: 09/20/2016 13:29:34 ******/
164EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Look For Blocking',
165 @step_id=1,
166 @cmdexec_success_code=0,
167 @on_success_action=1,
168 @on_success_step_id=0,
169 @on_fail_action=2,
170 @on_fail_step_id=0,
171 @retry_attempts=0,
172 @retry_interval=1,
173 @os_run_priority=0, @subsystem=N'TSQL',
174 @command=N'exec sp__Maint_BlockWatch',
175 @database_name=N'master',
176 @flags=0
177IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
178EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
179IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
180EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run Once a Minute',
181 @enabled=1,
182 @freq_type=4,
183 @freq_interval=1,
184 @freq_subday_type=4,
185 @freq_subday_interval=1,
186 @freq_relative_interval=0,
187 @freq_recurrence_factor=0,
188 @active_start_date=20060918,
189 @active_end_date=99991231,
190 @active_start_time=0,
191 @active_end_time=235959
192 --, @schedule_uid=N'bf518f11-c5ba-438a-8afd-e4e33e8dad1e'
193IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
194EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
195IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
196COMMIT TRANSACTION
197GOTO EndSave
198QuitWithRollback:
199 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
200EndSave:
201
202GO