One of the most common DBA tasks is to monitor disk usage.
You don't want your main user databases, or even worse your whole instance being down because of a full disk.
It is your duty to avoid that, and even this is something basic, a lot of DBAs fail on this.
An easy way to determine disk usage, is using the following query, since it gave me info about each datafile in each drive, and its free space:
You can then use it in a job or custom report so you can receive an alert when usage is low.
Source:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-volume-stats-transact-sql
You don't want your main user databases, or even worse your whole instance being down because of a full disk.
It is your duty to avoid that, and even this is something basic, a lot of DBAs fail on this.
An easy way to determine disk usage, is using the following query, since it gave me info about each datafile in each drive, and its free space:
SELECT DB_NAME(VStats.database_id) DBName, mf.physical_name PhysicalFileLocation, VStats.volume_mount_point AS Drive, CONVERT(int,VStats.total_bytes/1048576.0) AS TotalSpaceInMB, CONVERT(int,VStats.available_bytes/1048576.0) AS FreeSpaceInMB, round((convert(float,VStats.available_bytes)/ convert(float,VStats.total_bytes))*100,2) as PercentFreeSpace FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) VStats ORDER BY PercentFreeSpace
You can then use it in a job or custom report so you can receive an alert when usage is low.
Source:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-volume-stats-transact-sql
Comments
Post a Comment