Pages

Monday, April 6, 2015

SQL Server Daily Operational Tasks (Part 2: Reporting Disk Space for SQL Server using T-SQL)

This is the second part of our series “SQL Server Daily Operational Tasks” and will focus in this post on the disk space part of our daily checklist.

Disks are one of the valuable resources in any database system, simply they are containing the databases and log files that hold our data and transactions and that are why we need to keep it available and have enough free space for normal database operations.

We will demonstrate here some alternatives for collecting and reporting the SQL Server server’s disks and its current free space.

In fact, the way to collect disk space information is differ from one SQL Server version to another, the reason simply is that the objects used for collecting these figures are not available on all SQL Server version.

Then if the targeted version is SQL Server 2008 R2 SP 1 and above then we can use the new DMF “Dynamic Management Function” sys.dm_os_volume_stats() that requires two parameters (the database_id and file_id), by querying this DMF beside the catalog view sys.master_files to get the required result.

In the below script we simply will go through the following steps:

1- Querying sys.dm_os_volume_stats() and catalog view sys.master_files and inserting the result set into a temp tables.
-- Step 1
SELECT DISTINCT SUBSTRING(volume_mount_point,1,1) AS [Drive]total_bytes/1048576 AS [Total_MB]available_bytes/1048576 AS [Available_MB],
(total_bytes/1048576)-(available_bytes/1048576) AS [Used_MB],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),available_bytes/1048576)/CONVERT(DECIMAL(18,2),total_bytes/1048576)*100 )AS [Available_Space_%],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),(total_bytes/1048576)-(available_bytes/1048576))/CONVERT(DECIMAL(18,2),total_bytes/1048576)*100 )AS [Used_Space_%]
INTO #SpaceUsed
FROM sys.master_files F cross apply sys.dm_os_volume_stats (F.database_id,F.file_idV
ORDER BY SUBSTRING(volume_mount_point,1,1)
GO

2- Getting the used space by databases for each drive into a common table expression “CTE”.
-- Step 2
WITH dbs_size (dbs_size_MB,DriveAS(
SELECT SUM((size * 8)/1024)AS [dbs_size_MB],SUBSTRING(physical_name,1,1) AS [Drive] FROM sys.master_files
GROUP BY SUBSTRING(physical_name,1,1))

3- Querying the temp table #SpaceUsed and the common table expression dbs_size and adding the current date for the final result set.
-- Step 3
SELECT S.DriveS.Total_MBS.Available_MBS.Used_MBS.[Available_Space_%],S.[Used_Space_%],D.DBs_Size_MB,
S.Used_MB-D.DBs_Size_MB AS [Other_Files_Size_MB],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),D.DBs_Size_MB)/CONVERT(DECIMAL(18,2),S.Used_MB)*100) AS [DB_Size_%],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),(S.Used_MB-D.DBs_Size_MB))/CONVERT(DECIMAL(18,2),S.Used_MB)*100) AS [Other_Files_Size_%],
CONVERT(DATE,GETDATE()) AS [date]
FROM #SpaceUsed S join dbs_size D ON S.Drive=D.Drive
GO

4- Dropping the temp table #SpaceUsed.
-- Step 4
DROP TABLE #SpaceUsed
GO

