· 6 years ago · Oct 21, 2019, 01:00 PM
1USE [msdb]
2GO
3
4/****** Object: Job [Maintenance.Reindex] Script Date: 27.12.2016 14:06:38 ******/
5BEGIN TRANSACTION
6DECLARE @ReturnCode INT
7SELECT @ReturnCode = 0
8/****** Object: JobCategory [Database Maintenance] Script Date: 27.12.2016 14:06:38 ******/
9IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
10BEGIN
11EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
12IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
13
14END
15
16DECLARE @jobId BINARY(16)
17EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Maintenance.Reindex',
18 @enabled=1,
19 @notify_level_eventlog=0,
20 @notify_level_email=2,
21 @notify_level_netsend=0,
22 @notify_level_page=0,
23 @delete_level=0,
24 @description=N'Rebuilds or reorgonizes indexes in all databases based upon index fragmentation in sys.dm_db_index_physical_stats.
25If < 20 - Skip
26If > 20 and <=40 - Reorganize
27If > 40 - Rebuild
28
29логирование в tempdb..reindex_log
30исключена таблица audit_events',
31 @category_name=N'Database Maintenance',
32 @owner_login_name=N'sa',
33 @notify_email_operator_name=N'sql_admins', @job_id = @jobId OUTPUT
34IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
35/****** Object: Step [Reindex] Script Date: 27.12.2016 14:06:38 ******/
36EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Reindex',
37 @step_id=1,
38 @cmdexec_success_code=0,
39 @on_success_action=1,
40 @on_success_step_id=0,
41 @on_fail_action=2,
42 @on_fail_step_id=0,
43 @retry_attempts=0,
44 @retry_interval=0,
45 @os_run_priority=0, @subsystem=N'TSQL',
46 @command=N'SET LOCK_TIMEOUT 5000 -- В случае блокировки ждем 5 секунд, потом пропускаем этот индекс
47declare @db_list_reindex table(db_name nvarchar(128), db_id int)
48declare @db_list_reindex2 table(db_name nvarchar(128), db_id int)
49declare @db_name nvarchar(128)
50declare @db_id int
51declare @sql nvarchar(4000)
52declare @schema_name nvarchar(128)
53declare @table_name nvarchar(128)
54declare @index_name nvarchar(128)
55declare @avg_fragmentation_in_percent float
56declare @before_reindex_table nvarchar(max)
57declare @after_reindex_table nvarchar(max)
58declare @duration nvarchar(max)
59declare @subj varchar(50), @body nvarchar(max)
60declare @start_time datetime = getdate()
61declare @recipients varchar(max)
62declare @size int
63declare @onlinewarn nvarchar(2048)
64declare @logfreespace int
65declare @logfreespacequery nvarchar(4000)
66declare @flag bit = 0
67
68set @onlinewarn = N''<H4>Indexes, that couldn''''t be rebuilt:</H4>''
69
70set @before_reindex_table =
71 N''<H3>Table list before reindex</H3>'' +
72 N''<table border="1">'' +
73 N''<tr align="center" bgcolor="silver">''+
74 N''<th>db_name</th>'' +
75 N''<th>schema_name</th>''+
76 N''<th>table_name</th>'' +
77 N''<th>index_name</th>'' +
78 N''<th>avg_fragmentation</th>''
79
80if exists (select 1 from tempdb.sys.objects where name = N''reindex_log'')
81drop table tempdb..reindex_log
82
83create table tempdb..reindex_log
84(
85collection_time datetime,
86command nvarchar(4000)
87)
88
89insert into @db_list_reindex
90select name, database_id from sys.databases where name not in (''tempdb'', ''model'') --= (N''SBU_GO'') -- Включаем/исключаем БД
91
92insert into @db_list_reindex2
93select * from @db_list_reindex
94
95
96-- Дефрагментируем кучи
97CREATE TABLE #heaps_for_maintenance -- Создаем временную таблицу, куда поместим кучи для дефрагментации
98(
99 [db_name] [sysname] NOT NULL,
100 [schema_name] [sysname] NOT NULL,
101 [table_name] [sysname] NOT NULL,
102 [index_name] [sysname] NULL DEFAULT N'' - HEAP - '',
103 [avg_fragmentation_in_percent] [float] NULL
104)
105
106select top 1 @db_name=db_name,@db_id=db_id from @db_list_reindex
107while(@@rowcount <> 0) -- Собираем статистику по индексам по всем БД
108begin
109 set @sql = N''use ['' + @db_name + N''];
110 insert into #heaps_for_maintenance
111 select QUOTENAME(DB_NAME(ps.database_id)) as db_name,
112 QUOTENAME(sch.name) as schema_name,
113 QUOTENAME(obj.name) as table_name,
114 '''' - HEAP - '''' as index_name,
115 max(ps.avg_fragmentation_in_percent) as avg_fragmentation_in_percent
116 from sys.dm_db_index_physical_stats (''+cast(@db_id as nvarchar(6))+N'', NULL, NULL, NULL, NULL) as ps
117 inner join sys.indexes as ind on ps.object_id = ind.object_id and ps.index_id = ind.index_id
118 inner join sys.objects as obj on ps.object_id = obj.[object_id]
119 inner join sys.schemas as sch on sch.schema_id = obj.schema_id
120 where ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20
121 and DB_NAME(ps.database_id) + ''''.'''' + sch.name + ''''.'''' + obj.name not in (''''MBR.dbo.audit_events'''') -- исключить таблицы
122 and ps.index_type_desc = ''''HEAP'''' -- Only get clustered and nonclustered indexes
123 and obj.type_desc = ''''USER_TABLE'''' -- Restrict to user tables
124 and ps.page_count > 128 --- ignore tables less than 1MB
125 group by ps.database_id, obj.name, sch.name''
126
127 begin try
128 exec (@sql)
129 end try
130 begin catch
131 end catch
132
133 delete from @db_list_reindex where db_id=@db_id
134 select top 1 @db_name=db_name,@db_id=db_id from @db_list_reindex
135end
136
137if exists (select top 1 * from #heaps_for_maintenance)
138set @before_reindex_table = @before_reindex_table + cast((
139 select td = db_name, '''',
140 td = schema_name, '''',
141 td = table_name, '''',
142 td = index_name, '''',
143 td = cast(avg_fragmentation_in_percent as varchar(10)), ''''
144 from #heaps_for_maintenance order by avg_fragmentation_in_percent desc
145 for xml path(''tr''), type) as nvarchar(max));
146else
147 set @flag = 1
148
149select top 1 @db_name=db_name, @schema_name=schema_name, @table_name=table_name FROM #heaps_for_maintenance
150while( @@rowcount <> 0 ) -- Сама дефрагментация
151begin
152
153 set @sql=N''ALTER TABLE '' + @db_name + N''.'' + @schema_name + N''.'' + @table_name + N'' REBUILD WITH (ONLINE = ON);''
154
155 begin try
156 insert into tempdb..reindex_log select getdate(),@sql
157 exec (@sql);
158 end try
159 begin catch
160 set @onlinewarn = @onlinewarn + ''<strong>'' + @db_name + ''.'' + @schema_name + ''.'' + @table_name + ''</strong><br>'' + ERROR_MESSAGE() + ''<br>''
161 insert into tempdb..reindex_log select getdate(),ERROR_MESSAGE()
162 end catch
163
164 delete from #heaps_for_maintenance where db_name=@db_name AND schema_name=@schema_name AND table_name=@table_name
165 select top 1 @db_name=db_name, @schema_name=schema_name, @table_name=table_name FROM #heaps_for_maintenance
166end
167
168
169-- Дефрагментируем индексы
170CREATE TABLE #indexes_for_maintenance -- Создаем временную таблицу, куда поместим индексы для дефрагментации
171(
172 [db_name] [nvarchar](128) NULL,
173 [schema_name] [sysname] NOT NULL,
174 [table_name] [sysname] NOT NULL,
175 [index_name] [sysname] NULL,
176 [avg_fragmentation_in_percent] [float] NULL,
177 [size] [bigint] NULL,
178 [partition_number] [int] NULL
179)
180select top 1 @db_name=db_name,@db_id=db_id from @db_list_reindex2
181while(@@rowcount <> 0) -- Собираем статистику по индексам по всем БД
182begin
183 set @sql = N''
184 use ['' + @db_name + N''];
185 insert into #indexes_for_maintenance
186 select QUOTENAME(DB_NAME(ps.database_id)) as db_name,
187 QUOTENAME(sch.name) as schema_name,
188 QUOTENAME(obj.name) as table_name,
189 QUOTENAME(ind.name) as index_name,
190 max(ps.avg_fragmentation_in_percent) as avg_fragmentation_in_percent,
191 ps.page_count * 8 / 1024 as size,
192 ps.partition_number as partition_number
193 from sys.dm_db_index_physical_stats (''+cast(@db_id as nvarchar(6))+N'', NULL, NULL, NULL, NULL) as ps
194 inner join sys.indexes as ind on ps.object_id = ind.object_id and ps.index_id = ind.index_id
195 inner join sys.objects as obj on ps.object_id = obj.[object_id]
196 inner join sys.schemas as sch on sch.schema_id = obj.schema_id
197 where ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20
198 and DB_NAME(ps.database_id) + ''''.'''' + sch.name + ''''.'''' + obj.name not in (''''MBR.dbo.audit_events'''') -- исключить таблицы
199 and ps.index_type_desc in (''''CLUSTERED INDEX'''',''''NONCLUSTERED INDEX'''')
200 and ind.is_hypothetical = 0 -- Only real indexes;
201 and obj.type_desc = ''''USER_TABLE'''' -- Restrict to user tables
202 and ps.page_count > 128 --- ignore tables less than 1MB
203 group by ind.name, obj.name, sch.name, ps.database_id, ps.page_count, ps.object_id, ps.partition_number''
204
205 begin try
206 exec (@sql)
207 end try
208 begin catch
209 end catch
210
211 delete from @db_list_reindex2 where db_id=@db_id
212 select top 1 @db_name=db_name,@db_id=db_id from @db_list_reindex2
213end
214
215
216if exists (select top 1 * from #indexes_for_maintenance) -- Формируем html-таблицу со статистикой по индексам для отправки по почте
217 set @before_reindex_table = @before_reindex_table +
218 cast((
219 select td = db_name, '''',
220 td = schema_name, '''',
221 td = table_name, '''',
222 td = index_name, '''',
223 td = cast(avg_fragmentation_in_percent as varchar(10)), ''''
224 from #indexes_for_maintenance order by avg_fragmentation_in_percent desc
225 for xml path(''tr''), type) as nvarchar(max)) + N''</table>'';
226else
227 if @flag = 0
228 set @before_reindex_table = @before_reindex_table + N''</table>''
229 else
230 set @before_reindex_table = N''<H3>Table list before reindex</H3>'' + N''There are no indexes and heaps with fragmentation level over 20%''
231
232select top 1 @db_name=db_name,@schema_name=schema_name,@table_name=table_name,@index_name=index_name,@avg_fragmentation_in_percent=avg_fragmentation_in_percent, @size=size FROM #indexes_for_maintenance
233while( @@rowcount <> 0 ) -- Сама дефрагментация
234begin
235 IF @avg_fragmentation_in_percent <= 25 -- если фрагментация меньше 40% - REORGANIZE
236 set @sql=N''ALTER INDEX '' + @index_name + N'' ON '' + @db_name + N''.'' + @schema_name + N''.'' + @table_name + N'' REORGANIZE;''
237 ELSE -- иначе REBUILD
238 set @sql=N''ALTER INDEX '' + @index_name + N'' ON '' + @db_name + N''.'' + @schema_name + N''.'' + @table_name + N'' REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF, ONLINE = ON, MAXDOP = 4);''
239 begin try
240 insert into tempdb..reindex_log select getdate(),@sql
241 exec (@sql);
242 end try
243 begin catch
244 set @onlinewarn = @onlinewarn + ''<strong>'' + @db_name + ''.'' + @schema_name + ''.'' + @table_name + ''.'' + @index_name+ ''</strong><br>'' + ERROR_MESSAGE() + ''<br>'' -- Если индекс не получилось перестроить онлайн, пишем его в письмо
245 insert into tempdb..reindex_log select getdate(),ERROR_MESSAGE()
246 --set @sql = replace (@sql, ''ONLINE = ON,'','''') -- Можно, в случае невозможности перестойки онлайн, перестраивать оффлайн. Чревато жесткими блокировками.
247 --exec (@sql);
248 end catch
249 delete from #indexes_for_maintenance where db_name=@db_name AND schema_name=@schema_name AND table_name=@table_name AND index_name=@index_name AND avg_fragmentation_in_percent=@avg_fragmentation_in_percent
250 select top 1 @db_name=db_name,@schema_name=schema_name,@table_name=table_name,@index_name=index_name,@avg_fragmentation_in_percent=avg_fragmentation_in_percent, @size=size FROM #indexes_for_maintenance
251end
252
253if @onlinewarn = N''<H4>Indexes, that couldn''''t be rebuilt:</H4>''
254set @onlinewarn = N''''
255set @duration = N''<b>Duration:</b> '' + cast(cast(getdate() - @start_time as time(0)) as varchar) -- Длительность работы реиндекса
256set @subj=N''Maintenance Plan: reindex - '' + @@SERVERNAME
257set @body= @duration + @onlinewarn + @before_reindex_table
258
259select @recipients=so.email_address -- Получаем список адресов
260 from msdb.dbo.sysoperators as so
261 left outer join msdb.dbo.syscategories sc on (so.category_id = sc.category_id)
262 where so.name = N''sql_admins''
263
264--select @before_reindex_table
265exec msdb.dbo.sp_send_dbmail @recipients = @recipients, -- Отправляем письмо с отчетом
266 @subject =@subj,
267 @body = @body,
268 @body_format = ''HTML'';
269
270
271drop table #heaps_for_maintenance
272drop table #indexes_for_maintenance',
273 @database_name=N'master',
274 @flags=0
275IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
276EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
277IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
278EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Saturday 2:00',
279 @enabled=1,
280 @freq_type=8,
281 @freq_interval=64,
282 @freq_subday_type=1,
283 @freq_subday_interval=0,
284 @freq_relative_interval=0,
285 @freq_recurrence_factor=1,
286 @active_start_date=20161227,
287 @active_end_date=99991231,
288 @active_start_time=0,
289 @active_end_time=235959
290IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
291EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
292IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
293COMMIT TRANSACTION
294GOTO EndSave
295QuitWithRollback:
296 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
297EndSave:
298
299GO