3

I am confused over the web regrading few scripts on calculating the database size and free space within same.

Most of the scripts over the web uses sys.databases_files dmv to calculate free space like here and here

However if we see one from sp_spaceused or database properties via GUI amount of free space is different. I get the reason that it is diff as it has filter on type in (0,2,4) which excludes Log file.

But i am not able to understand which one is correct and why Log file is excluded from sp_spaceused or is it that scripts over web are not correct and showing something else?

1

You'd have to provide the scripts you're referring to for us to be able to advise you on their accuracy, but if they're based off sys.database_files and the DMVs then good chance you're looking at something that's correct for the purpose they're intended for.

sp_spaceused was specifically designed to tell you the size of the data inside the database, either the whole database itself or for an individual Table or Indexed View:

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

The Transaction Log is stored in a different file and is a different type of object and concept than the database and it's files. The use cases to measure the space consumed by the database either as a whole or for an individual Table or Indexed View is usually more useful to do so without including the Transaction Log files which have a different purpose.

The Transaction Log is a running log of transactions (hence the name) since the last time the log file was backed up, to assist in the recovery of the database to ensure the ACID principles are guaranteed. It generally fluctuates in size (internally) as more transactions are written to the log file, until a transaction log backup occurs. It has no specific concept of indexes, or the data as a whole for the current point in time, rather it is the history to how the data became what it currently is in the database.

sp_spaceused is beneficial in providing you information at the database level on the total space reserved from the disk, how much of that space is used by the data in the database vs how big is the total index_size, and the remaining unused space that will become used by the data and indexes as they grow. Additionally, at the Table or Indexed View level, it even tells you the number of rows in the table (efficiently using meta-data) besides the previously mentioned data points.

To understand the general growth trend of the database, one could routinely view or log the results of sp_spaceused since the change in the data size (and index size) is what will be indicative of that metric, not the Transaction Log size at any given time, per se.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.