Shrinking Database

Question

Shrinking Database

trayalacheruvu

SSC Eights!

Points: 965
More actions
July 28, 2009 at 1:29 am

#208689

Hi,
i have around 500 tables in my database with size of 20 GB. Now i am planing to remove 200 tables from the database. after deleting the database still the size is showing same like previously. i came to know that if we shrink the database it will remove unwanted space.Shell we do it in Prtoduction environment during the downtime.is there any performance issue by Shrinking the DB.Please update your inputs here.
Thanks,
Thulasi.

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

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

trayalacheruvu (7/28/2009)
Hi,
is there any performance issue by Shrinking the DB.Please update your inputs here.
Thanks,
Thulasi.

Yes, you'll face performance issues. This is due to the fact that shrinking causes fragmentation of indexes. You may have to reorganze or rebuild your indexes post shrink operation.

If you're certain that your database will not reach it's current size in next 3-6 months, you can do shrinking for once or else leave it to its current size as it'll be eventually filled in due course of time.

Pradeep Singh

ps. One Orange Chip Points: 29422 More actions · Answer 2

EDIT-double post due to internet issue.

Pradeep Singh

trayalacheruvu SSC Eights! Points: 965 More actions · Answer 3

hi,

Thanks for your quick update update.. Actually i am rebuilding the indexes every during the non-working hours.. ok after Shrink the Database i need to rebuild the indexes. the performance will be as usaual right?

Thanks,

thulasi

ps. One Orange Chip Points: 29422 More actions · Answer 4

trayalacheruvu (7/28/2009)
hi,
Thanks for your quick update update.. Actually i am rebuilding the indexes every during the non-working hours.. ok after Shrink the Database i need to rebuild the indexes. the performance will be as usaual right?
Thanks,
thulasi

You should not rebuild your indexes so often. this again makes your log files to grow heavily... Rebuiling indexes will, however, keep the performance right.

Pradeep Singh

trayalacheruvu SSC Eights! Points: 965 More actions · Answer 5

ok i agree rebilding the indexes will increase the log file..

Actually i want to show management after cleaning up the database this size got reduced from Ex: 200 GB to 150 GB..means which will suggestible one ?

Thanks,

Thulasi.

ps. One Orange Chip Points: 29422 More actions · Answer 6

trayalacheruvu (7/28/2009)
Actually i want to show management after cleaning up the database this size got reduced from Ex: 200 GB to 150 GB.

If you are sure the db will not grow upto 200GB again in the near future, you can go ahead with shrinking the database and rebuilding indexes.

Pradeep Singh

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

Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.

Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

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

ManoharV SSCommitted Points: 1730 More actions · Answer 8

will it be any performance issue if i shrink log file?

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

If it has to grow again, yes.

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: 1003853 More actions · Answer 10

trayalacheruvu (7/28/2009)
Hi,
i have around 500 tables in my database with size of 20 GB. Now i am planing to remove 200 tables from the database. after deleting the database still the size is showing same like previously. i came to know that if we shrink the database it will remove unwanted space.Shell we do it in Prtoduction environment during the downtime.is there any performance issue by Shrinking the DB.Please update your inputs here.
Thanks,
Thulasi.

You say the original database size was 20 GB. What was the "used" size after deleting the 200 tables?

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