DBA Fundamentals: Maintenance: Shrinking Files

[@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!

[@Ренат-ц8ж]

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

[@ntobergta]

In the situation where I have 1.7TB free.. I love that your Gotta Shrink solution is exactly what I came up with except my anal retentiveness makes me want to move them back to have clean, happy files 🙂

[@ssousa97]

We have a specific ( i think) scenario where we have sharded tables over days. Think of it as a history log for events that happend on that day. So each day we have a new table (shard) Some events are non-persistent, so we have jobs that delete them after X amount of days. So what happens is that we are "freeing up" space on tables that won't be used again, other than querying for historical analysis. So the cycle is: delete non-persistent events, reorganize indexes and then shrink the database file to regain the space. How bad is it ? Or that's "ok" considering that we won't be inserting data in the past tables ?

[@clasmir5281]

Great content! Almost done with this bag of popcorn!

[@kennethgardzinski]

Good information as usual. Thank you 😊.

[@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.

[@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?

[@JyotiYadav-gq1fs]

Hi. Can you please tell me is it right to schedule shrink command in ssms...because i was planning to do. My manger asked me to fix

[@BrentOzarUnlimited]

Watch the video. Cheers!

[@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.

[@avanthasiriwardana]

perfecto :)