How to Get SQL Server Database Size

Native Solution

Netwrix Auditor for SQL Server

Steps

1. Launch Microsoft SQL Server Management Studio (SSMS).

2. On the File menu, click “Connect Object Explorer”. Fill out the “Connect to Server” dialog box:

Server type: Select “Database Engine.”
Server name: Type the name of the SQL cluster server.
Authentication: Choose your preferred authentication method and specify your credentials. If you do not want to re-type the password every time you connect to the server, tick “Remember password.”

3. Click “Connect”.

4. Upon connection, click “New Query” and enter one of the following as the query:

sp_helpdb Stored Procedure

EXEC sp_helpdb;

sp_databases Stored Procedure

EXEC sp_databases;

sys.master_files Script

SELECT
name,
size,
size * 8/1024 'Size (MB)',
max_size
FROM sys.master_files;

To limit the results to a particular database, add the following line before the semicolon:
WHERE DB_NAME(database_id) = 'DatabaseName'

5. Click “Execute”.

6. Review the output, as illustrated in the table below.

Sample Output of the sp_helpdb Stored Procedure

Sample Output of the sp_databases Stored Procedure

Sample Output of the sys.master_files Script

Note that in the query result set, the total database size is calculated as the sum of the size of the data file itself and the log file.

Open Netwrix Auditor and navigate to Reports -> Predefined -> SQL Server — State-in-Time -> SQL Server Databases.
Click “View” to see your report:

Getting SQL Server Database Size with T-SQL Queries Can Be Cumbersome

Nowadays, databases are used almost everywhere: Business divisions, application development teams, and even webmasters use databases to store dynamically changing information. Microsoft SQL Server is one of the most common database management systems (DBMSs).

SQL database administrators often need to check the database size in the SQL server. For example, if a database is too large for the enterprise, it might need to be normalized; if the size is less than expected, you might be able to denormalize the database to improve query performance. Massive databases require robust hardware or at least a lot of space, so it is expected to see file size to be restricted. It’s essential to monitor the size of test and development databases since the logfiles can be even bigger than the database files, and the resulting volume of data can trigger significant problems.

If you need to check a single database size in SQL Server, you can quickly find the SQL Server database size in SQL Server Management Studio (SSMS): Right-click the database and then click Reports -> Standard Reports -> Disk Usage. Alternatively, you can use stored procedures like exec sp_spaceused to get all databases’ sizes or run the view sys.database_files to see the database size for which the view is stored. However, both methods will show you the size of only a single database. To process all databases on SQL Server, you must use complex T-SQL queries, which require more extensive scripting skills.

Using Netwrix Auditor for SQL Server, you can get database size in a few clicks, along with other key details like database state, location, and last complete backup data. Moreover, Netwrix Auditor provides invaluable actionable intelligence about your Microsoft SQL Server, including who has access to what, who’s logging in, and what critical changes are being made. As a result, you can efficiently conduct regular SQL Server auditing to mitigate the risk of a data breach, spot privilege abuse, prove IT compliance, and maintain high database availability.

Previous How-to

How to Get a SharePoint Online External Sharing Report

Next How-to

How to Check AD Group Membership with Command Line

How to Check SQL Server Database Size

Sample Output of the sp_helpdb Stored Procedure

Sample Output of the sp_databases Stored Procedure

Sample Output of the sys.master_files Script

Getting SQL Server Database Size with T-SQL Queries Can Be Cumbersome