Hello there, this post can explain various ways to know the SQL Server Space Usage information. Here are the different ways available:
XP_FIXEDDRIVES
SP_SPACEUSED
SP_HELPDB
DBCC SQLPERF (LOGSPACE)
sys.dm_os_volume_stats
Database and file wise Space usage
Used Space and Free Space For a given Database
Table wise space usage in a given database
Using SSMS Standard Reports
XP_CMDSHELL
PowerShell Script
XP_FIXEDDRIVES:
This is simple and straight method when we think of SQL Server Space Usage. It gets the drive free space information.
But we can’t know the Total Drive space, let’s say D drive is having 100 GB in which 60 GB is used and 40 GB is free then this command can only get us the free space information 40 GB.
This can quickly get the Database and table space information. For database it captures total size, un-allocated space, data and index space details. For a given table it captures the total number of rows, reserved space, Data, Index and unused space information.
/*********************************************/
/**** Database & Table Space Usage ***********/
/**** Tested: 2008, 2008 R2 ******************/
/**** 2012, 2014, 2016 ***************/
/*********************************************/
-- To capture the Database Space information
EXEC SP_SPACEUSED;
-- To capture the given table Space information
SP_SPACEUSED '[SCHEMA].[TABLE]';
SP_HELPDB:
It’s one of the best handy commands which can quickly captures all databases space and other information from the given SQL Server instance. It gets the database name, total size, owner, current status and compatibility level etc. Also this system stored procedure can be used for a specific database where it can get more useful information about the given database including the physical file path and file size.
/*********************************************/
/**** Database space and other information ***/
/**** Tested: 2008, 2008 R2 ******************/
/**** 2012, 2014, 2016 ***************/
/*********************************************/
--Capture All databases information from the SQL instance
EXEC SP_HELPDB;
--Capture the given database information
EXEC SP_HELPDB 'Master';
DBCC SQLPERF (LOGSPACE):
This is the quickest way to check the log usage for all databases in the instance. It captures log size, space used percentage and status.
/**************************************************/
/**** Log Space Usage Report for All databases*****/
/**** Tested: 2008, 2008 R2 ***********************/
/**** 2012, 2014, 2016 ********************/
/**************************************************/
DBCC SQLPERF(LOGSPACE);
sys.dm_os_volume_stats:
When we use XP_FIXEDDRIVES we can’t capture only the free space information, to get the total drive space information we can get it using “sys.dm_os_volume_stats”. Disk space details retrieved when only the drives containing any Database files from this instance. Let’s say the OS is attached the drives C, D, E, H and T. And no data or log file are located on C and H drive then the below query couldn’t get the C & H drive information rather it can capture the total and free space information from remaining drives.
/*********************************************/
/**** Drive Total and Free space info ********/
/**** Tested: 2008, 2008 R2 ******************/
/**** 2012, 2014, 2016 ***************/
/*********************************************/
SELECT [Total(GB)],
[FreeSpace(GB)],
CAST(([FreeSpace(GB)]*100)/[Total(GB)] AS DECIMAL(15,2)) AS [%FreeSpace]
FROM
(SELECT DISTINCT
OVS.volume_mount_point [Drive],
CAST(OVS.total_bytes / 1073741824 AS DECIMAL(15,2)) [Total(GB)],
CAST(OVS.available_bytes / 1073741824 AS DECIMAL(15,2)) [FreeSpace(GB)]
FROM sys.master_files SMF
CROSS APPLY sys.dm_os_volume_stats(SMF.database_id, SMF.[file_id]) OVS
) AS Result;
Database and File wise Space Usage:
Here is the query to get database and the individual file (Data and Log) wise space usage information.
/*********************************************/
/**** Data and Log file wise Disk Usage ******/
/**** Tested: 2008, 2008 R2 ******************/
/**** 2012, 2014, 2016 ***************/
/*********************************************/
-- Data and Log file wise space usage for all databases
SELECT D.name AS 'DB_Name',
CASE WHEN MF.[Type] = 0 THEN 'DATA' ELSE 'LOG' END AS 'File_Type',
MF.Name as 'File_Name',
CAST((SUM(size)*8)/1024.00 AS DECIMAL(15,2)) AS [Disk_Space(MB)],
CAST((SUM(size)*8)/(1024.00 * 1024.00) AS DECIMAL(15,2)) AS [Disk_Space(GB)]
FROM sys.databases D
JOIN sys.master_files MF
ON D.database_id=MF.database_id
GROUP BY D.name,MF.Name,MF.Type
ORDER BY D.name,MF.Name,MF.Type;
-- Database wise total disk usage in a given instance
SELECT D.name AS 'DB_Name',
CAST((SUM(size)*8)/1024.00 AS DECIMAL(15,2)) AS [Total_Disk_Space(MB)],
CAST((SUM(size)*8)/(1024.00 * 1024.00) AS DECIMAL(15,2)) AS [Total_Disk_Space(GB)]
FROM sys.databases D
JOIN sys.master_files MF
ON D.database_id=MF.database_id
GROUP BY D.name
ORDER BY D.name;
Used Space and Free Space For a given Database:
The most common situation is to identifying the available free space for a given single database. The below query can help you in identifying the percentage free space in a given database.
/**************************************************/
/**** For a given database used and free space ****/
/**** Tested: 2008, 2008 R2 ***********************/
/**** 2012, 2014, 2016 ********************/
/**************************************************/
SELECT
[Database],
[File_Name],
[FileGroup_Name],
File_Path,
[Total_Space(MB)],
[Available_Space(MB)],
CAST([Total_Space(MB)]/1024 AS DECIMAL(15,2)) AS [Total_Space(GB)],
CAST([Available_Space(MB)]/1024 AS DECIMAL(15,2)) AS [Available_Space(GB)],
CAST(([Available_Space(MB)]*100)/[Total_Space(MB)] AS DECIMAL(15,2)) AS '%_Free'
FROM
(SELECT
DB_NAME() AS 'Database',
df.name AS [File_Name],
ds.name AS [FileGroup_Name],
df.physical_name AS [File_Path],
CAST((df.size/128.0) AS DECIMAL(15,2)) AS [Total_Space(MB)],
CAST(df.size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) AS [Available_Space(MB)]
FROM sys.database_files AS df WITH (NOLOCK)
LEFT OUTER JOIN sys.data_spaces AS ds WITH (NOLOCK) ON df.data_space_id = ds.data_space_id) AS Result;
Table wise Disk Space Usage:
The below query gets you the “Table Wise disk space information” which can be helpful to identify the critical tables in a given database , here you can find more information.
/**************************************************/
/**** Table wise Disk Space Report ****************/
/**** Tested: 2008, 2008 R2 ***********************/
/**** 2012, 2014, 2016 ********************/
/**************************************************/
SET NOCOUNT ON;
BEGIN TRY
--Create a temparory table
CREATE TABLE #Tab (
[Name] NVARCHAR(128),
[Rows] CHAR(11),
[Reserved] VARCHAR(18),
[Data] VARCHAR(18),
[Index_size] VARCHAR(18),
[Unused] VARCHAR(18));
--Capture all tables data allocation information
INSERT #Tab
EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' ;
--Alter Rows column datatype to BIGINT to get the result in sorted order
ALTER TABLE #Tab ALTER COLUMN [ROWS] BIGINT ;
-- Get the final result: Remove KB and convert it into MB
SELECT Name,
[Rows],
CAST(LTRIM(RTRIM(REPLACE(Reserved,'KB',''))) AS BIGINT)/1024.0 AS 'Reserved MB',
CAST(LTRIM(RTRIM(REPLACE(Data,'KB',''))) AS BIGINT)/1024.0 AS 'Data MB',
CAST(LTRIM(RTRIM(REPLACE(Index_Size,'KB',''))) AS BIGINT)/1024.0 AS 'Index_Size MB',
CAST(LTRIM(RTRIM(REPLACE(Unused,'KB',''))) AS BIGINT)/1024.0 AS 'Unused MB'
FROM #Tab
ORDER BY [rows] DESC;
END TRY
BEGIN CATCH
DROP TABLE #Tab;
END CATCH
-- Drop the temparory table
DROP TABLE #Tab;
Using SSMS Standard Reports:
We can also check the disk space usage report from SSMS. Connect to the SQL Server instance from SSMS Right click on Database Reports Standard Reports Top 4 options are related to disk usage reports that you can see from the below image.
XP_CMDSHELL:
XP_CMDSHELL gives us more control on hosted windows server and we can execute shell commands to get the drive and space information. In most of the environments we could see that this option is disabled as there is a security risk when this option enabled and no proper control on system administrator accounts. If in case this option is enabled in your environment you can try capturing the DISK Space information. Here is an example for automated disk space report using XP_CMDSHELL.
EXEC xp_cmdshell 'wmic logicaldisk get size,freespace,caption';
PowerShell Scripts:
This is one of the best options to automate the Disk Space monitoring procedure. It provides more flexibility and provides more options in capturing DISK Space details.
Note: SQL Server Space Usage report can be automated in various ways, to quickly check the current status we can choose using the system stored procedures, for the report automation PowerShell script is the best option.
