SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shrinking Database


Shrinking Database

Author
Message
trayalacheruvu
trayalacheruvu
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 181
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.
ps.
ps.
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3389 Visits: 3668
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.
ps.
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3389 Visits: 3668
EDIT-double post due to internet issue.



Pradeep Singh
trayalacheruvu
trayalacheruvu
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 181
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.
ps.
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3389 Visits: 3668
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
trayalacheruvu
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 181
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.
ps.
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3389 Visits: 3668
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91687 Visits: 45285
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
ManoharV
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 255
will it be any performance issue if i shrink log file?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91687 Visits: 45285
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search