Disk Space Usage For Analysis

The following implementation helped me in analyzing the disk space usage. 

There are many ways to get the available disk space in the server and using SCOM we can get the alerts whenever the disk space reaches below the threshold levels. This implementation fetches the available space information and stores it in a table on a daily basis which helps in comparing the data with the previous days and also over a period of time to understand how much disk space is being utilized. 

Undoubtedly, when we have the data, we will have lot of scope in analyzing it. 

As the famous quote goes, “If you torture the data long enough, it will confess”. 😊

Our requirement is to fetch the information in the format below and using xp_fixeddrives, we get the following results:

Code:
USE TestDB1
GO

— Create a table to store the information
CREATE TABLE [dbo].[tbDriveSpaceInfo](
            [vcDriveName] VARCHAR(2) NULL,
            [iMBFree] INT NULL,
            [dtUpdTime] DATETIME NULL
) ON [PRIMARY]
GO

— Adding a default value to the Updated Date Time. It records when the execution took place.

ALTER TABLE [dbo].[tbDriveSpaceInfo] ADD  DEFAULT (GETDATE()) FOR [dtUpdTime]
GO

— Inserting values into the table
INSERT INTO [tbDriveSpaceInfo](vcDriveName,iMBFree)
EXEC xp_fixeddrives

SELECT * FROM [tbDriveSpaceInfo]

— Retrieving the stored information in a single row / readable format.
SELECT
MAX(CASE WHEN vcDriveName = ‘C’ THEN iMBFree END) AS ‘Drive-C’,
MAX(CASE WHEN vcDriveName = ‘D’ THEN iMBFree END) AS ‘Drive-D’,
–CASE WHEN vcDriveName = ‘E’ THEN iMBFree END AS ‘Drive-E’,
–CASE WHEN vcDriveName = ‘F’ THEN iMBFree END AS ‘Drive-F’,
MAX(CASE WHEN vcDriveName = ‘G’ THEN iMBFree END) AS ‘Drive-G’,
MAX(CASE WHEN vcDriveName = ‘H’ THEN iMBFree END) AS ‘Drive-H’,
MAX(CASE WHEN vcDriveName = ‘I’ THEN iMBFree END) AS ‘Drive-I’,
MAX(CASE WHEN vcDriveName = ‘J’ THEN iMBFree END) AS ‘Drive-J’,
MAX(CASE WHEN vcDriveName = ‘K’ THEN iMBFree END) AS ‘Drive-K’,
MAX(CASE WHEN vcDriveName = ‘L’ THEN iMBFree END) AS ‘Drive-L’,
MAX(CASE WHEN vcDriveName = ‘M’ THEN iMBFree END) AS ‘Drive-M’,
MAX(CASE WHEN vcDriveName = ‘Q’ THEN iMBFree END) AS ‘Drive-Q’,
MAX(CASE WHEN vcDriveName = ‘S’ THEN iMBFree END) AS ‘Drive-S’,
MAX(CASE WHEN vcDriveName = ‘W’ THEN iMBFree END) AS ‘Drive-W’,
dtUpdTime
 FROM tbDriveSpaceInfo
 GROUP BY dtUpdTime

As you see, this is implemented without leaving the SQL Server Management Studio and without using any third-party tool.