Check database size in Sql server ( Various Ways explained)

Sometimes before taking backup of database in sql server, we might need to check it's database size, or we might need size for some other process also, so in this post, I have explained how you can check SQL server database size in all possible ways, using SQL query or using built in Stored Procedures.

1.Using SQL server Query

We can run a simple query to get the size of all the database present in the SQL server, so here the query which you can use

SELECT
      d.database_id
    , d.name
    , d.state_desc
    , t.total_size
    , t.data_size 
    , t.log_size  
    , bu.full_last_date
    , bu.full_size
    , bu.log_last_date
    , bu.log_size
FROM (
    SELECT
          database_id
        , log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
    FROM sys.master_files
    GROUP BY database_id
) t
JOIN sys.databases d ON d.database_id = t.database_id

LEFT JOIN (
    SELECT
          database_name
        , full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
        , full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
        , log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
        , log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
    FROM (
        SELECT
              s.database_name
            , s.[type]
            , s.backup_finish_date
            , backup_size =
                        CAST(CASE WHEN s.backup_size = s.compressed_backup_size
                                    THEN s.backup_size
                                    ELSE s.compressed_backup_size
                        END / 1048576.0 AS DECIMAL(18,2))
            , RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
        FROM msdb.dbo.backupset s
        WHERE s.[type] IN ('D', 'L')
    ) f
    WHERE f.RowNum = 1
    GROUP BY f.database_name
) bu ON d.name = bu.database_name
ORDER BY t.total_size DESC

In the above SQL server query we are getting each database size, log size, and status if database is online or not.

Output:

2. Using sp_spaceused Stored Procedure

sp_spaceused Stored procedure displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

This stored procedure is system based, so we can directly use it, no need to create it.

USE EmployeeDetails;
EXEC sp_spaceused;

In the above query, EmployeeDetails is a database name, stored in sql server.

Output:

3. Using Table Sys.master_files

This stored procedure queries the sys.master_files view. So an alternative is to go straight to the view and cherry pick your columns:

    SELECT      sys.databases.name,  
                CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Total disk space]  
    FROM        sys.databases   
    JOIN        sys.master_files  
    ON          sys.databases.database_id=sys.master_files.database_id  
    GROUP BY    sys.databases.name  
    ORDER BY    sys.databases.name  

Here is the output of the above query

4. Using sp_databases Stored Procedure

One more option is the sp_databases system stored procedure. This stored procedure lists databases that either reside in an instance of the SQL Server or are accessible through a database gateway.

Simple Execute it as below:

EXEC sp_databases;

Output:

5. Manually Check using SQL server Management Studio GUI

This is another option to know database size.

Simply follow these directions:

Go to Server Explorer -> Expand it -> Right click on Database -> Choose Properties -> In popup window choose General tab ->See Size

Sample Output:

6. Using sys.database_files

Here is the another simple query to execute using sys.database_files to check Database size.

SELECT
    DB_NAME() AS [database_name],
    CONCAT(CAST(SUM(
        CAST( (size * 8.0/1024) AS DECIMAL(15,2) )
    ) AS VARCHAR(20)),' MB') AS [database_size]
FROM sys.database_files;

That's it, there are more ways to check db size in SQL Server, but these are simpler and better ways to check SQL server database size.

You may also like to read:

How to back up SQL Server database ? (Various Ways explained)

Restore (Import) database from .bak file in SQL server (With & without scripts)

Understanding SQL server switch case (With Example)

Difference between Stored procedure and function in SQL Server (Lists differences and explains each of them separately.)

Download and install SQL Server management Studio