Shrink all user database files

Question

Shrink all user database files

boubkhaled

Valued Member

Points: 71
More actions
December 27, 2017 at 6:42 pm

#334179

Comments posted to this topic are about the item Shrink all user database files

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

I appreciate you sharing but, be advised, shrinking databases every day is a form of "Death by SQL".

1. It can and usually will cause a type of fragmentation that can absolutely cripple your database for performance. The only way to recover from that is to do the necessary index maintenance and that can cause wanton growth of the MDF file if you use REBUILD or wanton growth of the LDF file if you use REORGANIZE.
2. Except when there has been accidental and incredible growth of either the MDF or LDF files, shrinking the database on a daily basis is totally fruitless unless you find out what is causing the excessive growth. It's a whole lot like washing your clothes and then drying them on a clothesline in the rain with the occasional dog lifting his leg on the basket you also left outside.
3. Shrinking the databases takes valuable time away from more useful and more critical maintenance such as finding stats the need to be updated and then updating them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63428 More actions · Answer 2

Why would you want to do that? I can see shrinking a database if you have to move it and space is at a premium. But that's hopefully a rare occurrence. One would be much better tracking growth rates and projecting when a server is projected to run out of space at the current growth rate, and then addressing the problem proactively.

HappyGeek SSCoach Points: 18726 More actions · Answer 3

Jeff Moden - Wednesday, December 27, 2017 7:07 PM
I appreciate you sharing but, be advised, shrinking databases every day is a form of "Death by SQL".
1. It can and usually will cause a type of fragmentation that can absolutely cripple your database for performance. The only way to recover from that is to do the necessary index maintenance and that can cause wanton growth of the MDF file if you use REBUILD or wanton growth of the LDF file if you use REORGANIZE.
2. Except when there has been accidental and incredible growth of either the MDF or LDF files, shrinking the database on a daily basis is totally fruitless unless you find out what is causing the excessive growth. It's a whole lot like washing your clothes and then drying them on a clothesline in the rain with the occasional dog lifting his leg on the basket you also left outside.
3. Shrinking the databases takes valuable time away from more useful and more critical maintenance such as finding stats the need to be updated and then updating them.

+ 10000

...

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

Jeff Moden - Wednesday, December 27, 2017 7:07 PM
I appreciate you sharing but, be advised, shrinking databases every day is a form of "Death by SQL".
1. It can and usually will cause a type of fragmentation that can absolutely cripple your database for performance. The only way to recover from that is to do the necessary index maintenance and that can cause wanton growth of the MDF file if you use REBUILD or wanton growth of the LDF file if you use REORGANIZE.
2. Except when there has been accidental and incredible growth of either the MDF or LDF files, shrinking the database on a daily basis is totally fruitless unless you find out what is causing the excessive growth. It's a whole lot like washing your clothes and then drying them on a clothesline in the rain with the occasional dog lifting his leg on the basket you also left outside.
3. Shrinking the databases takes valuable time away from more useful and more critical maintenance such as finding stats the need to be updated and then updating them.

Agreed 1000%. Except in rare instances, it's not worth the space you get back. When it is worth the space, you give a bunch of it away by doing the rebuilds. I think the wasted space is generally about 120% the size of your biggest table.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

pierre.bonhomme SSC Journeyman Points: 89 More actions · Answer 5

pierre.bonhomme

SSC Journeyman

Points: 89

January 2, 2018 at 3:34 am

#1974026

Why do that ??? Buy some HD

Perry Whittle SSC Guru Points: 233853 More actions · Answer 6

short answer is don't!!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

RandomStream Hall of Fame Points: 3735 More actions · Answer 7

RandomStream

Hall of Fame

Points: 3735

January 2, 2018 at 5:23 pm

#1974150

Jeff's right but don't be discouraged.

Arsh SSCertifiable Points: 6345 More actions · Answer 8

Not encouraged , unless the downside of this resulting in fragmentation and other stuff put by Jeff , can be dealt with. The lower costs of storage these days make it all the more avoidable. Also , SQL server reuses the empty space. Probably when one is sure that large amounts of spaces is not going to be used for long time. Overall , shrinking the database files is a no-no.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

RandomStream - Tuesday, January 2, 2018 5:23 PM
Jeff's right but don't be discouraged.

If you're talking about not being discouraged from writing articles, I absolutely agree! If contrary opinion were a deterrent, I'd have never made it past my first article. I actually embrace contrary opinion (although I embrace and value contrary proof in the form of code much more) because it normally causes discussions that flush out some really great ideas or confirms that some ideas aren't so great.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)