DBA Fundamentals: Maintenance: Shrinking Files

When you've got a lot of empty space inside a database, you can use DBCC SHRINKDB or SHRINKFILE in order to downsize the file and free up space on your drives. But should you? I'll explain how those commands work, why they make performance worse, and give you alternatives.

[@user-xp5sk8ts3q]

Thanks for the interesting story about the architecture of hard drives.

[@kennethgardzinski]

Good information as usual. Thank you 😊.

[@kelleyprebil8571]

Great explanation! I accidentally shrunk the log file on one of my Production servers as I was trying to shrink it on my sandbox server actually and didn't realize I was connected to the wrong server. Oops. I've only had to shrink that Production server's log file once when someone decided to do a massive delete + insert instead of just updating the data which blew up the log file size to WAY bigger than normal and WAY bigger than the data file. Tripled the size of the backups too.

[@totanlj18]

Thanks God you do only a Shrink on that server!

[@clasmir5281]

Great content! Almost done with this bag of popcorn!

[@samdani870]

Much clear on shrink. Can you make a video of lock, block, and deadlock with a bunch of real examples please.

[@BrentOzarUnlimited]

Sure, I have - check out my Mastering Index Tuning and Mastering Query Tuning classes where we spend about 3 hours in total on blocking and deadlocking. To learn more, go to BrentOzar.com and click Training at the top.

[@avanthasiriwardana]

perfecto :)

[@Vibestr]

Hello Brent
Thank you for the explanation. It helped me understand why shrinking paired w/ rebuilding indexes is a fruitless cause. Is the shrinking that you describe an example of notruncate because i was reading a truncate shrink doesn't move any files over but simply drops ("deallocates") the space at the end (and henceforth it is much more faster process)? Would using a truncate shrink be good since it doesn't reshuffle the database and perhaps there would be no need to perform a defrag? Or is having that extra unused allocated space not such a bad thing since it can make future inserts easier?

[@paulntumbong9568]

Good analysis sir, what script can you use to know what exactly is causing the log file growing big

[@TheBrentOzar]

Thanks! I cover that in my How I Use the First Responder Kit class.

[@lirusme2461]

How about tempdb datafile that continue growth because of verrion store? how to identification what transaction process and query that running?

[@BrentOzarUnlimited]

Check out my class Fundamentals of TempDB.