SQL Server migrations are a headache, ask anyone who’s been through the pain of moving a reasonably large server and I’m sure they will agree. There are all sorts of methods out there for getting the data over to our new server. We can simply backup the databases and restore them to their new home ..read more

This post comes off the back of my last, where I looked at issues caused by explicitly declaring a large number of values in an IN clause. The query processor ran out of internal resources and could not produce a query plan – When You’ve Managed to Confuse SQL With a Crazy IN Clause. My ..read more

Recently I received a cry for help over Teams. The issue was that an application was throwing up the following SQL error, The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very ..read more

“Could you help me, we deleted the database’s transaction log file and now that database is stuck in ‘Recovery Pending’?” This was a panicked call that I received a few weeks ago. “Sure, no problem” said I, “we’ll have to restore back to your last backup” And then things went silent for a while before ..read more

Full documentation on the Undercover Catalogue can be found HERE We’ve spotted a bug in the Databases module where an unprintable ASCII character was being inserted at the beginning of the file paths. This has caused a few problems when generating reports on that field. This is was likely only a problem if you don’t use ..read more

Way back in the mists of time I wrote a post on how to backup SQL server to an S3 bucket using TNTDrive, https://sqlundercover.com/2018/06/18/backup-your-on-premise-sql-server-directly-to-an-aws-s3-bucket/. Back then, if we wanted to backup SQL to S3 we needed to use a third party tool. Since SQL 2022 things have changed and we’ve now got the option to ..read more

We’ve been hearing of a few people getting errors from the latest Undercover Catalogue, Powershell interrogation script. The issue seems to be happening when the scripts tries downloading automatic updates and online module definitions when running the script with older versions of Powershell. A new version of the script has now been uploaded to our GitHub repo, https://github.com/SQLUndercover/UndercoverToolbox/blob/master/SQLUndercoverCatalogue/CatalogueInterrogation.ps1 If you are seeing problems, please use the new, updated script ..read more

Are you struggling with a laggy redo and a build up in the redo queue on your readonly secondaries? Are you suffering with high PARALLEL_REDO_TRAN_TURN waits? Then this magic remedy could cure your ailments. There is a slight misconception with readonly AG replicas that the data should be 100% up to date with what’s on the primary. Even with a synchronous node you can never guarantee that what you see in the database will be 100% up to date with the primary. I discussed why this happens in a different post here https://sqlundercover.com/2019/10/16/availability-groups-and-redo-latency-data-com ..read more

I recently received a complaint that Query Store for a particular database was turned off, which was strange as that particular database has seen quite a few performance issues and I know that I’d ensured Query Store was enabled in the past. No problem, I flicked the switch and Query Store was enabled again. Half an hour or so later and I’m being told that Query Store is again disabled. What’s going on? My first thought was that someone’s fiddling with things. I asked around but everyone denied touching it. So my next ida was to hit up sys.database_query_store_options . SELECT actual_state_ ..read more

This is something that I’ve seen pop up a couple of times on various forums recently, how can we export the results of an XEvent session. So I thought I’d answer the question here. It’s actually a very simple thing to do in SSMS, although the option is not always easy to spot. When we open an XEvent, we’ll also get a nice new toolbar appear with all sorts of options to do with XEvents. There are buttons to set up filters, group data, aggregate data amongst others, but there doesn’t seem to be anything letting up export data. sys.fn_xe_file_target_read_file One option that you’ve got if you’re ..read more