The complete script:
--  SQL Server version: SQL Server 2008 R2 SP1 and above
USE MASTER
GO
SELECT DISTINCT SUBSTRING(volume_mount_point,1,1) AS [Drive]total_bytes/1048576 AS [Total_MB]available_bytes/1048576 AS [Available_MB],
(total_bytes/1048576)-(available_bytes/1048576) AS [Used_MB],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),available_bytes/1048576)/CONVERT(DECIMAL(18,2),total_bytes/1048576)*100 )AS [Available_Space_%],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),(total_bytes/1048576)-(available_bytes/1048576))/CONVERT(DECIMAL(18,2),total_bytes/1048576)*100 )AS [Used_Space_%]
INTO #SpaceUsed
FROM sys.master_files F cross apply sys.dm_os_volume_stats (F.database_id,F.file_idV
ORDER BY SUBSTRING(volume_mount_point,1,1)
GO
WITH dbs_size (dbs_size_MB,DriveAS(
SELECT SUM((size * 8)/1024)AS [dbs_size_MB],SUBSTRING(physical_name,1,1) AS [Drive] 
FROM sys.master_files GROUP BY SUBSTRING(physical_name,1,1))
SELECT S.DriveS.Total_MBS.Available_MBS.Used_MBS.[Available_Space_%],S.[Used_Space_%],D.DBs_Size_MB,
S.Used_MB-D.DBs_Size_MB AS [Other_Files_Size_MB],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),D.DBs_Size_MB)/CONVERT(DECIMAL(18,2),S.Used_MB)*100) AS [DB_Size_%],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),(S.Used_MB-D.DBs_Size_MB))/CONVERT(DECIMAL(18,2),S.Used_MB)*100) AS [Other_Files_Size_%],
CONVERT(DATE,GETDATE()) AS [date]
FROM #SpaceUsed S join dbs_size D ON S.Drive=D.Drive
GO
DROP TABLE #SpaceUsed
GO

The result set should be as below:





As we can see, the result set is really amazing, we can check the total drive space, the used amount, the free space and also the space used by databases and the space used by other files “which is somehow important to know if there are some non-database files that consume a lot of space in database drives”, all these values are presented by MB and also by percentage.

Unfortunately there are two main problems in using this DMF, the first one is as mentioned above, the sys.dm_os_volume_stats is not available for SQL Server versions prior to SQL Server 2008 R2 SP 1 and also it does not include the disks without databases, for example the local disks in the clustered SQL servers and any drive that not being used as a database drive.

The alternative in these two situations is to use the undocumented extended stored procedure sys.xp_fixeddrives to collect drives information, but the bad luck here is the limited information this SP deliver as it only shows the drive name and the free space in MB.

In case of using this extended stored procedure then the collecting steps should be as below:


1- Creating a temp table to insert the SP data in.
-- Step 1
CREATE TABLE #AllDrives (DriveName CHAR(3),FreeSpace_MB INT,[date] DATE)
GO

2- Inserting the SP data into the temp table and updating the temp table with the current date.
-- Step 2
INSERT INTO #AllDrives (DriveName,FreeSpace)
EXEC sys.xp_fixeddrives
UPDATE #AllDrives SET [date]=CONVERT(DATE,GETDATE())
GO

3- Selecting the drives data and then dropping the temp table.
-- Step 3
SELECT FROM #AllDrives
DROP TABLE #AllDrives
GO

The complete script:
-- SQL Server version: SQL Server 2005
USE MASTER
GO
CREATE TABLE #AllDrives (DriveName CHAR(3),FreeSpace_MB INT,[date] SMALLDATETIME)
GO
INSERT INTO #AllDrives (DriveName,FreeSpace)
EXEC sys.xp_fixeddrives
UPDATE #AllDrives SET [date]=CONVERT(SMALLDATETIME,GETDATE())
GO
SELECT FROM #AllDrives
DROP TABLE #AllDrives
GO

-- SQL Server version: SQL Server 2008 & 2008 R2 RTM
USE MASTER
GO
CREATE TABLE #AllDrives (DriveName CHAR(3),FreeSpace_MB INT,[date] DATE)
GO
INSERT INTO #AllDrives (DriveName,FreeSpace)
EXEC sys.xp_fixeddrives
UPDATE #AllDrives SET [date]=CONVERT(DATE,GETDATE())
GO
SELECT FROM #AllDrives
DROP TABLE #AllDrives
GO

The result set should be as below:





Next Step:

There is another way to collect the drive space information out of SQL Server which is using the powerful Microsoft shell scripting tool “PowerShell”, how can we do that??, this will be the next post in our series. 

No comments:

Post a Comment