Drive Usage

To see drive usage of your database server without needing to RDP onto the server. 

SELECT DISTINCT

    vs.volume_mount_point,

    CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS DECIMAL(18,2)) AS TotalGB,

    CAST(vs.available_bytes / 1024.0 / 1024 / 1024 AS DECIMAL(18,2)) AS FreeGB,

    CAST((vs.total_bytes - vs.available_bytes) / 1024.0 / 1024 / 1024 AS DECIMAL(18,2)) AS UsedGB

FROM sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs

ORDER BY vs.volume_mount_point;

Diagnostic Scripts

SELECT wait_type,

       waiting_tasks_count,

       wait_time_ms

  FROM sys.dm_os_wait_stats

 WHERE wait_type like 'PAGEIOLATCH%'

 ORDER BY wait_type 

SELECT database_id,

       file_id,

       io_stall,

       io_pending_ms_ticks,

       scheduler_address

  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS t1,

       sys.dm_io_pending_io_requests AS t2

 WHERE t1.file_handle = t2.io_handle 

SELECT (SELECT SUM(CAST(size AS BIGINT)*8/1024/1024  FROM sys.master_files  WHERE type=1) AS logGB,

       (SELECT SUM(CAST(size AS BIGINT)*8/1024/1024  FROM sys.master_files  WHERE type=0) AS rowGB

SSMS Activity Monitor

TODO

Tables & Views

• sys.dm_io_pending_io_requests

• sys.dm_io_virtual_file_stats 

• sys.dm_os_wait_stats

Bibliography