If you’re a DBA, or have anything to do with managing SQL Server databases, I’m sure you’ve been asked this annoying question: How big is the database?
Unfortunately there is no single answer to that eternal and common question. There are too many variables which change the information you need to provide. For example, what is the database size going to be used for? If it is being used to determine the size of disk space for an upcoming migration project, the information will be different than if it is for judging whether a backup file will fit on a flash drive (a bad idea in itself, but I’m just making a point here).
The size of a database can be looked at in a number of ways, including the size of .mdf and .ldf files, the projected size of a .bak file, size of 8K data pages including or excluding indexes, and of course the actual size of the transaction log.
Luckily, SQL Server gives us the means with which to query and make sense of this data from within SQL Server Management Studio in a couple of different ways.
The sp_spaceused System Stored Procedure
Using sp_spaceused is a quick way to get a lot of information about a database size. The returned results can be a little difficult to understand however.
As an example, we’ll run the procedure against one of my test databases using the following syntax:
EXEC sp_spaceused @updateusage = true
The option, @updateusage = true, is important because it tells SQL Server to execute DBCC UPDATEUSAGE in order to get an up to date view of the space consumed by objects.
Once executed, we’ll see the following result sets:
The first result set is fairly simple. It tells us the database name, the database size in megabytes, and unallocated space in megabytes. But what do those values actually represent?
This value represents the size, in megabytes, of the sum of the physical .mdf file(s) and .ldf file(s) – that is – the data and log file combined. This value could be useful if for example you are migrating the physical database files to another volume.
This value represents the amount of space within the .mdf file which has not yet been allocated by SQL Server for use by database objects. Remember the .mdf file is simply a container that grows according to the auto-growth settings and limits in the database properties. Its size is relative the actual data contained within it, and is always dependant on these properties.
The output of the second results set is a little more complex, providing the reserved size, data size, index size and unused size in kilobytes.
This value represents the total space reserved by SQL server for use by database objects such as tables and indexes. I refer to this value so often, because this is an accurate projection of the full backup size of the database, as the backup consists only of reserved space. The reserved value is the sum of the data, index_size and unused value.
This value represents the space used by data pages.
This value represents the space used by index pages.
This value represents space which SQL Server has pre-allocated for use by objects such as data pages and indexes, but has not yet been used.
Reserved = data + index_size + unused
Transaction Log Size
Note above that sp_spaceused does not include any specific information about the size of the transaction log, other than the combined size of the transaction log file (.ldf) and the data file (.mdf) which is included in the database_size value returned in the first result set.
However, using a bit of know-how, we can work it out, because reserved + unallocated space should be more or less equal to the size of the .mdf file. Subtract that value from the database_size value, and you should be left with a good representation of the size of your log file.
We can test that using the above values from my test database.
Reserved = 1837.70 MB
Unallocated = 32.37 MB
Therefore, .mdf file size is roughly 1870 MB.
We can check that this is accurate:
So to work out the log size using sp_spaceused alone, we can assume that:
.LDF file size = 2084 MB (database_size value) – 1870 MB
Therefore, .ldf file size = 214MB approx.
Ok, well the calculations are close but a little off. Just remember that the log file is dynamic, it can grow depending on the activity levels of the database at the time the size is taken.
DBCC SQLPERF (logspace)
You can view log file space usage in more detail by using the DBCC command SQLPERF.
Remember that .ldf files, like .mdf files are simply containers that are sized and grow as per the file properties set in the database. The .ldf file is a container that holds vlf files. Vlf files are virtual log files that contain transaction log records.
Just because you have a .ldf file which is 250 MB, doesn’t mean you have 250 MB of transaction log records.
You can check the actual value relative to the size of the file by running
DBCC SQLPERF (logspace)
The output you get will be for all databases:
Note that for my test database the file size is 256 MB. That means the current .ldf file has 256 MB of available space for transactions to be recorded, but the Log Space Used (%) column tells us that only 4% of this space is currently being used, which makes the actual size of used space in the transaction log around 10 MB.
Hopefully this helps the next time you get asked, or maybe even ask yourself, that really annoying question: How big is the database?