DB whitespace

Question

DB whitespace

rummings

SSCarpal Tunnel

Points: 4575
More actions
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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

rummings SSCarpal Tunnel Points: 4575 More actions · Answer 2

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

rummings SSCarpal Tunnel Points: 4575 More actions · Answer 4

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

rummings SSCarpal Tunnel Points: 4575 More actions · Answer 6

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 9

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)

Eric M Russell SSC Guru Points: 125560 More actions · Answer 10

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