SQLConfiguration SQLConfiguration


Monitoring Databases  «Prev  Next»

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