SQL Server Space Usage

SQL Server Space Usage

SQL Server Space Usage

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.

SP_SPACEUSED

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.

 

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.

 

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.

 

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.

 

Database and File wise Space Usage:

Here is the query to get database and the individual file (Data and Log) wise space usage information.

 

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.

 

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.

 

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.

D:\Users\703124718.INDGE\Desktop\SQLServer_Space_Usage.jpg

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.

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.

Link1

Link2

Link3

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.

sql-server-space-usage-scripts is the Script file with all scripts.

Posted in SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , , , , | Leave a comment

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz