database performance analyzer

TSQL to setup custom drive space alerts on Solarwinds DPA (Database performance Analyzer)

— as shown above choose the custom SQL Alert – Multiple Numeric return while creating Alerts and use the following script to configure the custom alerts.

SET NOCOUNT ON

DECLARE @DBname nvarchar(128),

    @CMD1     nvarchar(200),

    @Drive    nvarchar(1),

    @MB_Free  int

IF OBJECT_ID(‘tempdb..#rdFreeSpace’) IS NOT NULL

DROP TABLE #rdFreeSpace

IF OBJECT_ID(‘tempdb..#rdDataDrives’) IS NOT NULL

DROP TABLE #rdDataDrives

IF OBJECT_ID(‘tempdb..#rdTotalSpace’) IS NOT NULL

DROP TABLE #rdTotalSpace

CREATE TABLE #rdDataDrives (DB nvarchar(128), Drive nvarchar(1));

CREATE TABLE #rdFreeSpace (Drive char(1), MB_Free int);

CREATE TABLE #rdTotalSpace (Drive char(1), GB_Total int);

INSERT INTO #rdFreeSpace EXEC xp_fixeddrives;

INSERT INTO #rdDataDrives

EXEC sp_MSforeachdb

@command1=”use [?]

SELECT DB_NAME(), UPPER(LEFT(filename,1)) as DRIVE

FROM dbo.sysfiles”

— always check the C: drive

INSERT INTO #rdDataDrives (Drive) VALUES (‘C’)

insert into #rdTotalSpace
SELECT distinct(left(volume_mount_point, 1)),
total_bytes/1048576/1024 as Size_in_GB
FROM sys.master_files AS f CROSS APPLY
sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576,
available_bytes/1048576 order by 1

SELECT DISTINCT dd.Drive, fs.MB_Free/1024 FreeSpace_GB
FROM #rdDataDrives dd join #rdFreeSpace fs
on fs.Drive = dd.drive
join #rdTotalSpace ft on ft.Drive = dd.drive
where (fs.MB_Free < 10240 and ft.GB_Total < 500) or (fs.MB_Free < 102400 and ft.GB_Total > 500)