All we need is an easy explanation of the problem, so here it is.
I am confused over the web regrading few scripts on calculating the database size and free space within same.
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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