DBA Darwin Awards: Log File Edition
Right after the DBA says “Uh oh,” they call in the consultants. We get to see some really amazing acts of career suicide.
You wouldn’t believe some of the stuff we’ve seen, and some of these things are probably happening in your environment right now. In this session, we’ll talk about some of the most common ways to shoot yourself in the foot, and we’ll show you how to put the gun back in the holster.
If you’re a production DBA of any experience level, do yourself a favor and look for these common mistakes before they call in the consultants. We’ll discuss recovery modes, bulk inserts, VLFs, replication, copy_only backups, and of course, shrinking – all so you can learn what you’re doing wrong before your boss has to hear it from the outsider.
https://www.youtube.com/watch?v=YnKW-HLuF7U
Links We Discussed
Automatically Shrink Your Transaction Logs – Jeremiah Peschka shares a diabolically dangerous idea to shrink your log files as soon as they grow.
A Busy/Accidental DBA’s Guide to Managing VLFs – Dave Levy explains the basics of Virtual Log Files, gives you a script to check how many you’ve got, and shows how to fix the problem.
Performance Impact of VLFs – Linchi Shea tested how delete/insert/update (DUI) performance is affected by 20,000 VLFs, and then follows up with an OLTP test too.
Minimally Logging Bulk Load Inserts – Greg Robidoux has a great chart explaining how to get minimally logged inserts depending on whether you’ve got clustered indexes, nonclustered indexes, existing data, etc. Also, in the video, I don’t explicitly say that these also work in simple and full recovery modes, but they do. (I wanted to keep things simple – no pun intended – for the 30-minute session.)
Microsoft Data Loading Performance Guide – Thomas Kejser, Peter Carlin, and Stuart Ozer discuss the improvements of trace flag 610 to get minimally logged inserts in 2008.
