Monitor disk space

/** This script checks the current space of the disks and if the available space is lower that the threshold (GB in this case) sends out email**/


use MyAdminDB
GO

declare @from varchar(100),
@to varchar(200),
@subject varchar(100),
@threshold int -- number of MB under which to launch an alert
　
　
　
SET NOCOUNT ON

DECLARE @list nvarchar(2000) = '';

WITH core AS (
　
　
SELECT DISTINCT

　
　
s.volume_mount_point [Drive],
　
　
CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs]
　
　
FROM
　
　
sys.master_files f
　
　
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
　
　
)
　
　
　
　
　
　
　
　
　
SELECT @list = @list + ' ' + Drive + ', current space available =' + cast(cast(round([AvailableMBs]*1.000/1024.000,2) as numeric(36,2)) as varchar) + 'GB'
　
　
FROM core
　
　
WHERE AvailableMBs < @threshold
　
　
IF LEN(@list) > 3 BEGIN
　
　
DECLARE @msg varchar(500) = 'Disk Space les than 8GB Notification. The following drives are currently reporting less than '
　
　
+ CAST(@threshold/1024 as varchar(12)) + ' GB free: ' + @list
　
　
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myprofile@mydomain.com',
@recipients = @to,
@subject = @subject,
@body = @msg
END

RETURN 0
　
　
　
　
　
GO
　
　
　
　
　
　
　
　
　

Script: Log file size to a user table

DECLARE @rowcnt INT
DECLARE @iterator INT
DECLARE @dbname VARCHAR(200)
DECLARE @exec_sql VARCHAR(500)
SET @rowcnt = 0
SET @iterator = 1
CREATE TABLE #db_file_info ( [Database_Name] SYSNAME NOT NULL, [File_ID] SMALLINT NOT NULL, [File_Type] VARCHAR(10) NOT NULL, [File_Name] SYSNAME NOT NULL, [File_Path] VARCHAR(500) NOT NULL, [File_Size_in_MB] INT NOT NULL, [Space_Used_in_MB] INT NOT NULL, [Space_Left_in_MB] INT NOT NULL, [Time_collected] smalldatetime )
CREATE TABLE #db ( dbid INT, name VARCHAR(200))
INSERT INTO #db
SELECT database_id, name FROM sys.databases
WHERE --database_id <> 11 AND [state] <> 6
SET @rowcnt = @@ROWCOUNT
WHILE @iterator <= @rowcnt
BEGIN
SELECT @dbname = '['+ name + ']' FROM #db
SET @exec_sql = ' USE ' + @dbname + ';
Insert into #DB_FILE_INFO Select db_name(),fileid,case when groupid = 0 then ''log file'' else ''data file'' end, name,filename, [file_size] = convert(int,round ((sysfiles.size*1.000)/128.000,0)), [space_used] = convert(int,round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,0)), [space_left] = convert(int,round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,0)), getdate() from dbo.sysfiles; '
EXEC( @exec_sql)
SET @iterator = @iterator + 1
END
INSERT INTO SQLAdmin_Monitor_db_file_info
SELECT * FROM #db_file_info
DROP TABLE #db
DROP TABLE #db_file_info

Script: To find the database file where the file space is used more than 94 % of the current file size

--The historic data is logged in a table in every 4 hours and the following query in run in the table to find the current grown rate of the databse file

SELECT Database_Name,Space_Used_in_MB,File_Size_in_MB,(cast(Space_Used_in_MB as float)/cast(File_Size_in_MB as float)) * 100 as newval
FROM Monitor_db..SQLAdmin_Monitor_db_file_info
WHERE FILE_ID = 1 AND (cast(Space_Used_in_MB as float)/cast(File_Size_in_MB as float)) * 100 >= 95 AND Time_collected IN (SELECT MAX(time_collected) FROM hCFS_SQLMonitor..SQLAdmin_Monitor_db_file_info )
ORDER BY File_Size_in_MB