March 10, 2015 at 3:33 pm
I've read several times in different places that one should NEVER shrink a database data file except as a last resort due to the effect on index fragmentation. One should add hard drive space instead. But the lead DBA where I work doesn't think it's a big deal, since we do have index maintenance jobs which run nightly.
He seem to have a point. A temporary performance hit due to fragmented indexes on one database is a lesser evil than ALL databases on a data drive at risk being unable to accept inserts until drive space can be added or data files moved (which requires downtime for the affected database). Perhaps the better advice would be shrink a file as an emergency short-term solution (not all the way - just enough to allow any DB that needs to grow to do so), then order up more drive capacity and/or move files as the longer term solution?
March 10, 2015 at 5:28 pm
dan-572483 (3/10/2015)
I've read several times in different places that one should NEVER shrink a database data file except as a last resort due to the effect on index fragmentation. One should add hard drive space instead. But the lead DBA where I work doesn't think it's a big deal, since we do have index maintenance jobs which run nightly.He seem to have a point. A temporary performance hit due to fragmented indexes on one database is a lesser evil than ALL databases on a data drive at risk being unable to accept inserts until drive space can be added or data files moved (which requires downtime for the affected database). Perhaps the better advice would be shrink a file as an emergency short-term solution (not all the way - just enough to allow any DB that needs to grow to do so), then order up more drive capacity and/or move files as the longer term solution?
Your DBA isn't doing his/her job if they are allowing AUTOGROWTHS to maintain the database sizes!! Autogrowth should be an EXCEPTIONAL event. I tell my clients to figure out how big the database needs to be 12-18 months down the road AT LEAST and make it that big NOW. Monitor monthly and take action if necessary.
Also, every time you write to a disk there is a non-zero chance of a corruption. Why would you waste all that effort to shrink a database (TONS of write activity) just to be forced to do a lot MORE write activity to unwind the ridiculous fragementation you knew you were creating?!?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 10, 2015 at 7:42 pm
The fragmentation problem of database shrink events is not to the indexes. That type of fragmentation is easily fixed. It's fragmentation of the file on disk. That's different and not easily fixed.
Now, NEVER is a huge word. I've shrunk databases in the past when there's been some type of event that caused it to grow in an inappropriate way. I'll do it in the future. It's not the one-off shrink that's a big deal. It's the weekly or nightly scheduled shrink event that leads to major issues and should be avoided.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 11, 2015 at 2:42 am
There are definitely times when shrinking is fine. I actually have a scheduled weekly shrink job on my dev server. That said, if you're so space-constrained that you're repeatedly shrinking and growing (index rebuilds will cause the DB to grow) production databases to avoid running out of space, there's a larger problem at hand.
Perhaps the better advice would be shrink a file as an emergency short-term solution (not all the way - just enough to allow any DB that needs to grow to do so), then order up more drive capacity and/or move files as the longer term solution?
No, the better advice would be not to shrink and to do sufficient planning and forecasting that you have enough drive capacity before the DBs out-grow what they have.
Be grateful you have a small enough DB that you can rebuild indexes nightly. I've worked with DBs large enough and with small enough maintenance windows that it took 2 weeks of nightly maintenance to rebuild just the indexes that had got > 30% fragmentation.
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
March 11, 2015 at 11:57 am
TheSQLGuru (3/10/2015)
Your DBA isn't doing his/her job if they are allowing AUTOGROWTHS to maintain the database sizes!! Autogrowth should be an EXCEPTIONAL event. I tell my clients to figure out how big the database needs to be 12-18 months down the road AT LEAST and make it that big NOW. Monitor monthly and take action if necessary.
Also, every time you write to a disk there is a non-zero chance of a corruption. Why would you waste all that effort to shrink a database (TONS of write activity) just to be forced to do a lot MORE write activity to unwind the ridiculous fragementation you knew you were creating?!?
In a perfect world, we could all be familiar enough will all our databases to accurately predict their behavior over a year and size drives & files appropriately. But in the real world some of us are responsible for dozens of SQL instances hosting hundreds of databases. The mission-critical or massive databases consume most of our attention, and many of the others have been running relatively quietly since before we started working at our current companies, or were installed by 3rd party application vendors who won't tell us much about how their DBs behave.
I agree that in all but rare cases, a routine shrink job is not a sound solution. But in the real world, it's something we need to do from time to time, so it's worth discussing how to do it with minimal disruption.
March 11, 2015 at 12:17 pm
The reason why shrink database should typically be avoided is that if the database grew to that size it indicates it needed that much space for some reason so shrinking it just means it will grow again. Unless you're planning to take a database/file out of active use and just keep it around for archive purposes shrinking for drive space reasons is an oh crap must happen or we're screwed solution, which hopefully isn't routine.....
March 11, 2015 at 12:22 pm
I have been a "real-world" SQL Server consultant for almost 20 years now. I have managed SQL servers with well over 7000 databases on per server. You CAN trend sizing needs, you CAN automate lots of stuff to make your job easier and not let those "unwatched" databases create messes.
I agree that in all but rare cases, a routine shrink job is not a sound solution. But in the real world, it's something we need to do from time to time
I have a VERY VERY hard time thinking of scenarios where a ROUTINE shrink job is required. If you shrink routinely that means the database is growing routinely. Why not leave it where it routinely grows to?!? You are getting all of the things I mentioned ... for ZERO benefit. Oh, and lets not forget the OS file fragmentation issue too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 11, 2015 at 12:36 pm
Two messages up GilaMonster describes a scheduled shrink job she has on a dev server.
The closest I've come to a "routine" shrink is when I had a 1TB database restored from a production backup I needed to remove the user data from and restore to a dev server that had nowhere enough space. That was a manual process I did once a year or so - not an Agent Job - but there are definitely legitimate uses for shrink.
March 11, 2015 at 12:53 pm
I can give an example of a "routine" shrink, but it's not on a production box.
We would take our production database and restore it to a staging machine. There, we'd clean the data and then shrink the database in order to make it as small as possible. Then, we'd take a backup of that database. That's what we would then use on our test and dev boxes.
Note though, the shrink wasn't run over and over on the same database. In fact, it really was only run once on that database. But, it was built into the process and automated, so I'd call it a routine shrink.
But, if the suggestion is, that it's a good, acceptable management practice to have a shrink running on a production database once an hour/day/week, I would object, vehemently. That's absolutely not a "routine" I've ever done in a system I've managed and if I saw it as a consultant, I'd tag it as one of the things to fix. It's dangerous. It hurts performance. And it's just not a good way to get things done.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 16, 2015 at 4:14 am
ZZartin (3/11/2015)
The reason why shrink database should typically be avoided is that if the database grew to that size it indicates it needed that much space for some reason so shrinking it just means it will grow again. Unless you're planning to take a database/file out of active use and just keep it around for archive purposes shrinking for drive space reasons is an oh crap must happen or we're screwed solution, which hopefully isn't routine.....
This times a thousand.
Your database is growing for a reason. Find out why.
In more detail, there are many types of transactional events that can cause the size of the data file(s) to grow, and almost as many that will cause the space to become "free" again afterwards.
Some of these operations are to be expected and are perfectly reasonable. Others may be down to bad coding or a misunderstanding of either the data itself or the business requirements in the first place.
Also, over time, quite simply, things change...
I saw a comment above mentioning that it is difficult to avoid autoshrink/growth when one has dozens of servers and hundreds of databases. Whilst I agree this may seem daunting at first, it is a situation that many of us are faced with daily and we must learn to adapt and cope with it.
To paraphrase an old adage: the best time to understand your growth requirements was two years ago - the second best time is NOW.
Create a discussion group of the technical and business users for each of your databases. Gain an understanding of what future requirements are, and publish a statement of intent, along with timescales, of when you are going to tackle each perceived problem.
Sometimes the solution is simply to buy more space. Sometimes code can be refactored. Sometimes it becomes clear that some data regularly becomes "archive" status and can safely be separated off somewhere so that it does not cause the core database to bloat.
Automatically shrinking/growing your databases will bite you back.
Good luck.
March 16, 2015 at 4:25 am
dan-572483 (3/11/2015)
Two messages up GilaMonster describes a scheduled shrink job she has on a dev server.
Emphasis *dev* server (it's mostly there because the developers have a habit of recreating their DBs from their development tools, their tools for whatever reason set the DBs into full recovery and at the end of the week we have massive log files)
I would never suggest or support a scheduled shrink on a production server. Not under any circumstances. And you took one sentence and ignored all the other parts about not shrinking to handle low drive space problems....
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
March 16, 2015 at 11:16 am
I never suggested that a weekly shrink job on a production database was a sound practice. I just thought that the commandment "NEVER shrink a database!" was a bit too absolute.
March 16, 2015 at 11:57 am
dan-572483 (3/16/2015)
I never suggested that a weekly shrink job on a production database was a sound practice. I just thought that the commandment "NEVER shrink a database!" was a bit too absolute.
File shrinking is a tool and it should be used properly just like any other tool with understanding of how it works and what exactly it does.
What I found more frightening about your post was that it implied that you wouldn't realize you had a drive space issue until your databases stopped working. Which is a problem that routine shrinking won't help with anyways.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply