Description
The stock alert in DPA will take the spaceused / maxsize, if maxsize is not set however, the alert is defaulted to 100% free. This alert takes space used / size (rather than maxsize), it also lists out the full location of the files and does not roll up the files for the database to a total sum. Some DBA's may find it useful to know what the current size of the file is set to and what percent of it is free before it needs to grow. This is a cursor that creates a temp and drops a temp table after querying the sys.database_files table for every DB found, then returns spaceused / size turned into a % and deducted from 100 to show total space free on the DB file for size it's currently set for.
Alert Definition
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:
- Alert Name: DB File Freespace
- Execution Interval: >= 1 Day
- Notification Text: The following DB Files have reached the notification threshold of space percentage free. Please check on the files and space used in the DB.
- Execute Against: Monitored DB
- Units: Percent Free
- Thresholds
- High: Min 0, Max 5
- Medium: Min 5, Max 10
SQL Statement
-- CREATE TEMP TABLE FOR HOLDING DATA
CREATE TABLE #DPA_FREESPACE (
DB_FILES VARCHAR(300)
, FILE_LOC VARCHAR(300)
, PERCENTFREE DECIMAL(9, 2)
)
-- DECLARING AND SETTING VARIABLES FOR CURSOR
DECLARE @STRSQL NVARCHAR(2000)
DECLARE @DBNAME VARCHAR(MAX)
DECLARE @GETDBNAME CURSOR SET @GETDBNAME = CURSOR
FOR
SELECT NAME
FROM SYS.DATABASES
WHERE STATE = 0
OPEN @GETDBNAME
FETCH NEXT
FROM @GETDBNAME
INTO @DBNAME
@FETCH_STATUS = 0
BEGIN
PRINT @DBNAME
-- CURSOR THAT HITS THE SYSFILES AND DATABASE_FILES TO RETRIEVE FILE AND SIZE INFORMATION
SELECT @STRSQL = '
USE ' + QUOTENAME(@DBNAME) + ';
INSERT INTO #DPA_FREESPACE
SELECT
NAME
, PHYSICAL_NAME
, (100 - (((CAST (FILEPROPERTY(NAME,''SPACEUSED'') AS FLOAT))/ (CAST(SIZE AS FLOAT) )) *100)) AS PERCENTFREE
FROM SYS.DATABASE_FILES
WHERE TYPE=0 AND STATE=0
'
EXEC SP_EXECUTESQL @STRSQL
FETCH NEXT
FROM @GETDBNAME
INTO @DBNAME
END
CLOSE @GETDBNAME
DEALLOCATE @GETDBNAME
-- END RESULT SELECTING FROM TABLE TO BE DISPLAYED IN DPA ALERT
SELECT FILE_LOC
, PERCENTFREE
FROM #DPA_FREESPACE
-- DROPPING TEMP TABLE
DROP TABLE #DPA_FREESPACE
