· 10 years ago · Sep 22, 2016, 03:46 AM
1---Run on master
2---Works for tables that contain a date "yyyymm" within the name
3USE [master]
4IF object_id('[dbo].[archive_log]') IS NULL AND object_id('[dbo].[PK_archive_log]') IS NULL
5BEGIN
6CREATE TABLE [dbo].[archive_log](
7[id] int IDENTITY(1,1) NOT NULL CONSTRAINT [PK_archive_log] PRIMARY KEY CLUSTERED,
8[source_database] sysname NULL,
9[source_schema] sysname NULL,
10[dest_database] sysname NULL,
11[dest_schema] sysname NULL,
12[start_time] datetime NOT NULL,
13[end_time] datetime NULL,
14[compression_type] nvarchar(10) NULL,
15[retention_months] int NULL,
16[identify_table_query] nvarchar(100) NULL,
17[source_table] sysname NULL,
18[table_orig_size_mb] nvarchar(100) NULL,
19[table_compressed_size_mb] nvarchar(100) NULL,
20[error_line] int NULL,
21[error_message] nvarchar(max) NULL
22)
23END
24GO
25
26ALTER PROCEDURE [dbo].[linked_server_move_table]
27 @test char(1),
28 @source_linked_server nvarchar(100),
29 @source_database nvarchar(100),
30 @source_schema nvarchar(50),
31 @dest_linked_server nvarchar(100),
32 @dest_database nvarchar(100),
33 @dest_schema nvarchar(50),
34 @retention_months int,
35 @compress_retained nvarchar(2),
36 @compress_latest_retained nvarchar(2),
37 @compression_type nvarchar(50),
38 @identify_table_query nvarchar(100),
39 @log_to_table nvarchar(2),
40 @table_date_index int,
41 @table_date_length int
42
43AS
44
45BEGIN
46
47 /*
48 Modify email configuration near end.
49
50 exec [source_linked_server].master.[dbo].[linked_server_move_table]
51 @test = 'N',
52 @source_linked_server = 'source_linked_server',
53 @source_database = 'MyDatabase',
54 @source_schema = 'dbo',
55 @dest_linked_server = 'dest_linked_server',
56 @dest_database = 'MyDatabase',
57 @dest_schema = 'dbo',
58 @retention_months = 13,
59 @compress_retained = 'N',
60 @compress_latest_retained = 'N',
61 @compression_type = 'NONE',
62 @identify_table_query = 'ACustomerTable20%',
63 @log_to_table = 'Y',
64 @table_date_index = 15,
65 @table_date_length = 8
66 */
67
68 DECLARE
69 @identify_table_command nvarchar(1000),
70 @source_table nvarchar(100),
71 @source_table_date datetime,
72 @source_table_age_months int,
73 @move_command nvarchar(1000),
74 @compress_command nvarchar(1000),
75 @drop_command nvarchar(1000),
76 @dest_command nvarchar(1000),
77 @exists_in_dest bit = 1,
78 @orig_size_command nvarchar(1000),
79 @compressed_size_command nvarchar(1000),
80 @table_orig_size nvarchar(100),
81 @table_compressed_size nvarchar(100),
82 @start_time datetime = getdate(),
83 @end_time datetime,
84 @error_message nvarchar(4000),
85 @error_line int,
86 @identify_retained_count_command nvarchar(1000),
87 @identify_retained_count int,
88 @log_to_table_stmt NVARCHAR(4000),
89 @move_execute_stmt NVARCHAR(4000)
90
91 IF @source_database = @dest_database AND @source_schema = @dest_schema
92 BEGIN
93 SET @identify_table_command =
94 N' SELECT top 1 @source_table = tab.name FROM ' + @source_database + '.sys.tables tab
95 JOIN ' + @source_database + '.sys.objects obj on obj.name = tab.name
96 JOIN ' + @source_database + '.sys.partitions part on part.object_id = obj.object_id
97 WHERE tab.name LIKE ''' + @identify_table_query + '''
98 GROUP BY tab.name
99 HAVING SUM(part.data_compression) = 0
100 ORDER BY tab.name asc'
101 END
102 ELSE
103 BEGIN
104 SET @identify_table_command =
105 N' SELECT top 1 @source_table = name FROM ' + @source_database + '.sys.tables
106 where name LIKE ''' + @identify_table_query + '''
107 order by name asc'
108 END
109
110 EXEC sp_executesql @identify_table_command, N'@source_table nvarchar(100) out', @source_table out
111
112 select @source_table_date = SUBSTRING ( @source_table , @table_date_index , @table_date_length )
113 select @source_table_age_months = DATEDIFF ( mm , @source_table_date , getdate() )
114
115 SET @dest_command =
116 N' SELECT top 1 name FROM [' + @dest_linked_server + '].' + @dest_database + '.sys.tables
117 where name = ''' + @source_table + '''
118 order by name asc'
119
120 EXEC sp_executesql @dest_command
121 IF @@ROWCOUNT = 0
122 BEGIN
123 SET @exists_in_dest = 0
124 PRINT @source_table + ' does not exist in destination [' + @dest_linked_server + '].' + @dest_database + '.' + @dest_schema
125 END
126 ELSE
127 BEGIN
128 PRINT @source_table + ' already exists in destination [' + @dest_linked_server + '].' + @dest_database + '.' + @dest_schema
129 END
130 PRINT '=============================='
131
132 IF @exists_in_dest = 0 AND (@source_table_age_months > @retention_months)
133 BEGIN
134 BEGIN TRAN
135
136 BEGIN TRY
137
138 SET @orig_size_command =
139 N' SELECT @table_orig_size =
140 CAST(((sum(a.total_pages) * 8) / 1024) AS NVARCHAR)
141 FROM
142 ' + @source_database + '.sys.tables t
143 INNER JOIN
144 ' + @source_database + '.sys.indexes i ON t.object_id = i.object_id
145 INNER JOIN
146 ' + @source_database + '.sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
147 INNER JOIN
148 ' + @source_database + '.sys.allocation_units a ON p.partition_id = a.container_id
149 WHERE
150 t.name NOT LIKE ''dt%'' AND
151 i.object_id > 255 AND
152 i.index_id <= 1 AND
153 t.name = ''' + @source_table + '''
154 GROUP BY
155 t.name, i.object_id, i.index_id, i.name'
156
157 SET @move_command =
158 N' SELECT * INTO ' + @dest_database + '.' + @dest_schema + '.' + @source_table +
159 ' FROM [' + @source_linked_server + '].' + @source_database + '.' + @source_schema + '.' + @source_table
160
161 SET @compress_command =
162 N' ALTER TABLE [' + @dest_linked_server + '].' + @dest_database + '.' + @dest_schema + '.' + @source_table +
163 ' REBUILD PARTITION = ALL
164 WITH
165 (DATA_COMPRESSION = ' + @compression_type + '
166 )'
167
168 SET @drop_command =
169 N' DROP TABLE ' + @source_database + '.' + @source_schema + '.' + @source_table
170
171 EXEC sp_executesql @orig_size_command, N' @table_orig_size nvarchar(100) out', @table_orig_size out
172
173 PRINT 'Begin move of ' + @source_table + ' to destination [' + @dest_linked_server + '].' + @dest_database + '.' + @dest_schema
174
175 SET @move_execute_stmt = N'EXEC (' + char(39) + @move_command + char(39) + ') AT [' + @dest_linked_server + '];'
176 IF @test = 'N'
177 BEGIN
178 EXEC sp_executesql @move_execute_stmt
179 END
180 ELSE IF @test = 'Y'
181 BEGIN
182 PRINT 'TEST: EXEC sp_executesql @move_execute_stmt'
183 PRINT @move_execute_stmt
184 END
185 PRINT 'Move Complete'
186 PRINT '=============================='
187
188 IF (UPPER(@compression_type) = 'ROW' OR UPPER(@compression_type) = 'PAGE')
189 BEGIN
190 PRINT 'Begin compression of ' + @source_table + ' in destination [' + @dest_linked_server + '].' + @dest_database + '.' + @dest_schema
191 EXEC sp_executesql @compress_command
192 PRINT 'Original size of ' + @source_table + ': ' + @table_orig_size + ' MB'
193 SET @compressed_size_command =
194 N' SELECT @table_compressed_size =
195 CAST(((sum(a.total_pages) * 8) / 1024) AS NVARCHAR)
196 FROM
197 [' + @dest_linked_server + '].' + @dest_database + '.sys.tables t
198 INNER JOIN
199 [' + @dest_linked_server + '].' + @dest_database + '.sys.indexes i ON t.object_id = i.object_id
200 INNER JOIN
201 [' + @dest_linked_server + '].' + @dest_database + '.sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
202 INNER JOIN
203 [' + @dest_linked_server + '].' + @dest_database + '.sys.allocation_units a ON p.partition_id = a.container_id
204 WHERE
205 t.name NOT LIKE ''dt%'' AND
206 i.object_id > 255 AND
207 i.index_id <= 1 AND
208 t.name = ''' + @source_table + '''
209 GROUP BY
210 t.name, i.object_id, i.index_id, i.name'
211
212 EXEC sp_executesql @compressed_size_command, N' @table_compressed_size nvarchar(100) out', @table_compressed_size out
213 PRINT 'Compressed size of ' + @source_table + ': ' + @table_compressed_size + ' MB'
214 PRINT 'Compression Complete'
215 PRINT '=============================='
216 END
217
218 PRINT 'Begin drop of ' + @source_table + ' in source ' + @source_database + '.' + @source_schema
219 IF @test = 'N'
220 BEGIN
221 EXEC sp_executesql @drop_command
222 END
223 ELSE IF @test = 'Y'
224 BEGIN
225 PRINT 'TEST: EXEC sp_executesql @drop_command'
226 PRINT @drop_command
227 END
228 PRINT 'Drop Complete'
229 PRINT '=============================='
230
231 END TRY
232 BEGIN CATCH
233 SET @error_message = ERROR_MESSAGE()
234 SET @error_line = ERROR_LINE()
235 PRINT 'Error occurred on line ' + cast(@error_line as varchar(10))
236 + ': ' + @error_message
237 -- error occurred, so rollback the transaction
238 ROLLBACK
239 END CATCH
240 -- if we were successful, we should still have a transaction, so commit it
241 IF @@TRANCOUNT > 0
242 COMMIT
243 END
244 ELSE IF @exists_in_dest = 1 AND @source_table_age_months > @retention_months AND @source_database != @dest_database
245 BEGIN
246 SET @drop_command =
247 N' DROP TABLE ' + @source_database + '.' + @source_schema + '.' + @source_table
248 select @drop_command
249
250 PRINT 'Begin drop of ' + @source_table + ' in source'
251 IF @test = 'N'
252 BEGIN
253 EXEC sp_executesql @drop_command
254 END
255 ELSE IF @test = 'Y'
256 BEGIN
257 PRINT 'TEST: EXEC sp_executesql @drop_command'
258 PRINT @drop_command
259 END
260
261 PRINT 'Drop Complete'
262 PRINT '=============================='
263 END
264 ELSE IF (@exists_in_dest = 0 OR (@source_database = @dest_database AND @source_schema = @dest_schema)) AND @compress_retained = 'Y'
265 BEGIN
266
267 SET @dest_database = @source_database
268 SET @dest_schema = @source_schema
269
270 SET @identify_table_command =
271 N' SELECT top 1 @source_table = tab.name FROM ' + @source_database + '.sys.tables tab
272 JOIN ' + @source_database + '.sys.objects obj on obj.name = tab.name
273 JOIN ' + @source_database + '.sys.partitions part on part.object_id = obj.object_id
274 WHERE tab.name LIKE ''' + @identify_table_query + '''
275 GROUP BY tab.name
276 HAVING SUM(part.data_compression) = 0
277 ORDER BY tab.name asc'
278
279 EXEC sp_executesql @identify_table_command, N'@source_table nvarchar(100) out', @source_table out
280
281 SET @identify_retained_count_command =
282 N' USE [' + @source_database + ']
283 select @identify_retained_count = count(*)
284 from (
285 SELECT count(distinct [data_compression_desc] ) as compression_types,
286 OBJECT_NAME(' + @source_database + '.sys.objects.object_id) AS [ObjectName]
287 FROM ' + @source_database + '.sys.partitions
288 INNER JOIN ' + @source_database + '.sys.objects
289 ON ' + @source_database + '.sys.partitions.object_id = ' + @source_database + '.sys.objects.object_id
290 AND SCHEMA_NAME(' + @source_database + '.sys.objects.schema_id) <> ''SYS''
291 AND OBJECT_NAME(' + @source_database + '.sys.objects.object_id) LIKE ''' + @identify_table_query + '''
292 group by OBJECT_NAME(' + @source_database + '.sys.objects.object_id)
293 )a
294 where a.compression_types = 1
295 USE [master]'
296
297 EXEC sp_executesql @identify_retained_count_command, N'@identify_retained_count nvarchar(100) out', @identify_retained_count out
298
299 BEGIN TRAN
300
301 BEGIN TRY
302
303 IF (@compress_latest_retained = 'Y' AND @identify_retained_count = 1) OR (@identify_retained_count > 1)
304 BEGIN
305 PRINT @source_table + ' eligible for source compression'
306
307 SET @orig_size_command =
308 N' SELECT @table_orig_size =
309 CAST(((sum(a.total_pages) * 8) / 1024) AS NVARCHAR)
310 FROM
311 ' + @source_database + '.sys.tables t
312 INNER JOIN
313 ' + @source_database + '.sys.indexes i ON t.object_id = i.object_id
314 INNER JOIN
315 ' + @source_database + '.sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
316 INNER JOIN
317 ' + @source_database + '.sys.allocation_units a ON p.partition_id = a.container_id
318 WHERE
319 t.name NOT LIKE ''dt%'' AND
320 i.object_id > 255 AND
321 i.index_id <= 1 AND
322 t.name = ''' + @source_table + '''
323 GROUP BY
324 t.name, i.object_id, i.index_id, i.name'
325
326 EXEC sp_executesql @orig_size_command, N' @table_orig_size nvarchar(100) out', @table_orig_size out
327
328 SET @compress_command =
329 N' ALTER TABLE ' + @source_database + '.' + @source_schema + '.' + @source_table +
330 ' REBUILD PARTITION = ALL
331 WITH
332 (DATA_COMPRESSION = ' + @compression_type + '
333 )'
334
335 IF UPPER(@compression_type) = 'ROW' OR UPPER(@compression_type) = 'PAGE'
336 BEGIN
337 PRINT 'Begin compression of ' + @source_table + ' in source ' + @source_database + '.' + @source_schema
338 EXEC sp_executesql @compress_command
339 PRINT 'Original size of ' + @source_table + ': ' + @table_orig_size + ' MB'
340 SET @compressed_size_command =
341 N' SELECT @table_compressed_size =
342 CAST(((sum(a.total_pages) * 8) / 1024) AS NVARCHAR)
343 FROM
344 ' + @source_database + '.sys.tables t
345 INNER JOIN
346 ' + @source_database + '.sys.indexes i ON t.object_id = i.object_id
347 INNER JOIN
348 ' + @source_database + '.sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
349 INNER JOIN
350 ' + @source_database + '.sys.allocation_units a ON p.partition_id = a.container_id
351 WHERE
352 t.name NOT LIKE ''dt%'' AND
353 i.object_id > 255 AND
354 i.index_id <= 1 AND
355 t.name = ''' + @source_table + '''
356 GROUP BY
357 t.name, i.object_id, i.index_id, i.name'
358
359 EXEC sp_executesql @compressed_size_command, N' @table_compressed_size nvarchar(100) out', @table_compressed_size out
360 PRINT 'Compressed size of ' + @source_table + ': ' + @table_compressed_size + ' MB'
361 PRINT 'Compression Complete'
362 PRINT '=============================='
363 END
364 END
365 ELSE
366 BEGIN
367 BEGIN TRY
368 RAISERROR ('No tables available in source that meet archive requirements',16,1)
369 END TRY
370 BEGIN CATCH
371 SET @error_message = ERROR_MESSAGE()
372 SET @error_line = ERROR_LINE()
373 PRINT 'Error occurred on line ' + cast(@error_line as varchar(10))
374 + ': ' + @error_message
375 END CATCH
376 END
377
378 END TRY
379 BEGIN CATCH
380 SET @error_message = ERROR_MESSAGE()
381 SET @error_line = ERROR_LINE()
382 PRINT 'Error occurred on line ' + cast(@error_line as varchar(10))
383 + ': ' + @error_message
384 -- error occurred, so rollback the transaction
385 ROLLBACK
386 EXEC msdb.dbo.sp_send_dbmail
387 @profile_name = N'Default',
388 --@recipients = N'company@email.com',
389 @reply_to = N'dev@email.com',
390 @blind_copy_recipients = N'dev@email.com',
391 @subject = 'Linked Server Archive Table Failure',
392 @body = @error_message
393 END CATCH
394 -- if we were successful, we should still have a transaction, so commit it
395 IF @@TRANCOUNT > 0
396 COMMIT
397
398 END
399 ELSE
400 BEGIN
401 BEGIN TRY
402 RAISERROR ('No tables available in source that meet archive requirements',16,1)
403 END TRY
404 BEGIN CATCH
405 SET @error_message = ERROR_MESSAGE()
406 SET @error_line = ERROR_LINE()
407 PRINT 'Error occurred on line ' + cast(@error_line as varchar(10))
408 + ': ' + @error_message
409 END CATCH
410 END
411
412 SET @end_time = getdate()
413
414 SET @log_to_table_stmt = N'
415 INSERT INTO [' + @dest_linked_server + '].master.dbo.archive_log (
416 [source_database],
417 [source_schema],
418 [dest_database],
419 [dest_schema],
420 [start_time],
421 [end_time],
422 [compression_type],
423 [retention_months],
424 [identify_table_query],
425 [source_table],
426 [table_orig_size_mb],
427 [table_compressed_size_mb],
428 [error_line],
429 [error_message])
430 VALUES (
431 ' + CHAR(39) + ISNULL(@source_database,'') + CHAR(39) + ',
432 ' + CHAR(39) + ISNULL(@source_schema,'') + CHAR(39) + ',
433 ' + CHAR(39) + ISNULL(@dest_database,'') + CHAR(39) + ',
434 ' + CHAR(39) + ISNULL(@dest_schema,'') + CHAR(39) + ',
435 CAST(' + CHAR(39) + CONVERT(NVARCHAR(23), ISNULL(@start_time,''), 121) + CHAR(39) + ' AS DATETIME),
436 CAST(' + CHAR(39) + CONVERT(NVARCHAR(23), ISNULL(@end_time,''), 121) + CHAR(39) + ' AS DATETIME),
437 ' + CHAR(39) + ISNULL(@compression_type,'') + CHAR(39) + ',
438 CAST(' + CHAR(39) + CAST(ISNULL(@retention_months,'') AS NVARCHAR(23)) + CHAR(39) + ' AS INT),
439 ' + CHAR(39) + ISNULL(@identify_table_query,'') + CHAR(39) + ',
440 ' + CHAR(39) + ISNULL(@source_table,'') + CHAR(39) + ',
441 ' + CHAR(39) + ISNULL(@table_orig_size,'') + CHAR(39) + ',
442 ' + CHAR(39) + ISNULL(@table_compressed_size,'') + CHAR(39) + ',
443 CAST(' + CHAR(39) + CAST(ISNULL(@error_line,'') AS NVARCHAR(23)) + CHAR(39) + ' AS INT),
444 ' + CHAR(39) + ISNULL(@error_message,'') + CHAR(39) + ')
445 '
446
447 IF @log_to_table = 'Y'
448 BEGIN
449 EXECUTE dbo.sp_executesql @log_to_table_stmt
450 END
451 ELSE
452 BEGIN
453 PRINT 'Statement to log to archive_history:'
454 PRINT @log_to_table_stmt
455 END
456
457END