Monitoring Databases  «Prev  Next»

Lesson 3 Monitoring database size with SQL-EM
Objective Monitor the size of a database with SQL-EM.

Monitoring Database Size using SQL-EM

SQL Server has not used SQL-EM (SQL Enterprise Manager) since SQL Server 2005. SQL-EM was replaced by SQL Server Management Studio (SSMS), which is the primary tool for managing and monitoring SQL Server instances and databases.
To monitor the size of a database in SSMS, you can use the following methods:
  1. Right-click the database in Object Explorer and select 
    Reports > Standard Reports > Disk Usage.
    This will open a report that shows the size of the database files, as well as the free space available on the disk drive where the files are located.
  2. Use the following SQL query
SELECT
    SUM(size) AS database_size
FROM
    sys.database_files
WHERE
    database_id = DATABASE_ID();

This query will return the total size of the database files in bytes. You can also use a third-party monitoring tool to monitor the size of your SQL Server databases. Some popular third-party monitoring tools include:
  1. SolarWinds Database Performance Monitor
  2. Quest Spotlight on SQL Server
  3. DBmaestro
  4. Datadog
  5. New Relic

These tools typically offer a variety of features for monitoring database size, such as:
  1. Real-time monitoring
  2. Alerting
  3. Trending
  4. Reporting

Which method you choose to monitor the size of your SQL Server databases will depend on your specific needs and requirements.
SQL Server provides three levels of information about database size; 1) total size, 2) size of each object, and 3) size for each file. Depending on the detail you want, you can use one of the tabs on SQL-EM database screen.
To get to the screen you perform the following steps.
  1. Start SQL-EM
  2. Select the server group
  3. Select the server
  4. Select database
  5. Select the database you want to check

Next you select the tab you want to see.


1) SQL-Server General Tab
Database General Tables and Indexes - Space Allocated
1) The General Tab
The General tab lists the total database size and the space available.
Space Allocated

2) Tables & Indexes Tab
Table
2) Table and Indexes: For every table and index in the database the Table & Indexes tab will display the number of rows and number of kilobytes used.

3) Space Allocated Tab
General Tables and Indexes - Space Allocated
3) The database Space Allocated tab will display the size of each file and how much of the size is used.

The next lesson will cover how to monitor a database with dbcc.

Monitoring Database Space in SQL-Server

By completing the simulation, you should have noted that the Customer table is using 48MB of space and the Sales Rec table is using about 148 MB of space. ourDatabase.sys.sysfiles has the size of each file.
Normally you would differentiate between mdf and ldf, but if you only need the database size, I would go for only the mdf file.

High Performance SQL Server

Monitoring database File Sizes

Three factors of file size should be monitored: the size of the database files and their maximum growth size, the amount of free space within the files, and the amount of free space on the disk drives. The current and maximum file sizes are stored within the sys.database_files database catalog view. The following code displays the name, size, and max size for the DatabaseSample database: 
USE DatabaseSample;
SELECT name, size, max_size from sys.database_files;

Result: 
name size max_size
--------- ------- ---------
DatabaseSample_Data 25080 -1
DatabaseSample_Log 256 268435456

Here, size is the current size, and max_size is the maximum size of the file, in 8KB pages. A value of -1 for max_size indicates that the file will grow until the disk is full, and 268435456 indicates that the maximum size of the log file will be 2TB. To check the current and maximum file sizes for all the databases, use the sys.master_files catalog view. To detect the percentage of the file that is actually being used, use the sp_spaceused system stored procedure. Optionally, you can run the DBCC UPDATEUSAGE command to correct disk space usage inaccuracies or use the @updateusage optional parameter with the sp_spaceused command.
The following command updates the space usage information of the DatabaseSample sample database and then runs the sp_spaceused command: 
USE DatabaseSample;
DBCC UPDATEUSAGE (DatabaseSample);
EXEC sp_spaceused;

Result: 
database_name			database_size			unallocated space
--------------------------------------------------------
DatabaseSample 		197.94 MB 				15.62 MB

reserved 						data 							index_size 					unused
------------------ ------------------ ------------------ --------
184648 KB 					96672 KB 					81440 KB 						6536 KB

SEMrush Software