To Shrink or Not to Shrink?
in [Server Configuration]
|
Prev: How to find out the point time when the database is damaged/crashe
Next: creating a connection in VisualStudio
From: nzrdb6 on 4 May 2010 11:10 I've inherited a bit of a mess. My employer is a majot form but they've never had a DBA!!!!! I've got 30 instances with 600 DBs in total. Unfortunately, none of the DBs were ever sized properly at the outset, so many DBs have too much logcial file space. Now I know it's a DBA no-no to auto shrink, but in my circumstance should I do a blanket shrink once a week, targetting 10% free space followed immediately by an index defrag? Just a jobbing DBA trying to bring some order into a world of chaos! Thanks Gurus
From: Tibor Karaszi on 4 May 2010 12:30 One-time shrink can be more or less necessary if you inherit a system and want to clean up some mess. But remember that both data and log files will bump up to a certain size for a reason and keep growing and shrinking files will just cost. So, do it once for those you feel you will benefit from the returned disk space, but leave some 20-50% free space in data files and have "comfortable" size for log files. I guess you already seen: http://www.karaszi.com/SQLServer/info_dont_shrink.asp -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "nzrdb6" <alex.campbell(a)sqltechconsulting.co.uk> wrote in message news:4ace53af-4757-4e35-b059-7bbe70777931(a)e35g2000yqm.googlegroups.com... > I've inherited a bit of a mess. My employer is a majot form but > they've never had a DBA!!!!! I've got 30 instances with 600 DBs in > total. Unfortunately, none of the DBs were ever sized properly at the > outset, so many DBs have too much logcial file space. Now I know it's > a DBA no-no to auto shrink, but in my circumstance should I do a > blanket shrink once a week, targetting 10% free space followed > immediately by an index defrag? > > Just a jobbing DBA trying to bring some order into a world of chaos! > > Thanks Gurus
From: John Bell on 4 May 2010 12:29 On Tue, 4 May 2010 08:10:45 -0700 (PDT), nzrdb6 <alex.campbell(a)sqltechconsulting.co.uk> wrote: >I've inherited a bit of a mess. My employer is a majot form but >they've never had a DBA!!!!! I've got 30 instances with 600 DBs in >total. Unfortunately, none of the DBs were ever sized properly at the >outset, so many DBs have too much logcial file space. Now I know it's >a DBA no-no to auto shrink, but in my circumstance should I do a >blanket shrink once a week, targetting 10% free space followed >immediately by an index defrag? > >Just a jobbing DBA trying to bring some order into a world of chaos! > >Thanks Gurus Hi I am not sure why you think you should need to regularly shrink the database???? Expanding the database costs resource and can cause poor performance especially if you have lots of database all trying to expand http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx Therefore autoshrink and a scheduled shrink are going to have the same issues. If over the course of the week (or some other period) the data in the files expands and contracts then you should size them so that they don't have to expand. If the the database files are a size which they are not likely to hit in a significant period of time, then you may want to do a single shrink activity if reclaiming the space will be beneficial. John
From: TheSQLGuru on 4 May 2010 13:08
Agree with Tibor. ONE TIME this could be feasible of you have a VERY high percentage of free space in the database files. Weekly - absolutely not! I recommend to my clients to leave 12-18 months of future growth as free space in their database files. Oh, and I STRONGLY recommend you have your company hire a professional to give their system a very thorough review. Their are likely MANY issues that need to be fixed, some of which could put the company at risk. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "nzrdb6" <alex.campbell(a)sqltechconsulting.co.uk> wrote in message news:4ace53af-4757-4e35-b059-7bbe70777931(a)e35g2000yqm.googlegroups.com... > I've inherited a bit of a mess. My employer is a majot form but > they've never had a DBA!!!!! I've got 30 instances with 600 DBs in > total. Unfortunately, none of the DBs were ever sized properly at the > outset, so many DBs have too much logcial file space. Now I know it's > a DBA no-no to auto shrink, but in my circumstance should I do a > blanket shrink once a week, targetting 10% free space followed > immediately by an index defrag? > > Just a jobbing DBA trying to bring some order into a world of chaos! > > Thanks Gurus |