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:
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.
Use the following SQL query
SUM(size) AS database_size
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:
SolarWinds Database Performance Monitor
Quest Spotlight on SQL Server
These tools typically offer a variety of features for monitoring database size, such as:
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.
Select the server group
Select the server
Select the database you want to check
Next you select the tab you want to see
1) SQL-Server General Tab
The General tab lists the total database size and the space available.
2) Tables & Indexes Tab 3) Space Allocated Tab
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.
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:
SELECT name, size, max_size from sys.database_files;
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:
DBCC UPDATEUSAGE (DatabaseSample);
database_name database_size unallocated space
DatabaseSample 197.94 MB 15.62 MB