you are already in trouble and it is too late when …
- The transaction log becomes full, SQL Server Database Engine issues a 9002 error.
- The SQL Server Database Filegroup is Full.
- Run out of disk space
You must have the answer to theses questions:
- Who many times the database base can still growth?
- Do we use the best Practices for Growing Database Files?
Growing database files by a percentage is relatively harmless when databases are small. Once you’ve grown beyond a few gigabytes, a ten percent file growth can be a cause of performance problems while SQL Server has to wait for space to be allocated.
At the other extreme, for databases over a few gigabytes, it doesn’t make sense to keep growing in tiny 1 MB increments where you can run into extreme physical fragmentation.
Generally, it is rather to grow in increments than in percentages.
Script to list database files that use percent growth, you do not need to change, you just need to be sure it’s okay.
1SELECT d.name as database_name,
2 mf.name as file_name,
3 mf.type_desc as file_type,
4 mf.growth as current_percent_growth
5
6FROM sys.master_files mf (NOLOCK)
7JOIN sys.databases d (NOLOCK) on mf.database_id=d.database_id
8WHERE is_percent_growth=1
Script to monitoring size files.
1DECLARE @growth_count INT= 3; -- Who many time the database can growth ?
2SET NOCOUNT ON;
3--#region Local drives
4-- DROP TABLE IF EXISTS #svr_drives;
5
6
7CREATE TABLE #svr_drives
8(
9 DriveLetter CHAR(1),
10 TotalSpace BIGINT,
11 FreeSpace BIGINT
12);
13
14-- Populate temp table with free space of all local drives
15INSERT INTO #svr_drives
16SELECT DISTINCT
17 SUBSTRING(volume_mount_point, 1, 1) AS DriveLetter,
18 total_bytes / 1024 / 1024 AS TotalSpace,
19 available_bytes / 1024 / 1024 AS FreeSpace
20FROM sys.master_files AS f
21 CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);
22--select * from #svr_drives
23
24--#endregion Local drives
25
26
27--#region create sysaltfiles
28-- DROP TABLE IF EXISTS #sysaltfiles;
29CREATE TABLE #sysaltfiles
30(
31 [dbname] [NVARCHAR](128) NULL,
32 [fileid] [SMALLINT] NULL,
33 [FileSize] [BIGINT] NULL,
34 [TotalSpace] [BIGINT] NULL,
35 [FreeSpace] [BIGINT] NULL,
36 [maxsize] [INT] NOT NULL,
37 [status] [INT] NULL,
38 [dbid] [SMALLINT] NULL,
39 [filename] [NVARCHAR](260) NOT NULL,
40 [Usage] [VARCHAR](4) NOT NULL,
41 [DriveLetter] [NVARCHAR](1) NULL,
42 [IsgrowthInMB] [INT] NOT NULL,
43 [growth] [INT] NOT NULL,
44 [growth_count] [INT],
45 [WillGrowth] [BIGINT] NULL,
46 [WillMissingSpaceDisk] INT,
47 [NearFileSizeLimit] INT,
48 [Message] NVARCHAR(MAX)
49);
50INSERT INTO #sysaltfiles
51(dbname,
52 fileid,
53 FileSize,
54 TotalSpace,
55 FreeSpace,
56 maxsize,
57 [status],
58 [dbid],
59 [filename],
60 Usage,
61 DriveLetter,
62 IsgrowthInMB,
63 growth
64)
65
66
67SELECT DB_NAME(dbid) dbname,
68 s.fileid,
69 (CONVERT(BIGINT, s.[size]) * 8) / 1024 AS FileSize,
70 TotalSpace,
71 FreeSpace AS FreeSpace,
72 maxsize / 128 AS maxsize,
73 [status],
74 [dbid],
75 [filename],
76 CASE STATUS&0x40
77 WHEN 0x40
78 THEN 'log'
79 ELSE 'data'
80 END AS 'Usage',
81 SUBSTRING([filename], 1, 1) AS DriveLetter,
82 CASE STATUS&0x100000
83 WHEN 0x100000
84 THEN 0
85 ELSE-1
86 END AS IsgrowthInMB,
87 growth
88FROM sys.sysaltfiles s
89 INNER JOIN #svr_drives d ON SUBSTRING([filename], 1, 1) = d.DriveLetter
90WHERE DATABASEPROPERTYEX(DB_NAME(dbid), 'Status') = 'ONLINE'
91 AND DATABASEPROPERTYEX(DB_NAME(dbid), 'Updateability') <> 'READ_ONLY'
92 AND DB_NAME(dbid) NOT IN('model'); -- we can add a filter.
93
94
95--#endregion create sysaltfiles
96
97
98--#region Message
99UPDATE #sysaltfiles
100 SET
101 growth_count = @growth_count,
102 [WillGrowth] = CASE
103 WHEN IsgrowthInMB = 0
104 THEN FileSize * (POWER(1 + ([growth] / 100.0), @growth_count) - 1)
105 ELSE(growth / 128) * @growth_count
106 END,
107 WillMissingSpaceDisk = CASE
108 WHEN CASE
109 WHEN IsgrowthInMB = 0
110 THEN FileSize * (POWER(1 + ([growth] / 100.0), @growth_count) - 1)
111 ELSE(growth / 128) * @growth_count
112 END > FreeSpace
113 THEN-1
114 ELSE 0
115 END,
116 NearFileSizeLimit = CASE
117 WHEN [maxsize] > 0
118 AND CASE
119 WHEN IsgrowthInMB = 0
120 THEN FileSize * (POWER(1 + ([growth] / 100.0), @growth_count))
121 ELSE FileSize + (growth / 128) * @growth_count
122 END > [maxsize]
123 THEN-1
124 ELSE 0
125 END,
126 [Message] = ''
127FROM #sysaltfiles;
128
129UPDATE #sysaltfiles
130SET
131 [Message] = [Message] + CASE
132 WHEN WillMissingSpaceDisk = -1
133 THEN 'The database [' + dbname + '] will missing space disk when the file [' + [filename] + '] will growth (' + CAST([WillGrowth] / 1024 AS NVARCHAR(20)) + ' GB) after ' + CAST(growth_count AS NVARCHAR(20)) + ' "growths" on disk (' + [DriveLetter] + ':) ' + CAST([FreeSpace] / 1024 AS NVARCHAR(20)) + ' GB Free of ' + CAST([TotalSpace] / 1024 AS NVARCHAR(20)) + ' GB.' + CHAR(13) + CHAR(10)
134 ELSE ''
135 END;
136
137UPDATE #sysaltfiles
138SET
139 [Message] = [Message] + CASE
140 WHEN NearFileSizeLimit = -1
141 THEN 'The database [' + dbname + '] is near of file size limit for the file [' + [filename] + ']. Using ' + CAST(FileSize AS NVARCHAR(20)) + ' GB of max ' +CAST(maxsize AS NVARCHAR(20)) + ' GB' + CHAR(13) + CHAR(10)
142 ELSE ''
143 END;
144
145DECLARE @Message NVARCHAR(MAX)= '';
146SELECT @Message = @Message + [Message]
147FROM #sysaltfiles
148WHERE [Message] <> ''
149ORDER BY dbname;
150-- SELECT * FROM #sysaltfiles;
151--#endregion Message
152PRINT @Message;
If you are using a job to monitoring, you can add this script to send an e-mail.
1--#region Send email
2IF(@Message <> '')
3BEGIN
4 DECLARE @body NVARCHAR(MAX)= '<p>The database server [servername] will have space disk problem. </p><p/><p/><p>[Message]</p><p/>This message was sent by [JobName] AT [servername]';
5 DECLARE @jobname SYSNAME, @jobid UNIQUEIDENTIFIER;
6 SELECT @jobname = b.name,
7 @jobid = b.job_id
8 FROM sys.dm_exec_sessions a,
9 msdb.dbo.sysjobs b
10 WHERE a.session_id = @@spid
11 AND (SUBSTRING(MASTER.dbo.FN_VARBINTOHEXSTR(CONVERT(VARBINARY(16), b.JOB_ID)), 1, 10)) = SUBSTRING(a.PROGRAM_NAME, 30, 10);
12 PRINT 'Job Name : ' + @jobname;
13 SET @body = REPLACE(@body, '[JobName]', @jobname);
14 SET @body = REPLACE(@body, '[servername]', @@SERVERNAME);
15 SET @body = REPLACE(@body, '[Message]', @Message);
16 EXEC msdb.dbo.sp_send_dbmail
17 @recipients = 'user@yourdomain.com',
18 @subject = 'Warning : Monitoring size file database',
19 @body = @body,
20 @body_format = 'HTML',
21 @from_address = 'Sql Server <sender@yourdomain.com>',
22 @reply_to = 'sender@yourdomain.com';
23
24END;
25--#endregion Send email
Comments