Post reply

DB Size, Space Usage and Shrink DB Confusion

  • February 26, 2004 at 1:25 pm

    #101894

    I am confused with the DB Size, Space Allocated, Space Available, Free Space and Shrink DB.

    1) When a database is initially created, say with 10 MB, does this means that the DB Size is now 10 MB, the Space Allocated is 10 MB, the Space Available is 10 MB (or close to it because of overhead), and the Free Space is 10 MB (or close to it because of overhead)?

    2) When a database is initially created, say with 10 MB, is this storage area filled with zeroes?

    I have the following DB characteristics:

    View Taskpad: DB Size 9658 MB, Space Available 381 MB

    View Taskpad: Space Allocated 9275 MB (Used), 377 MB (Free)

    DB Properties: DB Size 9658, Space Available 381 MB

    3) First, I had to run the sp_spaceused @updateusage=true stored procedure to get the Space Available in the View Taskpad and DB Properties to display. Before the stored procedure was run the Space Available showed zeroes. Is this a problem with SQL EM? Did I do the right thing by running the stored procedure?

    4) The 377 MB of Free Space is this space really free or do I need to Shrink the DB in order to reused this space?

    5) My understanding (even though I am not sure) is that when you delete data out of the .mdf and .ldf the files are not shrunk. The storage area is replaced with zeroes which creates a bunch of empty pages. The Shrink DB or Shrink File commands must be executed to actually shrink the .mdf and .ldf files. Is this accurate? Are the empty pages considered Free Space?

    Thanks in advance,

     

     

  • February 27, 2004 at 3:07 am

    #496124

    There is a whole bunch of information on this topics available in BOL. Too much to post here. I would start at 'space allocation' in BOL and work through the links. Maybe you can also get hands on Inside SQL Server 2000 ?

    BTW, running sp_spaceused @updateusage=true should almost never be a mistake.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply