Space Available issues

Question

Space Available issues

RS-298333

SSC Veteran

Points: 206
More actions
September 15, 2006 at 9:26 am

#116694

I've this issue with the database size and the space available in sql server 2000. My databases are showing big amt of space available. For Ex. if i execute sp_spaceused on tempdb it gives me datatbase_size : 6GB and unallocated_space: 5.8GB. I tried to use shrinkdatabase, shrinkfile and also checked to move the pages to the beginning but no success. It still shows that huge amt of space is being used and/or occupied by the database. This is the case with most of the database. And some of our databases are even bigger than 30-40 GB. Any ideas to free this unused space for OS.
I think the problem started when our database was set to grow automatically by 10%.
Thanks for your help.
RS

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

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

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

You should never set the database to grow by a percentage especially if the size of a database starts out small. It takes 73 growth spurts to grow a 1 meg database to a lousy 1 gig at a 10% growth rate... what do you suppose that does to the fragmentation of the underlying file? Always grow a database in mega bytes... lot's of megabytes.

In order to shrink temp DB, you have to click the [Files] button on the shrink window and you have to tell the system what size in megabytes you want it to be. However, having a 6gb tempdb is not a bad thing... we set our up to allocate 12gb on server startup so we don't have to wait for growth and there's enough tempdb to go around for everyone including the system. Got resources? USE THEM! Don't have resources? GET THEM! If you don't, you'll be fighting a constant battle to save space the hard way. Do a table inventory and backup and get rid of the ones you don't need.

Truth is, automatic growth isn't what it's cracked up to be... it for lazy DBA's that don't want to spend any time planning the size of a database. To keep fragmentation to a minimum, your initial size for the database should be enough to keep the db operating for a year with no automatic growth required. It's gonna get there eventually, why not now? And, the DBA's should be looking for the 90% full marker after the initial large setup... once you reach that, add another year's worth of anticipated growth.

--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)

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

absolutely!

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/