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