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