<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 General Discussion
»
Shrinking Database
11 posts, Page 1 of 2
1
2
»»
Shrinking Database
Rate Topic
Display Mode
Topic Options
Author
Message
trayalacheruvu
trayalacheruvu
Posted Tuesday, July 28, 2009 1:29 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, February 19, 2013 10:18 PM
Points: 25,
Visits: 152
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.
Post #760568
ps.
ps.
Posted Tuesday, July 28, 2009 1:48 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236,
Visits: 3,620
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
Post #760575
ps.
ps.
Posted Tuesday, July 28, 2009 1:51 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236,
Visits: 3,620
EDIT-double post due to internet issue.
Pradeep Singh
Post #760576
trayalacheruvu
trayalacheruvu
Posted Tuesday, July 28, 2009 3:38 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, February 19, 2013 10:18 PM
Points: 25,
Visits: 152
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
Post #760627
ps.
ps.
Posted Tuesday, July 28, 2009 4:11 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236,
Visits: 3,620
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
Post #760639
trayalacheruvu
trayalacheruvu
Posted Tuesday, July 28, 2009 4:40 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, February 19, 2013 10:18 PM
Points: 25,
Visits: 152
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.
Post #760652
ps.
ps.
Posted Tuesday, July 28, 2009 7:45 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236,
Visits: 3,620
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
Post #760751
GilaMonster
GilaMonster
Posted Tuesday, July 28, 2009 8:35 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099,
Visits: 30,392
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 2008, MVP
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
Post #760805
ManoharV
ManoharV
Posted Sunday, August 02, 2009 5:43 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, January 14, 2010 8:33 PM
Points: 108,
Visits: 255
will it be any performance issue if i shrink log file?
Post #763735
GilaMonster
GilaMonster
Posted Sunday, August 02, 2009 1:15 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099,
Visits: 30,392
If it has to grow again, yes.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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
Post #763780
« Prev Topic
|
Next Topic »
11 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
