Before this post gets started, let’s get past the title. I know what many of you are thinking: what in the name of Paul Randal would make someone have an EXTRA log file? The short answer is: desperate times.

True, there’s no advantage to having more than one log file, but sometimes that one file grows suddenly and fills up the drive in the middle of a transaction and you’re stuck with those dreaded “THE DATABASE IS DOWN!!!” tickets until that transaction finishes. So, in the heat of the moment, you hit the panic button and create ANOTHER log file on a different drive.

Then, minutes, hours, or even weeks later, you want to put the universe back in order by resizing the original log file and removing the extra one. But what if you find you can’t remove that extra one, no matter what you try to do?

That’s what happened to me. Let me show you how I solved this riddle.

Continue reading →

This is not a post about the “optimize for ad hoc workloads” setting on your favorite SQL Server instance, but that is a good place to start.

Lots of folks that I’m too busy/tired/lazy to link to have promoted using this setting as a best practice for keeping your plan cache from bloating up like Violet Beauregarde in the Chocolate Factory. The theory goes, if a query is just run once, why do you even care about keeping the execution plan? Just bin it.

That said, this IS a post about Query Store, that nifty tool in the newer versions of SQL Server. It’s got a lot of functionality, such as persisting nearly all plans, which includes those ad hoc queries whose plans you probably didn’t want in the first place.

Yeah, unfortunately, there’s nothing like “optimize for ad hoc workloads” for Query Store. But for those of you who don’t want Query Store drowning in all your ad hoc nonsense, let me share a little something.

Continue reading →