How to Get SQL Server Database Size

The ability to keep track of our SQL Server databases is very important to maintaining our storage resources effectively and within the proper performance limits. Knowing how large databases are can put us in a position to set up storage capacity which indicates the beginning of solving issues related to disk space, and improving database performance.

In this article, We will learn about how to Get SQL Server Database Size by understanding various methods along with the implementation and so on.

How to Get SQL Server Database Size?

Monitoring SQL Server database sizes is essential for efficient storage management and performance optimization. Below are the approaches that help us to understand How to Get SQL Server Database Size as follows:

1. Using Table Sys.master_files
   
2. Using Stored Proc sp_spaceused
   
3. Using the Manual Option in SSMS

1. Using Table Sys.master_files

The sys.master_files system-view provides detailed information about all files associated with the SQL Server instance, including database files. We can use this view to retrieve the size of database files.

USE [YourDatabaseName];
GO


SELECT 
    DB_NAME(database_id) AS DatabaseName,
    name AS FileName,
    size/128.0 AS FileSizeInMB
FROM 
    sys.master_files
WHERE 
    type_desc = 'ROWS';

OUTPUT:

Explanation:

• DB_NAME(database_id) retrieves the name of the database.
  
• name retrieves the name of the database file.
  
• size/128.0 calculates the file size in megabytes (MB). SQL Server stores the size of database files in 8KB pages, so dividing by 128 converts it to MB.
  
• type_desc = 'ROWS' filters the results to show only data files.

2. Using Stored Proc sp_spaceused

SQL Server has a sp_spaceused stored procedure that, when executed, produces tables then defining the current space used for a certain database or any object within that database. This stored procedure provides data about data size, unallocated space, reserved space, data space, index space and space across unused.

To use sp_spaceused, execute the following SQL command:

USE [YourDatabaseName];
GO
EXEC sp_spaceused;

OUTPUT:

Explanation: The result set from sp_spaceused includes the following columns:

• Database_name: Name of the database.
  
• Database_size: Total size of the database.
  
• Unallocated space: Space available for new data.
  
• Reserved: Total reserved space for database objects.
  
• Data: Used space by data.
  
• Index_size: Used space by indexes.
  
• Unused: Space that is allocated but not used.

3. Using the Manual Option in SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) grant us the GUI which is user-friendly and to be used to work on and track SQL Server Database Management. It has an embedded query ability that launches DB size visualization without running SQL queries.

Steps to follows:

• Space to Verify Data Size in SSMS
  
• Run SSMS, specify the instance of our SQL Server and establish a connection to it.
  
• In the Object Explorer, if the Databases node is not expanded already, expand it.
  
• Click on the right-click on the data declaration we want to review. Select Properties after clicking on.
  
• In the Database Properties dialog which can be accessed from the General page and select the field names for the conditions.
  
• Number of database size will be displayed right below this header. We will see the database size in MB.

Concusion

Overall, Monitoring SQL Server database sizes is critical for effective database management. By utilizing the methods outlined in this article, database administrators can efficiently track database sizes, allocate resources effectively, and optimize database performance. Understanding database sizes is key to making informed decisions and ensuring the smooth operation of SQL Server environments.

varshneyrajat57

Follow

Improve

Article Tags :

• SQL Server
• Databases