06-28-10, 14:01 #1Registered User
- Join Date
- Mar 2009
Unanswered: Understanding space used vs. disk space
SQL Server 2005
Painfully retentive description below but actual question is, if you're trying to decide if your db is going to bonk when you push a big data update into it what do you believe? sp_spaceused? the size Windows tells you it is taking up on the disk? Can anyone point me to a good article that discusses how sp_spaceused compares to actual file size on disk and whether the unallocated space takes into account the actual disk size?
I have a db that I will be loading in 36GB of data and then doing assorted processing with said data this week. The footprint of the data will be in excess of 130 GB unless I start dropping objects before their new version is in place which is always a little scary. The server currently has 21 GB of free space on the drive that contains both the .mdf & the .ldf files.
In the past this has always been a problem as space issues typically cause multiple crashes. I'm trying to make sense out of the info I have on current space for this db:
The first return from sp_spaceused gives:
Database_size: 456168.44 MB (456 GB)
Unallocatedspace: 366583.49 MB (366 GB)
This makes me think that the db is 89.6 GB (456168.44 -366583.49) and that I have 367 GB of free space reserved on the server. This seems odd immediately because if I add the size of the .mdf & .ldf files I get 467 GB.
The second return from sp_spaceused gives me:
Reserved: 58199048 KB (58 GB total db size?)
Data: 48426592 KB (48 GB)
Index_Size: 9734784 KB (9.7 GB)
Unused: 37672 KB
This implies that the db is currently occupying 58 GB of space, which doesn't jive with 89.6 GB from the first statement.
Why doesn't this add up?
06-29-10, 10:13 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
First result set:
Database_size: total allocated size on disk for all data and log devices. This number corresponds to the total disk space used by all database files;
Unallocated space: the space within Database_size minus the sum of the sizes of log devices, that is not reserved for any objects. This value corresponds to the amount of data that can be loaded into the database without causing it to grow, meaning the allocated space on disk will not change.
Second result set:
Reserved = Data + Index_Size + Unused. In your case it's 58.199048GB, which is what Reserved shows. This means that you can load up to 397.969392GB worth of data and index (watch out for index, because while data can be preestimated, you really need to account of growth of index B-Trees associated with the tables you're populating, including clustered and nonclustered);
Data and Index are straight forward, holding data and index pages respectively;
Unused: this is not FREE, but is reserved for existing objects, while not used by them yet.
"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."