June 6, 2012 at 5:39 am
Hello,
I was adding a SQL Agent Alert to check the data file size of a user database and e-mail me if it increased beyond a certain threshold. While setting up this alert, I checked the size of the data file using SSMS (right-clicked the user database and selected Tasks / Shrink / Files) and deliberately set the alert threshold to a value lower than this to test that the alert worked.
The alert certainly did its job immediately, however the current value reported in the e-mail was significantly higher than what I was expecting. When I queried the [master].[sys].[master_files] and .[sys].[database_files] views, I could see where the e-mail was presumably getting its values from.
The file sizes in those views are much higher than the actual size of the .mdf, .ndf files on the server, the initial values in the user database properties and those displayed when right-clicking the database and selecting Tasks / Shrink / Files. To me it seems clear that the sizes in the view are incorrect/out of date, but I'm not sure how these get updated and/or whether there is a command/process to update them. I would like the alert to e-mail a realistic value and run when the data file size genuinely goes above the threshold set. I have spent some time researching how these get updated, but I get the impression it's supposed to happen automatically?
Any ideas?
Regards,
Chris Stride
June 6, 2012 at 5:55 am
The size columns in the two views are in pages, so I assume you are multiplying them by 8, to get the size in KB then dividing by 1024 to get the size in MB or dividing by 1048576 to get the size in GB?
The update to the views is instant, I just increased a log file from 25MB to 100MB and the views updated instantly.
June 6, 2012 at 6:21 am
I'm embarrassed to say a total school-boy error on my part! Thanks Anthony. Haven't really looked at those tables before, but should have known they were in pages before posting.
Because there is now a fair amount of free space in the data files for this database, I really want the alert to fire if the space used goes over a certain threshold. Can this be achieved in the GUI alert set up or do I have to create a custom procedure to do this using DBCC SHOWFILESTATS?
June 6, 2012 at 6:26 am
I use this to insert into a table every day the current used, free and total space. You could refactor it to use sp_send_dbmail and build up a message and email it. Runs via a job
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'a.FILEID, ' + CHAR(13) + CHAR(10) +
'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +
'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
)
AS NVARCHAR(MAX)
),
' ',CHAR(13) + CHAR(10)
)
--SELECT @SQL
EXECUTE sp_executesql @SQL
You could also take a look at policy based management to do this aswell
June 6, 2012 at 6:47 am
Thanks Anthony. Appreciate your help.
June 6, 2012 at 7:10 am
Refactored it, but a bit messy on the email, need to format it how you want it to by building strings into variables, then passing that into sp_send_dbmail, but works, used a threshold of 90% used to send the mail.
CREATE TABLE ##temp (DatabaseName SYSNAME, FileID INT, FileSizeMB DECIMAL(12,2), SpaceUsedMB DECIMAL(12,2), FreeSpaceMB DECIMAL(12,2), LogicalName SYSNAME)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'INSERT INTO ##temp (DatabaseName, FileID, FileSizeMB, SpaceUsedMB, FreeSpaceMB, LogicalName)'+ CHAR(13) + CHAR(10)+
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'a.FILEID, ' + CHAR(13) + CHAR(10) +
'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'a.NAME' + CHAR(13) + CHAR(10) +
'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
)
AS NVARCHAR(MAX)
),
' ',CHAR(13) + CHAR(10)
)
--SELECT @SQL
EXECUTE sp_executesql @SQL
exec msdb.dbo.sp_send_dbmail
@profile_name = '',
@recipients = '',
@subject = 'Used Space Threshold Breach',
@query = 'SELECT DatabaseName, FileID, LogicalName, FileSizeMB, SpaceUsedMB, FreeSpaceMB, ((SpaceUsedMB/FileSizeMB)*100) AS PercentageUsedSpace FROM ##temp WHERE ((SpaceUsedMB/FileSizeMB)*100) > 90'
DROP TABLE ##temp
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply