Monitoring database size files before blow up
Rédigé par Sozezzo - - Aucun commentaireyou 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.
SELECT d.name as database_name, mf.name as file_name, mf.type_desc as file_type, mf.growth as current_percent_growth FROM sys.master_files mf (NOLOCK) JOIN sys.databases d (NOLOCK) on mf.database_id=d.database_id WHERE is_percent_growth=1
Script to monitoring size files.
DECLARE @growth_count INT= 3; -- Who many time the database can growth ?
SET NOCOUNT ON;
--#region Local drives
-- DROP TABLE IF EXISTS #svr_drives;
CREATE TABLE #svr_drives
( DriveLetter CHAR(1), TotalSpace BIGINT, FreeSpace BIGINT
);
-- Populate temp table with free space of all local drives
INSERT INTO #svr_drives
SELECT DISTINCT SUBSTRING(volume_mount_point, 1, 1) AS DriveLetter, total_bytes / 1024 / 1024 AS TotalSpace, available_bytes / 1024 / 1024 AS FreeSpace
FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);
--select * from #svr_drives
--#endregion Local drives
--#region create sysaltfiles
-- DROP TABLE IF EXISTS #sysaltfiles;
CREATE TABLE #sysaltfiles
( [dbname] [NVARCHAR](128) NULL, [fileid] [SMALLINT] NULL, [FileSize] [BIGINT] NULL, [TotalSpace] [BIGINT] NULL, [FreeSpace] [BIGINT] NULL, [maxsize] [INT] NOT NULL, [status] [INT] NULL, [dbid] [SMALLINT] NULL, [filename] [NVARCHAR](260) NOT NULL, [Usage] [VARCHAR](4) NOT NULL, [DriveLetter] [NVARCHAR](1) NULL, [IsgrowthInMB] [INT] NOT NULL, [growth] [INT] NOT NULL, [growth_count] [INT], [WillGrowth] [BIGINT] NULL, [WillMissingSpaceDisk] INT, [NearFileSizeLimit] INT, [Message] NVARCHAR(MAX)
);
INSERT INTO #sysaltfiles
(dbname, fileid, FileSize, TotalSpace, FreeSpace, maxsize, [status], [dbid], [filename], Usage, DriveLetter, IsgrowthInMB, growth
)
SELECT DB_NAME(dbid) dbname, s.fileid, (CONVERT(BIGINT, s.[size]) * 8) / 1024 AS FileSize, TotalSpace, FreeSpace AS FreeSpace, maxsize / 128 AS maxsize, [status], [dbid], [filename], CASE STATUS&0x40 WHEN 0x40 THEN 'log' ELSE 'data' END AS 'Usage', SUBSTRING([filename], 1, 1) AS DriveLetter, CASE STATUS&0x100000 WHEN 0x100000 THEN 0 ELSE-1 END AS IsgrowthInMB, growth
FROM sys.sysaltfiles s INNER JOIN #svr_drives d ON SUBSTRING([filename], 1, 1) = d.DriveLetter
WHERE DATABASEPROPERTYEX(DB_NAME(dbid), 'Status') = 'ONLINE' AND DATABASEPROPERTYEX(DB_NAME(dbid), 'Updateability') <> 'READ_ONLY' AND DB_NAME(dbid) NOT IN('model'); -- we can add a filter.
--#endregion create sysaltfiles
--#region Message
UPDATE #sysaltfiles SET growth_count = @growth_count, [WillGrowth] = CASE WHEN IsgrowthInMB = 0 THEN FileSize * (POWER(1 + ([growth] / 100.0), @growth_count) - 1) ELSE(growth / 128) * @growth_count END, WillMissingSpaceDisk = CASE WHEN CASE WHEN IsgrowthInMB = 0 THEN FileSize * (POWER(1 + ([growth] / 100.0), @growth_count) - 1) ELSE(growth / 128) * @growth_count END > FreeSpace THEN-1 ELSE 0 END, NearFileSizeLimit = CASE WHEN [maxsize] > 0 AND CASE WHEN IsgrowthInMB = 0 THEN FileSize * (POWER(1 + ([growth] / 100.0), @growth_count)) ELSE FileSize + (growth / 128) * @growth_count END > [maxsize] THEN-1 ELSE 0 END, [Message] = ''
FROM #sysaltfiles;
UPDATE #sysaltfiles SET [Message] = [Message] + CASE WHEN WillMissingSpaceDisk = -1 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) ELSE '' END;
UPDATE #sysaltfiles SET [Message] = [Message] + CASE WHEN NearFileSizeLimit = -1 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) ELSE '' END;
DECLARE @Message NVARCHAR(MAX)= '';
SELECT @Message = @Message + [Message]
FROM #sysaltfiles
WHERE [Message] <> ''
ORDER BY dbname;
-- SELECT * FROM #sysaltfiles;
--#endregion Message
PRINT @Message;
If you are using a job to monitoring, you can add this script to send an e-mail.
--#region Send email IF(@Message <> '') BEGIN 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]'; DECLARE @jobname SYSNAME, @jobid UNIQUEIDENTIFIER; SELECT @jobname = b.name, @jobid = b.job_id FROM sys.dm_exec_sessions a, msdb.dbo.sysjobs b WHERE a.session_id = @@spid AND (SUBSTRING(MASTER.dbo.FN_VARBINTOHEXSTR(CONVERT(VARBINARY(16), b.JOB_ID)), 1, 10)) = SUBSTRING(a.PROGRAM_NAME, 30, 10); PRINT 'Job Name : ' + @jobname; SET @body = REPLACE(@body, '[JobName]', @jobname); SET @body = REPLACE(@body, '[servername]', @@SERVERNAME); SET @body = REPLACE(@body, '[Message]', @Message); EXEC msdb.dbo.sp_send_dbmail @recipients = 'user@yourdomain.com', @subject = 'Warning : Monitoring size file database', @body = @body, @body_format = 'HTML', @from_address = 'Sql Server <sender@yourdomain.com>', @reply_to = 'sender@yourdomain.com'; END; --#endregion Send email