How big is the database? How to Answer that Annoying Question…

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:

spaceused
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?

database_size

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.

unallocated space

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.

reserved

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.

data

This value represents the space used by data pages.

index_size

This value represents the space used by index pages.

unused

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.

Remember,

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:

mdf

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.

ldf

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:

sqlperf

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?

Advertisements

3 thoughts on “How big is the database? How to Answer that Annoying Question…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s