SIZE CALCULATION OF SQL DATABASES

In this post i write about some methods of calculating the size of databases in a SQL Server instance

To get the size of the mdf+ldf files taken together, use the following

exec sp_helpdb



There is another method that gives the size of the data and the log files separately. The size column is not the actual size but the number of 8KB extents (a measure of size in SQL Server). Therefore to get the actual size, we need to multiply the third column with eight to get the actual size in KBs.

use master
select name, type_desc, size, size*8 as KB, size*8/1024 as MB from sys.master_files



The same can also be achieved if we want to view the details of any one single database. For example to find the details of only the master database, use the following

use master
select name, type_desc, size, size*8 as KB, size*8/1024 as MB from sys.database_files

All the above methods are consistent and give the correct database size.

No comments: