Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Shrinking Database Expand / Collapse
Author
Message
Posted Tuesday, July 28, 2009 1:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 29, 2013 3:44 AM
Points: 25, Visits: 156
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
Posted Tuesday, July 28, 2009 1:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, January 26, 2014 11:13 AM
Points: 2,242, Visits: 3,641
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
Posted Tuesday, July 28, 2009 1:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, January 26, 2014 11:13 AM
Points: 2,242, Visits: 3,641
EDIT-double post due to internet issue.




Pradeep Singh
Post #760576
Posted Tuesday, July 28, 2009 3:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 29, 2013 3:44 AM
Points: 25, Visits: 156
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
Posted Tuesday, July 28, 2009 4:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, January 26, 2014 11:13 AM
Points: 2,242, Visits: 3,641
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
Posted Tuesday, July 28, 2009 4:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 29, 2013 3:44 AM
Points: 25, Visits: 156
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
Posted Tuesday, July 28, 2009 7:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, January 26, 2014 11:13 AM
Points: 2,242, Visits: 3,641
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
Posted Tuesday, July 28, 2009 8:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
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
Posted Sunday, August 02, 2009 5:43 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Sunday, August 02, 2009 1:15 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
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 »

Add to briefcase 12»»

Permissions Expand / Collapse