· 8 years ago · Dec 19, 2017, 08:50 PM
1USE [msdb]
2GO
3
4/****** Object: Job [GTD- Plan de Mantencion] Script Date: 17/01/2017 12:30:45 pm ******/
5BEGIN TRANSACTION
6DECLARE @ReturnCode INT
7SELECT @ReturnCode = 0
8/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 17/01/2017 12:30:45 pm ******/
9IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
10BEGIN
11EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
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'GTD - Plan de Mantencion',
18 @enabled=1,
19 @notify_level_eventlog=2,
20 @notify_level_email=0,
21 @notify_level_netsend=0,
22 @notify_level_page=0,
23 @delete_level=0,
24 @description=N'No description available.',
25 @category_name=N'[Uncategorized (Local)]',
26 @owner_login_name=N'sa', @job_id = @jobId OUTPUT
27IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
28/****** Object: Step [Update and Recreate Index] Script Date: 17/01/2017 12:30:46 pm ******/
29EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update and Recreate Index',
30 @step_id=1,
31 @cmdexec_success_code=0,
32 @on_success_action=3,
33 @on_success_step_id=0,
34 @on_fail_action=3,
35 @on_fail_step_id=0,
36 @retry_attempts=0,
37 @retry_interval=0,
38 @os_run_priority=0, @subsystem=N'TSQL',
39 @command=N'--Tarea a programar en JOB
40DECLARE @dbname NVARCHAR(100);
41
42DECLARE cDatabases CURSOR FOR
43SELECT name FROM sys.databases
44WHERE name NOT IN(''master'', ''model'', ''tempdb'') AND state = 0 AND is_read_only = 0
45
46-- Open the cursor.
47OPEN cDatabases;
48
49-- Loop through the partitions.
50WHILE (1=1)
51 BEGIN;
52 FETCH NEXT
53 FROM cDatabases
54 INTO @dbname;
55
56 IF @@FETCH_STATUS < 0 BREAK;
57
58 EXEC master.dbo.dba_MantencionIndices @dbname;
59
60 END;
61
62-- Close and deallocate the cursor.
63CLOSE cDatabases;
64DEALLOCATE cDatabases;',
65 @database_name=N'master',
66 @flags=0
67IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
68/****** Object: Step [Update Statistic] Script Date: 17/01/2017 12:30:46 pm ******/
69EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update Statistic',
70 @step_id=2,
71 @cmdexec_success_code=0,
72 @on_success_action=1,
73 @on_success_step_id=0,
74 @on_fail_action=2,
75 @on_fail_step_id=0,
76 @retry_attempts=0,
77 @retry_interval=0,
78 @os_run_priority=0, @subsystem=N'TSQL',
79 @command=N'DECLARE @dbname NVARCHAR(100);
80
81DECLARE cDatabases CURSOR FOR
82SELECT name FROM sys.databases
83WHERE name NOT IN(''master'', ''model'', ''tempdb'') AND state = 0
84
85-- Open the cursor.
86OPEN cDatabases;
87
88-- Loop through the partitions.
89WHILE (1=1)
90 BEGIN;
91 FETCH NEXT
92 FROM cDatabases
93 INTO @dbname;
94
95 IF @@FETCH_STATUS < 0 BREAK;
96
97 EXEC master.dbo.dba_MantencionEstadistica @dbname, 1, 100;
98
99 END;
100
101-- Close and deallocate the cursor.
102CLOSE cDatabases;
103DEALLOCATE cDatabases;',
104 @database_name=N'master',
105 @flags=0
106IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
107EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
108IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
109EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule Run',
110 @enabled=1,
111 @freq_type=8,
112 @freq_interval=16,
113 @freq_subday_type=1,
114 @freq_subday_interval=0,
115 @freq_relative_interval=0,
116 @freq_recurrence_factor=1,
117 @active_start_date=20150420,
118 @active_end_date=99991231,
119 @active_start_time=200000,
120 @active_end_time=235959,
121 @schedule_uid=N'9b8aff07-cdd7-4a29-9ea7-55b6dfe94957'
122IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
123EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
124IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
125COMMIT TRANSACTION
126GOTO EndSave
127QuitWithRollback:
128 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
129EndSave:
130
131GO
132
133USE MASTER
134GO
135
136CREATE PROCEDURE [dbo].[MantencionIndicesParametros]
137 @dbname nvarchar(100),
138 @objectid int,
139 @indexid int,
140 @objectname nvarchar(130) OUT,
141 @schemaname nvarchar(130) OUT,
142 @indexname nvarchar(130) OUT,
143 @partitioncount bigint OUT
144AS
145BEGIN
146
147 DECLARE @SQL NVARCHAR(MAX);
148
149 SET @SQL = 'SELECT QUOTENAME(o.name) Name
150 INTO ##tmp
151 FROM [' + @dbname + '].sys.objects AS o JOIN [' + @dbname + '].sys.schemas as s ON s.schema_id = o.schema_id
152 WHERE o.object_id = ' + CAST(@objectid AS NVARCHAR)+ ';'
153 EXEC(@SQL)
154 SET @objectname = (SELECT Name FROM ##tmp)
155 DROP TABLE ##tmp
156
157
158 SET @SQL = 'SELECT QUOTENAME(s.name) Name
159 INTO ##tmp
160 FROM [' + @dbname + '].sys.objects AS o JOIN [' + @dbname + '].sys.schemas as s ON s.schema_id = o.schema_id
161 WHERE o.object_id = ' + CAST(@objectid AS NVARCHAR)+ ';'
162 EXEC(@SQL)
163 SET @schemaname = (SELECT Name FROM ##tmp)
164 DROP TABLE ##tmp
165
166
167 SET @SQL = 'SELECT QUOTENAME(name) Name
168 INTO ##tmp
169 FROM [' + @dbname + '].sys.indexes
170 WHERE object_id = ' + CAST(@objectid AS NVARCHAR)+ ' AND index_id = ' + CAST(@indexid AS NVARCHAR)+ ';'
171 EXEC(@SQL)
172 SET @indexname = (SELECT Name FROM ##tmp)
173 DROP TABLE ##tmp
174
175
176 SET @SQL = 'SELECT count(*) Reg
177 INTO ##tmp
178 FROM [' + @dbname + '].sys.partitions
179 WHERE object_id = ' + CAST(@objectid AS NVARCHAR)+ ' AND index_id = ' + CAST(@indexid AS NVARCHAR)+ ';'
180 EXEC(@SQL)
181 SET @partitioncount = CAST((SELECT Reg FROM ##tmp) AS BIGINT);
182 DROP TABLE ##tmp
183
184END
185
186
187
188USE MASTER
189GO
190-- Procedimiento que se ejecuta desde JOB anterior
191CREATE PROCEDURE [dbo].[MantencionIndices]
192 @dbname as NVARCHAR(100)
193AS
194BEGIN
195 -- Ensure a USE <databasename> statement has been executed first.
196 SET NOCOUNT ON;
197 DECLARE @objectid int;
198 DECLARE @indexid int;
199 DECLARE @partitioncount bigint;
200 DECLARE @schemaname nvarchar(130);
201 DECLARE @objectname nvarchar(130);
202 DECLARE @indexname nvarchar(130);
203 DECLARE @partitionnum bigint;
204 DECLARE @partitions bigint;
205 DECLARE @frag float;
206 DECLARE @command nvarchar(MAX);
207 DECLARE @dbidname smallint;
208 DECLARE @ErrorExec nvarchar(max);
209
210 -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
211 -- and convert object and index IDs to names.
212 SET @dbidname = DB_ID(@dbname);
213
214 SELECT
215 object_id AS objectid,
216 index_id AS indexid,
217 partition_number AS partitionnum,
218 avg_fragmentation_in_percent AS frag
219 INTO #work_to_do
220 FROM sys.dm_db_index_physical_stats(@dbidname, NULL, NULL , NULL, 'LIMITED')
221 WHERE avg_fragmentation_in_percent > 25 AND index_id > 0;
222
223
224 -- Declare the cursor for the list of partitions to be processed.
225 DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
226
227 -- Open the cursor.
228 OPEN partitions;
229
230 -- Loop through the partitions.
231 WHILE (1=1)
232 BEGIN;
233 FETCH NEXT
234 FROM partitions
235 INTO @objectid, @indexid, @partitionnum, @frag;
236
237 IF @@FETCH_STATUS < 0 BREAK;
238
239 EXEC master.dbo.MantencionIndicesParametros
240 @dbname,
241 @objectid,
242 @indexid,
243 @objectname OUTPUT,
244 @schemaname OUTPUT,
245 @indexname OUTPUT,
246 @partitionnum OUTPUT;
247
248 -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
249 IF @frag < 30.0
250 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + QUOTENAME(@dbname) + N'.' + @schemaname + N'.' + @objectname + N' REORGANIZE';
251 IF @frag >= 30.0
252 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + QUOTENAME(@dbname) + N'.' + @schemaname + N'.' + @objectname + N' REBUILD';
253 IF @partitioncount > 1
254 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
255
256 BEGIN TRY
257 EXEC(@command);
258 PRINT 'Se ha ejecutado la sentencia: ' + @command;
259 END TRY
260 BEGIN CATCH
261 SET @ErrorExec = CAST(ERROR_NUMBER() AS NVARCHAR) + ' - ' + ERROR_MESSAGE()
262 PRINT @ErrorExec + 'Al ejecutar este coamndo: ' + @command;
263 END CATCH;
264 END;
265
266 -- Close and deallocate the cursor.
267 CLOSE partitions;
268 DEALLOCATE partitions;
269
270 -- Drop the temporary table.
271 BEGIN TRY
272 DROP TABLE #work_to_do;
273 END TRY
274 BEGIN CATCH
275 SET @ErrorExec = CAST(ERROR_NUMBER() AS NVARCHAR) + ' - ' + ERROR_MESSAGE()
276 PRINT @ErrorExec + 'Al ejecutar este coamndo "DROP TABLE #work_to_do"'
277 END CATCH
278
279END
280
281
282USE MASTER
283GO
284
285-- Procedimiento que se ejecuta desde JOB anterior
286CREATE PROCEDURE [dbo].[Estadistica]
287 @dbname as NVARCHAR(100),
288 @MaxDaysOld int = 7,
289 @SamplePercent int = NULL,
290 @SampleType nvarchar(50) = 'PERCENT'
291AS
292BEGIN
293 DECLARE @SQL nvarchar(max);
294 DECLARE @ErrorExec nvarchar(max);
295 DECLARE @dbidname smallint;
296
297 SET @dbidname = DB_ID(@dbname);
298
299 BEGIN TRY
300 DROP TABLE ##OldStats
301 END TRY
302 BEGIN CATCH
303 SET @ErrorExec = CAST(ERROR_NUMBER() AS NVARCHAR) + ' - ' + ERROR_MESSAGE()
304 PRINT @ErrorExec + 'Al ejecutar este coamndo "DROP TABLE ##OldStats"'
305 END CATCH
306
307 SET @SQL = '
308 USE [' + @dbname + '];
309 SELECT
310 RowNum = ROW_NUMBER() OVER (ORDER BY ISNULL(STATS_DATE(object_id, st.stats_id),1))
311 ,TableName = QUOTENAME(''' + @dbname + ''') + ''.'' + QUOTENAME(OBJECT_SCHEMA_NAME(st.object_id)) + ''.'' + QUOTENAME(OBJECT_NAME(st.object_id))
312 ,StatName = QUOTENAME(st.name)
313 ,StatDate = ISNULL(STATS_DATE(object_id, st.stats_id),1)
314 INTO ##OldStats
315 FROM [' + @dbname + '].sys.stats st WITH (nolock) inner join sys.sysobjects so on st.object_id = so.id
316 WHERE DATEDIFF(day, ISNULL(STATS_DATE(object_id, st.stats_id),1), GETDATE()) > ' + CAST(@MaxDaysOld AS NVARCHAR) + ' AND
317 OBJECT_SCHEMA_NAME(st.object_id)<>''SYS'' AND so.type NOT IN(''TF'')
318 ORDER BY ROW_NUMBER() OVER (ORDER BY ISNULL(STATS_DATE(object_id, st.stats_id),1));';
319
320 EXEC sp_executesql @SQL;
321
322 DECLARE @MaxRecord int
323 DECLARE @CurrentRecord int
324 DECLARE @TableName nvarchar(255)
325 DECLARE @StatName nvarchar(255)
326 DECLARE @SampleSize nvarchar(100)
327
328 SET @MaxRecord = (SELECT MAX(RowNum) FROM ##OldStats WITH(NOLOCK))
329 SET @CurrentRecord = 1
330 SET @SQL = ''
331 SET @SampleSize = ISNULL(' WITH SAMPLE ' + CAST(@SamplePercent AS nvarchar(20)) + ' ' + @SampleType,N'')
332
333 WHILE @CurrentRecord <= @MaxRecord
334 BEGIN
335
336 SELECT
337 @TableName = os.TableName
338 ,@StatName = os.StatName
339 FROM ##OldStats os WITH(NOLOCK)
340 WHERE RowNum = @CurrentRecord
341
342 SET @SQL = N'UPDATE STATISTICS ' + @TableName + ' ' + @StatName + @SampleSize
343
344 BEGIN TRY
345 EXEC(@SQL);
346 PRINT 'Se ha ejecutado la sentencia: ' + @SQL;
347 END TRY
348 BEGIN CATCH
349 SET @ErrorExec = CAST(ERROR_NUMBER() AS NVARCHAR) + ' - ' + ERROR_MESSAGE()
350 PRINT @ErrorExec + 'Al ejecutar este coamndo: ' + @SQL;
351 END CATCH;
352
353 PRINT 'Se ha ejecutado la sentencia: ' + @SQL;
354
355
356 SET @CurrentRecord = @CurrentRecord + 1
357
358 END
359END