How to Find SCCM Database Size using SQL Query

In this post, I will show you how to find SCCM database size using SQL query. SQL Server allows you to manually check the size of the SCCM database, including free space details.

We know that each Configuration Manager site requires a supported SQL Server version and configuration to host the site database. Older versions of SQL Server installed for SCCM must be updated to recent version as they provide better performance.

SQL Server is used to store information for Configuration Manager sites. Each site in a Configuration Manager hierarchy contains a site database and a server that is assigned the site database server role.

You must be aware of the size of the SCCM database because occasionally, you may run out of the disk space. For example, the size of the report server database log file taking up most of the disk space is one of the most common problems ConfigMgr administrators run into. Luckily, you can shrink the size of SCCM reporting services log database.

You might need to look at the data being stored in the DB if your SCCM database is using up more disk space. The ConfigMgr database should not be shrunk because doing so would just reduce performance; instead, it is advised that you increase your disk space if necessary. If you do shrink it, run your database maintenance operations on it afterwards because your indexes will likely be fragmented.

Also Read: How to Change SQL Database Recovery Model

Determine the Size of ConfigMgr Database

There are two ways to determine the size of SCCM database: using SQL query and manually checking the database properties in SQL Server. Using a SQL query provides more details in the output, and you can even export the database size details to a file.

Some useful SQL queries that you can use with Configuration Manager are listed below:

How to Find SCCM Database Size using SQL Query

Let’s go through the steps to find the size of SCCM database using a SQL query.

Launch the SQL Server Management Studio.
Connect to the Database Engine with your credentials.
Right-click SCCM Database and select New Query.
In the query window, enter the below SQL query and click Execute.
The query output shows the SCCM database size and file path details.

select
Sys.FILEID as 'FileID',
left(Sys.NAME,15) as 'DBName',
left(Sys.FILENAME,60) as 'DBFilePath',
convert(decimal(12,2),round(Sys.size/128.000,2)) as 'Filesize (MB)',
convert(decimal(12,2),round(fileproperty(Sys.name,'SpaceUsed')/128.000,2)) as 'UsedSpace (MB)',
convert(decimal(12,2),round((Sys.size-fileproperty(Sys.name,'SpaceUsed'))/128.000,2)) as 'FreeSpace (MB)',
convert(decimal(12,2),round(Sys.growth/128.000,2)) as 'GrowthSpace (MB)'
from dbo.sysfiles Sys

From the query output, you get the size of the SCCM database in MB along with following details:

DBName: The name of the SQL database that holds the ConfigMgr data.
FileSize (MB): The total size of the ConfigMgr Database in MB.
UsedSpace (MB): The total space utilized.
FreeSpace (MB): Free space remaining in SCCM database.
GrowthSpace (MB)

Check the Size of SCCM Database from SQL Server

If you want to avoid using a SQL query to find the size of SCCM database, you can manually check the database size from SQL server. To accomplish that, launch the SQL Server Management Studio and connect to Database Engine. Expand Database and right-click on ConfigMgr Database and select Properties.

On the Database Properties window of Configuration Manager database, select General option. On the right-hand side, you can see the size of the SCCM database along with total space available. The SQL collation details are also shown in the same window.