Post reply

DB whitespace

  • April 9, 2012 at 12:22 pm

    #259727

    I was asked this by a systems guy and I did not have a good answer:

    what is MS-SQL reporting as DB whitespace?

    Any information would be greatly appreciated.

    Charlie

  • April 9, 2012 at 12:25 pm

    #1470835

    To answer that properly, we'd need to know what report you're looking at, because 'whitespace' is not a usual term for data space.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 9, 2012 at 12:58 pm

    #1470877

    Thanks for the reply Gail. I relayed your response to the person that asked the question. He said he really meant to ask about "free space"... I told him there are a lot of script on the internet that you can run to report the free space in a database. Thanks again for the reply Gail!

  • April 9, 2012 at 1:02 pm

    #1470879

    Mostly that will be unallocated pages. SQL stores data on pages that are 8kb in size. If a page does not belong to an object (it's never been allocated, was allocated to an object that was dropped, or was deallocated because it had no rows on it), it's an unallocated page and part of the database free space.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 9, 2012 at 1:40 pm

    #1470904

    Is there a better way to regain this free space other than a Shrink..... which leads to a rebuild or reorganization of the now fragmented indexes?

  • April 9, 2012 at 1:44 pm

    #1470909

    Why do you want to reclaim it?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 9, 2012 at 2:09 pm

    #1470915

    the higher ups are super stingy with disk space.... we have to squeeze out all we can to run the system.

  • April 9, 2012 at 2:11 pm

    #1470918

    rummings (4/9/2012)

    the higher ups are super stingy with disk space.... we have to squeeze out all we can to run the system.

    DASD or SAN?

  • April 9, 2012 at 2:12 pm

    #1470919

    So they'd rather see free space wasted at a disk level than free space 'wasted' in a database?

    Seriously, unless there's huge space free in the DB, there is no gain from releasing it back to the file system and a great deal to lose by doing so.

    There is no advantage in having 0 free space in a DB and 20% of the disk free over having that free space within the database file.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 9, 2012 at 8:32 pm

    #1471036

    rummings (4/9/2012)

    the higher ups are super stingy with disk space.... we have to squeeze out all we can to run the system.

    BWAAA-HAAAA!!!! The first mistake was someone telling the higher ups about it. 🙂 Tell them to buy a couple of extra spindles and to stop worrying about it. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 10, 2012 at 11:52 am

    #1471398

    rummings (4/9/2012)

    the higher ups are super stingy with disk space.... we have to squeeze out all we can to run the system.

    Before deciding to shrink down the database, do some reading on blog posts by Paul Randal.

    http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 11 posts - 1 through 10 (of 10 total)

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