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 below.

Sample Output of the sp_helpdb Stored Procedure

How to Check SQL Server Database Size - sp_helpdb

Sample Output of the sp_databases Stored Procedure

How to Check SQL Server Database Size - sp_databases

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.

How to Check SQL Server Database Size - sys.master_files

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. One of the most common database management systems (DBMSs) is Microsoft SQL Server.

SQL database administrators often need to estimate how large a database is. 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. In general, massive databases require strong hardware, or at least a lot of space, so it is normal to see file size be restricted. It’s particular important to monitor the size of test and development databases, since the log files 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, you can quickly find the SQL Server database sizein 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 database size. However, both of these methods will show you the size of only a single database. To process all databases on SQL Server, you have to use T-SQL queries, which are complex and 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 full 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 easily 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 Find Duplicate Files

Next How-to

How to Check AD Group Membership with Command Line

How to Check SQL Server Database Size