-
Notifications
You must be signed in to change notification settings - Fork 671
/
dbo.usp_SetFileGrowth.sql
292 lines (270 loc) · 13.2 KB
/
dbo.usp_SetFileGrowth.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
/**********************************************************************************************
* PURPOSE : This procedure set minimal File growth values for every datafiles and logfiles (SQL 2005 and above)
* usp_SetFileGrowth _job is executed every 6h daily
* Create usp_SetFileGrowth stored procedure and usp_SetFileGrowth_job job.
***********************************************************************************************/
IF OBJECT_ID('dbo.usp_SetFileGrowth', 'P') IS NULL
EXECUTE ('CREATE PROCEDURE dbo.usp_SetFileGrowth AS SELECT 1');
GO
ALTER PROCEDURE usp_SetFileGrowth
(
@datafilesize_lowerlimit INT,
@datafilesize_upperlimit INT,
@datafile_growth_target_lower INT,
@datafile_growth_target_medium INT,
@datafile_growth_target_upper INT,
@logfilesize_lowerlimit INT,
@logfilesize_upperlimit INT,
@logfile_growth_target_lower INT,
@logfile_growth_target_medium INT,
@logfile_growth_target_upper INT
)
AS
/*
*************************************************************************************************************
Name : dbo.usp_SetFileGrowth
Purpose : Set minimal autogrow values for datafiles and logfiles depending on the file size
If autogrow is already set with higher value, nothing is done
If autogrow is disable, nothing is done
If autogrow is in percent, autogrow is set in MB
Author : Samuel HERVOUET
Prerequisite : None
How to use it : EXEC DBM_Toolbox.dbo.usp_SetFileGrowth @datafilesize_lowerlimit
,@datafilesize_upperlimit
,@datafile_growth_target_lower
,@datafile_growth_target_medium
,@datafile_growth_target_upper
,@logfilesize_lowerlimit
,@logfilesize_upperlimit
,@logfile_growth_target_lower
,@logfile_growth_target_medium
,@logfile_growth_target_upper
Examples : EXEC DBM_Toolbox.dbo.usp_SetFileGrowth 400,2000,100,200,500,null,null,null,null,null
For Datafiles less than 400 MB, set 100 MB autogrow
For Datafiles between 400 MB and 2000 MB set 200 MB autogrow
For Datafiles greater than 2000 MB set 500 MB autogrow
EXEC DBM_Toolbox.dbo.usp_SetFileGrowth null,null,null,null,null,1000,5000,200,500,1000
For Logfiles less than 1000 MB, set 200 MB autogrow
For Logfiles between 1000 MB and 5000 MB set 500 MB autogrow
For Logfiles greater than 5000 MB set 1000 MB autogrow
EXEC DBM_Toolbox.dbo.usp_SetFileGrowth 400,2000,100,200,500,1000,5000,200,500,1000
Set autogrow for Datafiles AND Logfiles as explained in the 2 previous examples
Version : 1.0
************************************************************************************************************
*/
/* -- For test only
DECLARE @datafilesize_lowerlimit INT
DECLARE @datafilesize_upperlimit INT
DECLARE @datafile_growth_target_lower INT
DECLARE @datafile_growth_target_medium INT
DECLARE @datafile_growth_target_upper INT
-----------
DECLARE @logfilesize_lowerlimit INT
DECLARE @logfilesize_upperlimit INT
DECLARE @logfile_growth_target_lower INT
DECLARE @logfile_growth_target_medium INT
DECLARE @logfile_growth_target_upper int
-------------------------------------------------
SET @datafilesize_lowerlimit = 400
SET @datafilesize_upperlimit = 2000
SET @datafile_growth_target_lower = 100
SET @datafile_growth_target_medium = 200
SET @datafile_growth_target_upper = 500
----------
SET @logfilesize_lowerlimit = 500
SET @logfilesize_upperlimit = 1000
SET @logfile_growth_target_lower = 200
SET @logfile_growth_target_medium = 500
SET @logfile_growth_target_upper = 1000
*/
SET NOCOUNT ON
PRINT '*********** Parameters ******************'
PRINT 'Datafiles size lower limit : ' + convert(varchar(20), @datafilesize_lowerlimit) + ' MB'
PRINT 'Datafiles size upper limit : ' + convert(varchar(20), @datafilesize_upperlimit) + ' MB'
PRINT 'Datafiles growth lower target : ' + convert(varchar(20), @datafile_growth_target_lower) + ' MB'
PRINT 'Datafiles growth medium target : ' + convert(varchar(20), @datafile_growth_target_medium) + ' MB'
PRINT 'Datafiles growth upper target : ' + convert(varchar(20), @datafile_growth_target_upper) + ' MB'
PRINT ' '
PRINT 'Logfiles size lower limit : ' + convert(varchar(20), @logfilesize_lowerlimit) + ' MB'
PRINT 'Logfiles size upper limit : ' + convert(varchar(20), @logfilesize_upperlimit) + ' MB'
PRINT 'Logfiles growth lower target : ' + convert(varchar(20), @logfile_growth_target_lower) + ' MB'
PRINT 'Logfiles growth medium target : ' + convert(varchar(20), @logfile_growth_target_medium) + ' MB'
PRINT 'Logfiles growth upper target : ' + convert(varchar(20), @logfile_growth_target_upper) + ' MB'
PRINT '*****************************************'
-- Retrieve files informations
DECLARE @sql VARCHAR(8000)
SET @sql=' USE [?]
SELECT ''?'' [Dbname]
,[name] [Filename]
,type_desc [Type]
,physical_name [FilePath]
,CONVERT(INT,[size]/128.0) [TotalSize_MB]
,CONVERT(INT,FILEPROPERTY(name, ''SpaceUsed''))/128.0 AS [Space_Used_MB]
,CASE is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR(5),growth)
ELSE CONVERT(VARCHAR(20),(growth/128))
END [Autogrow_Value]
,CASE is_percent_growth
WHEN 1 THEN ''Pct''
ELSE ''MB''
END [Unit]
,CASE max_size
WHEN -1 THEN CASE growth
WHEN 0 THEN CONVERT(VARCHAR(30),''Restricted'')
ELSE CONVERT(VARCHAR(30),''Unlimited'') END
ELSE CONVERT(VARCHAR(25),max_size/128)
END [Max_Size]
FROM [?].sys.database_files'
-- Create temp table to store Files informations
IF EXISTS(SELECT 1 FROM tempdb..sysobjects WHERE name='##AutogrowthDetails')
DROP TABLE ##AutogrowthDetails
CREATE TABLE ##AutogrowthDetails (
Dbname VARCHAR(128)
,Filename VARCHAR(128)
,Type VARCHAR(10)
,Filepath VARCHAR(2000)
,TotalSize_MB INT
,Space_Used_MB INT
,Autogrow_Value VARCHAR(15)
,Unit VARCHAR(15)
,Max_Size VARCHAR(30)
)
INSERT INTO ##AutogrowthDetails EXEC sp_msforeachdb @sql
DECLARE @dbname varchar(8000)
DECLARE @file varchar(8000)
DECLARE @filename varchar(8000)
DECLARE @type varchar(20)
DECLARE @totalsizemb int
DECLARE @autogrowthvalue varchar(20)
DECLARE @filegrowth_target varchar(20)
DECLARE @sql2 varchar(8000)
-- Set file autogrow value depending on thresholds
DECLARE SetAutogrowthSize cursor for
SELECT dbname, filename, type, TotalSize_MB, Autogrow_Value
FROM ##AutogrowthDetails
WHERE dbname not in ('master','msdb','tempdb','model')
AND dbname IN (select name from sys.databases where state_desc = 'ONLINE')
AND Autogrow_Value <> 0
AND (
(
(TotalSize_MB < @datafilesize_lowerlimit AND Autogrow_Value < @datafile_growth_target_lower and type = 'ROWS')
OR
((TotalSize_MB BETWEEN @datafilesize_lowerlimit AND @datafilesize_upperlimit) AND Autogrow_Value < @datafile_growth_target_medium AND type = 'ROWS')
OR
(TotalSize_MB > @datafilesize_upperlimit AND Autogrow_Value < @datafile_growth_target_upper AND type = 'ROWS')
)
OR
(
(TotalSize_MB < @logfilesize_lowerlimit AND Autogrow_Value < @logfile_growth_target_lower and type = 'LOG')
OR
((TotalSize_MB BETWEEN @logfilesize_lowerlimit AND @logfilesize_upperlimit) AND Autogrow_Value < @logfile_growth_target_medium AND type = 'LOG')
OR
(TotalSize_MB > @logfilesize_upperlimit AND Autogrow_Value < @logfile_growth_target_upper AND type = 'LOG')
)
)
ORDER BY dbname, filename
OPEN SetAutogrowthSize
FETCH NEXT FROM SetAutogrowthSize INTO @dbname, @file, @type, @totalsizemb, @autogrowthvalue
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type = 'ROWS' AND (@totalsizemb < @datafilesize_lowerlimit AND @autogrowthvalue < @datafile_growth_target_lower)
SET @filegrowth_target = @datafile_growth_target_lower
IF @type = 'ROWS' AND ((@totalsizemb between @datafilesize_lowerlimit AND @datafilesize_upperlimit) AND @autogrowthvalue < @datafile_growth_target_medium)
SET @filegrowth_target = @datafile_growth_target_medium
IF @type = 'ROWS' AND ((@totalsizemb = @datafilesize_upperlimit OR @totalsizemb > @datafilesize_upperlimit) AND @autogrowthvalue < @datafile_growth_target_upper)
SET @filegrowth_target = @datafile_growth_target_upper
IF @type = 'LOG' AND (@totalsizemb < @logfilesize_lowerlimit AND @autogrowthvalue < @logfile_growth_target_lower)
SET @filegrowth_target = @logfile_growth_target_lower
IF @type = 'LOG' AND ((@totalsizemb between @logfilesize_lowerlimit AND @logfilesize_upperlimit) AND @autogrowthvalue < @logfile_growth_target_medium)
SET @filegrowth_target = @logfile_growth_target_medium
IF @type = 'LOG' AND ((@totalsizemb = @logfilesize_upperlimit OR @totalsizemb > @logfilesize_upperlimit) AND @autogrowthvalue < @logfile_growth_target_upper)
SET @filegrowth_target = @logfile_growth_target_upper
SET @sql2 = 'ALTER DATABASE ['+ @dbname + '] MODIFY FILE (NAME = '''+@file+''', FILEGROWTH =' +@filegrowth_target + ')'
Print '********************************************************************************************************************************************'
Print '| Database Name: ' + @dbname + ' | Logical File Name: ' + @file + ' | File Type: ' + @type + ' | Current Size: ' + convert(varchar(20), @totalsizemb) + ' MB | Current Growth increment: ' + @autogrowthvalue
Print '| Process will update filegrowth size from ' + convert(varchar(20), @autogrowthvalue) + ' to ' + convert(varchar(20), @filegrowth_target) + ' MB'
Print '| Executing following ALTER command: '
Print '| '+ @sql2
Print '********************************************************************************************************************************************'
Print ' '
exec (@sql2)
FETCH NEXT FROM SetAutogrowthSize INTO @dbname, @file, @type, @totalsizemb, @autogrowthvalue
END
CLOSE SetAutogrowthSize
DEALLOCATE SetAutogrowthSize
GO
-------------------------------------
-- Job creation (scheduled every 6h)
-------------------------------------
PRINT 'usp_SetFileGrowth_job job creation'
USE [msdb]
GO
IF EXISTS (SELECT name FROM [msdb].[dbo].[sysjobs] WHERE name = 'usp_SetFileGrowth_job')
BEGIN
PRINT 'Replace usp_SetFileGrowth_job'
EXEC msdb.dbo.sp_delete_job @job_name = N'usp_SetFileGrowth_job' ;
END
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'usp_SetFileGrowth_job',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'usp_SetFileGrowth_job', @server_name = N'(LOCAL)'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'usp_SetFileGrowth_job', @step_name=N'usp_SetFileGrowth_job - Step 1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC DBM_Toolbox.dbo.usp_SetFileGrowth 400,2000,100,200,500,1000,5000,100,500,1000
GO',
@database_name=N'DBM_Toolbox',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'usp_SetFileGrowth_job',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'DISTRIBUTED-DB',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'usp_SetFileGrowth_job', @name=N'Every6h',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=6,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20131125,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO