Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Posts
    27

    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?

    Thanks!

    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?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •