· 7 years ago · Nov 19, 2018, 11:58 AM
1USE [_Imagine_Maintenance]
2GO
3/****** Object: StoredProcedure [dbo].[DatabaseMaintenance_RunWeeklyMaintenancePlan] Script Date: 09/11/2012 10:56:08 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9
10ALTER PROCEDURE [dbo].[DatabaseMaintenance_RunWeeklyMaintenancePlan]
11@Input_DatabaseName as sysname = '*',
12@Input_DatabaseNameRangeStart as varchar(1) = '',
13@Input_DatabaseNameRangeEnd as varchar(1) = '',
14@Input_RunDailySteps as tinyint = 0
15AS
16
17/*
18 @Input_DatabaseName -- Allows to target a specific database
19 @Input_DatabaseNameRangeStart -- Allows to enter a start letter for the database name
20 @Input_DatabaseNameRangeEnd -- Allows to enter a end letter for the database name
21
22 -- using A as the start range will also include _Imagine_Maintenance
23
24 EX:
25 EXEC [DatabaseMaintenance_RunWeeklyMaintenancePlan] '*','A','K'
26 EXEC [DatabaseMaintenance_RunWeeklyMaintenancePlan] '*','M','Z'
27
28
29 -- 04/16/2012 - CWK - Added handling if the DB is removed during the duration from a backup procedure etc. Also fixed the
30 -- table load so the results are alpha.
31 -- 06/01/2012 - CWK - Added handling to ignore _Mount databases that are created during MMPs client backups
32 -- 06/22/2012 - CWK\ Sk - T#49120 - Pass in the DB ID to the fragmentation table function.
33*/
34
35SET NOCOUNT ON
36
37DECLARE @Loop_MainLogID as numeric
38DECLARE @Loop_MaintenanceStart as datetime
39
40DECLARE @Loop_DatabaseCount as numeric
41DECLARE @Loop_DatabaseRow as numeric
42DECLARE @Loop_Database_ID as numeric
43DECLARE @Loop_Database_Name as sysname
44DECLARE @Loop_DatabaseStart as datetime
45DECLARE @Loop_DatabaseResult as varchar(MAX)
46
47DECLARE @Loop_StepCount as numeric
48DECLARE @Loop_StepRow as numeric
49DECLARE @Loop_Step_ID as numeric
50DECLARE @Loop_Step_Name as varchar(50)
51DECLARE @Loop_StepStart as datetime
52DECLARE @Loop_StepResult as varchar(MAX)
53
54DECLARE @Loop_TableCount as numeric
55DECLARE @Loop_TableRow as numeric
56DECLARE @Loop_Table_ID as numeric
57DECLARE @Loop_Table_Name as sysname
58DECLARE @Loop_TableStart as datetime
59DECLARE @Loop_Table_DefragLevel as decimal
60DECLARE @Loop_TempSQL as varchar(MAX)
61DECLARE @Loop_TableResult as varchar(MAX)
62
63IF EXISTS (SELECT top 1 1 FROM tempdb.dbo.sysobjects WHERE id=OBJECT_ID('tempdb.dbo.#Temp_Steps'))
64 DROP TABLE #Temp_Steps
65
66IF EXISTS (SELECT top 1 1 FROM tempdb.dbo.sysobjects WHERE id=OBJECT_ID('tempdb.dbo.#Temp_Databases'))
67 DROP TABLE #Temp_Databases
68
69IF EXISTS (SELECT top 1 1 FROM tempdb.dbo.sysobjects WHERE id=OBJECT_ID('tempdb.dbo.#Temp_Tables'))
70 DROP TABLE #Temp_Tables
71
72IF EXISTS (SELECT top 1 1 FROM tempdb.dbo.sysobjects WHERE id=OBJECT_ID('tempdb.dbo.#Temp_ReindexCheckResult'))
73 DROP TABLE #Temp_ReindexCheckResult
74
75CREATE TABLE #Temp_Steps (
76 RowID INT IDENTITY(1, 1) primary key clustered,
77 StepID numeric,
78 StepName varchar(50))
79
80CREATE TABLE #Temp_Databases (
81 RowID INT IDENTITY(1, 1) primary key clustered,
82 DatabaseID numeric,
83 DatabaseName sysname)
84
85CREATE TABLE #Temp_Tables (
86 RowID INT IDENTITY(1, 1) primary key clustered,
87 TableID numeric,
88 TableName sysname)
89
90CREATE TABLE #Temp_ReindexCheckResult (
91 RowID INT IDENTITY(1, 1) primary key clustered,
92 MaximumFragmentation decimal)
93
94----------------------------------------------
95-- Insert Main Log Entry
96----------------------------------------------
97INSERT INTO dbo.DBMaintenanceLog
98(DatabaseName, DatabaseNameRangeStart, DatabaseNameRangeEnd, RunDailySteps, StartDateStamp, EndDateStamp)
99SELECT @Input_DatabaseName,@Input_DatabaseNameRangeStart,@Input_DatabaseNameRangeEnd,@Input_RunDailySteps, GETDATE(),NULL
100
101SET @Loop_MainLogID = SCOPE_IDENTITY()
102
103----------------------------------------------
104-- Clean up old records
105----------------------------------------------
106SET @Loop_MaintenanceStart = GETDATE()
107
108IF EXISTS(SELECT * FROM dbo.DBMaintenanceLog WHERE DateDiff(month,StartDateStamp,GETDATE()) > 6)
109BEGIN
110
111 DELETE FROM dbo.DBMaintenanceLog WHERE DateDiff(month,StartDateStamp,GETDATE()) > 6
112
113 DELETE FROM dbo.DBMaintenanceStepLog WHERE parentlogID NOT IN
114 (SELECT ID FROM dbo.DBMaintenanceLog)
115
116 INSERT INTO dbo.DBMaintenanceStepLog
117 (ParentLogID,DatabaseID, TableID, StepID, StartDateStamp, EndDateStamp, Result)
118 SELECT @Loop_MainLogID, 0,0,0,@Loop_MaintenanceStart,GETDATE(), 'Log Cleanup'
119
120END
121
122----------------------------------------------
123-- Load the user databases
124----------------------------------------------
125IF @Input_DatabaseName IN ('','*')
126BEGIN
127
128 IF @Input_DatabaseNameRangeStart <> '' AND
129 @Input_DatabaseNameRangeEnd <> ''
130 BEGIN
131
132 INSERT INTO #Temp_Databases
133 SELECT Database_ID,name FROM sys.databases
134 WHERE
135 name NOT IN ('master', 'tempdb', 'model', 'msdb') AND
136 name NOT LIKE '%_mount%' AND
137 (
138 (
139 LEFT(name,1) >= @Input_DatabaseNameRangeStart AND
140 LEFT(name,1) <= @Input_DatabaseNameRangeEnd
141 ) OR
142 (
143 -- avoid missing imagine_maintenance
144 1 = CASE WHEN @Input_DatabaseNameRangeStart = 'A' AND LEFT(name,1) = '_' THEN 1 ELSE 0 END
145 )
146 )
147 ORDER BY
148 name
149
150 SET @Loop_DatabaseCount = @@ROWCOUNT
151
152 END
153 ELSE
154 BEGIN
155
156 INSERT INTO #Temp_Databases
157 SELECT Database_ID,name FROM sys.databases
158 WHERE
159 name NOT IN ('master', 'tempdb', 'model', 'msdb') AND
160 name NOT LIKE '%_mount%'
161 ORDER BY
162 name
163
164 SET @Loop_DatabaseCount = @@ROWCOUNT
165
166 END
167
168END
169ELSE
170BEGIN
171
172 INSERT INTO #Temp_Databases
173 SELECT Database_ID,name FROM sys.databases
174 WHERE name = @Input_DatabaseName
175
176 SET @Loop_DatabaseCount = @@ROWCOUNT
177
178END
179
180SET @Loop_DatabaseRow = 1
181
182----------------------------------------------
183-- Load the enabled steps
184----------------------------------------------
185IF @Input_RunDailySteps = 0
186BEGIN
187
188 INSERT INTO #Temp_Steps
189 SELECT ID,name FROM dbo.DBMaintenanceStep WHERE isWeekly = 1 ORDER BY Ordinal, ID
190
191 SET @Loop_StepCount = @@ROWCOUNT
192
193END
194ELSE
195BEGIN
196
197 INSERT INTO #Temp_Steps
198 SELECT ID,name FROM dbo.DBMaintenanceStep WHERE isDaily = 1 ORDER BY Ordinal, ID
199
200 SET @Loop_StepCount = @@ROWCOUNT
201
202END
203
204SET @Loop_StepRow = 1
205
206----------------------------------------------
207-- loop through the databases
208----------------------------------------------
209WHILE @Loop_DatabaseRow <= @Loop_DatabaseCount
210BEGIN
211 -- Clear
212 SELECT
213 @Loop_Database_ID = 0,
214 @Loop_Database_Name = '',
215 @Loop_DatabaseResult = 'Database Completed',
216 @Loop_DatabaseStart = GETDATE()
217
218 -- Grab the next database
219 SELECT
220 @Loop_Database_ID = DatabaseID,
221 @Loop_Database_Name = REPLACE(REPLACE(DatabaseName,']',''),'[','')
222 FROM
223 #Temp_Databases
224 WHERE
225 RowID = @Loop_DatabaseRow
226
227 SET @Loop_StepRow = 1
228
229 ----------------------------------------------
230 -- Check that the database still exists, we had an issue with a temporary backup database
231 ----------------------------------------------
232 IF NOT EXISTS(SELECT * FROM sys.databases WHERE REPLACE(REPLACE(name,']',''),'[','') = @Loop_Database_Name)
233 BEGIN
234 SET @Loop_DatabaseResult = 'Database no longer exists'
235 END
236 ELSE
237 BEGIN
238 ----------------------------------------------
239 -- Loop through the steps
240 ----------------------------------------------
241 WHILE @Loop_StepRow <= @Loop_StepCount
242 BEGIN
243
244 -- Clear
245 SELECT
246 @Loop_Step_ID = 0,
247 @Loop_Step_Name = '',
248 @Loop_StepResult = 'Step Completed',
249 @Loop_StepStart = GETDATE()
250
251 -- Grab the next step
252 SELECT
253 @Loop_Step_ID = StepID,
254 @Loop_Step_Name = StepName
255 FROM
256 #Temp_Steps
257 WHERE
258 RowID = @Loop_StepRow
259
260 ----------------------------------------------
261 -- Shrink - only runs on the top level
262 ----------------------------------------------
263 -- Here is where the work occurs
264 IF @Loop_Step_Name = 'Shrink Database'
265 BEGIN
266
267 -- we do this once for the whole database
268 BEGIN TRY
269 DBCC SHRINKDATABASE(@Loop_Database_Name) WITH NO_INFOMSGS
270 END TRY
271 BEGIN CATCH
272 SET @Loop_StepResult = 'Error encountered. - ' + ERROR_MESSAGE()
273 END CATCH
274
275 END
276 ELSE
277 BEGIN
278 ----------------------------------------------
279 -- items running on table by table level
280 ----------------------------------------------
281
282 ----------------------------------------------
283 -- load the tables
284 ----------------------------------------------
285 -- Clear the table
286 TRUNCATE TABLE #Temp_Tables
287 SET @Loop_TempSQL = 'SELECT object_ID, Replace(Replace(Name,'']'',''''),''['','''') from [' +
288 @Loop_Database_Name + '].sys.tables where type=''U'' AND
289 Name Not Like ''Backup_%'' AND
290 Name Not Like ''%_Backup_%'' AND
291 Name Not Like ''IR_OBJ_%''
292 ORDER BY
293 Replace(Replace(Name,'']'',''''),''['','''')
294 '
295
296 INSERT INTO #Temp_Tables (tableid,tableName) EXEC(@Loop_TempSQL)
297
298 SELECT @Loop_TableCount = COUNT(*) FROM #Temp_Tables
299 SET @Loop_TableRow = 1
300
301 ----------------------------------------------
302 -- Loop through each table
303 ----------------------------------------------
304 WHILE @Loop_TableRow <= @Loop_TableCount
305 BEGIN
306
307 -- Clear
308 SELECT
309 @Loop_Table_ID = 0,
310 @Loop_Table_Name = '',
311 @Loop_TableResult = NULL,
312 @Loop_TableStart = GETDATE()
313
314 -- Grab the next step
315 SELECT
316 @Loop_Table_ID = TableID,
317 @Loop_Table_Name = TableName
318 FROM
319 #Temp_Tables
320 WHERE
321 RowID = @Loop_TableRow
322
323 ----------------------------------------------
324 -- Rebuild indexes checks on each table
325 ----------------------------------------------
326 -- Here is where the work occurs
327 IF @Loop_Step_Name = 'Rebuild Indexes'
328 BEGIN
329 TRUNCATE TABLE #Temp_ReindexCheckResult
330
331 SET @Loop_TempSQL = '
332 SELECT
333 MAX(avg_Fragmentation_in_percent)
334 FROM
335 [' + @Loop_Database_Name + '].sys.dm_db_index_physical_stats(' + CAST(@Loop_Database_ID as varchar(25)) + ', ' + CAST(@Loop_Table_Id as varchar(25)) + ', NULL, NULL, NULL)
336 WHERE
337 index_type_desc <> ''HEAP''
338 GROUP BY
339 OBJECT_ID'
340
341 INSERT INTO #Temp_ReindexCheckResult (MaximumFragmentation) EXEC(@Loop_TempSQL)
342
343 SET @Loop_Table_DefragLevel = 0
344
345 SELECT @Loop_Table_DefragLevel = MAX(MaximumFragmentation)
346 FROM #Temp_ReindexCheckResult
347
348 IF @Loop_Table_DefragLevel >= 30
349 BEGIN
350 SET @Loop_TempSQL = 'DBCC DBREINDEX(''[' + @Loop_Database_Name + ']..[' + @Loop_Table_Name + ']'','''',0) WITH NO_INFOMSGS'
351
352 BEGIN TRY
353 EXEC ( @Loop_TempSQL)
354 SET @Loop_TableResult = 'Reindexed - ' + CAST(@Loop_Table_DefragLevel as varchar(25)) + '% fragmented.'
355 END TRY
356 BEGIN CATCH
357 SET @Loop_TableResult = 'Error encountered. - ' + ERROR_MESSAGE()
358 END CATCH
359
360 END
361 ELSE
362 BEGIN
363
364 SET @Loop_TableResult = CASE WHEN @Loop_Table_DefragLevel IS NULL THEN 'Skipped - No indexes'
365 ELSE 'Skipped - ' + CAST(@Loop_Table_DefragLevel as varchar(25)) + '% fragmented.' END
366 END
367
368 END
369 ELSE
370 BEGIN
371 ----------------------------------------------
372 -- Reorganize table by table
373 ----------------------------------------------
374 IF @Loop_Step_Name = 'Reorganize Indexes'
375 BEGIN
376
377 -- we do this once for the whole database
378 SET @Loop_TempSQL = ''
379 SET @Loop_TempSQL = 'ALTER INDEX ALL ON [' + @Loop_Database_Name + ']..[' + @Loop_Table_Name + '] REORGANIZE WITH ( LOB_COMPACTION = ON )'
380
381 BEGIN TRY
382 EXEC ( @Loop_TempSQL)
383 SET @Loop_TableResult = 'Reorganized'
384 END TRY
385 BEGIN CATCH
386 SET @Loop_TableResult = 'Error encountered. - ' + ERROR_MESSAGE()
387 END CATCH
388
389 END
390 END
391
392 ----------------------------------------------
393 -- Log Table Completion
394 ----------------------------------------------
395 INSERT INTO dbo.DBMaintenanceStepLog
396 (ParentLogID,DatabaseID, TableID, StepID, StartDateStamp, EndDateStamp, Result)
397 SELECT @Loop_MainLogID, @Loop_Database_ID,@Loop_Table_ID,@Loop_Step_ID,@Loop_TableStart,GETDATE(), @Loop_TableResult
398
399 -- Increment the table counter
400 SET @Loop_TableRow = @Loop_TableRow + 1
401
402 END
403 END
404
405 ----------------------------------------------
406 -- Log Step Completion
407 ----------------------------------------------
408 INSERT INTO dbo.DBMaintenanceStepLog
409 (ParentLogID,DatabaseID, TableID, StepID, StartDateStamp, EndDateStamp, Result)
410 SELECT @Loop_MainLogID, @Loop_Database_ID,0,@Loop_Step_ID,@Loop_StepStart,GETDATE(), @Loop_StepResult
411
412 -- Increment the step counter
413 SET @Loop_StepRow = @Loop_StepRow + 1
414
415 END
416 END
417
418 ----------------------------------------------
419 -- Log Database Completion
420 ----------------------------------------------
421 INSERT INTO dbo.DBMaintenanceStepLog
422 (ParentLogID,DatabaseID, TableID, StepID, StartDateStamp, EndDateStamp, Result)
423 SELECT @Loop_MainLogID, @Loop_Database_ID,0,0,@Loop_DatabaseStart,GETDATE(), @Loop_DatabaseResult
424
425 -- Increment the database counter
426 SET @Loop_DatabaseRow = @Loop_DatabaseRow + 1
427
428END
429
430----------------------------------------------
431-- update Main Log Entry
432----------------------------------------------
433UPDATE
434 dbo.DBMaintenanceLog
435SET
436 EndDateStamp = GETDATE()
437WHERE
438 ID = @Loop_MainLogID
439
440
441--GO