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

Full documentation on the Undercover Catalogue can be found HERE It’s been a while since the Undercover Catalogue last received an update but having had a few of those moments recently when I thought to myself ‘I really wish I was capturing that data’, so I decided to roll a few changes into the Catalogue. If you’ve got automatic updates switched on, you’ll most likely already have received 0.4.4. If not, you’ll need to head over to GitHub and download and run https://github.com/SQLUndercover/UndercoverToolbox/blob/master/SQLUndercoverCatalogue/Updates/Catalogue_UD044.sql Database Last Access ..read more

Your boss walks up to you one morning and says, “Hey, I wanna list of all of our databases and when they were last accessed”. If you’ve got some sort of auditing switched on or a trace or xevent catching this sort of info you might be ok, but I’m betting you don’t have any of that. That’s cool, it’s not something that I tend to monitor as standard either. But if you’re not monitoring it, is there any way that you can get at that info? SQL doesn’t really give us anything obvious here, there’s no magic, ‘last_access_date’ column in sys.databases or anywhere else for that matter. There may be a ..read more

Presenting you with an updated version of our sp_snapshot procedure, to easily create database snapshots. This new version adds more flexibility to the procedure, allowing you to specify the snapshot’s suffix, add a timestamp and alter the path that the snapshot files are taken to. Parameters @DatabaseList  – a comma delimited string of database names, allows wildcards @Suffix – adds a suffix to the snapshot name, giving it the format <databasename>_<suffix>, DEFAULT value is ‘snapshot’ @FilePath – specify a file path where the snapshot files will be saved to. If left blank ..read more

Picture this, you’re happily backing up your database to a Azure blob storage until suddenly it starts mysteriously failing with the error… Write to backup block blob device https://****** failed. Device has reached its limit of allowed blocks. What’s going on, nothing’s changed?! Well, it has, the database has grown and the issue that you’re bumping into here is that there’s a limit to the number of blocks we can upload to blob storage. That limit is 50,000. So what does that mean for our backups? Well, essentially this puts a limit on the size of a SQL backup file. But there are a few thing ..read more

Since witnessing a rather nasty cyber attack around a year ago, I’ve been thinking quite a bit about security. Do we really know how secure our SQL Servers are? Penetration testing is a great way to find out where our weaknesses and vulnerabilities are. Ideally you probably want to be getting regular pen tests conducted by external companies (although in my experience, some are better than others. I’ve known some who argue totally pointless issues and miss glaring holes which I know exist, but that’s a whole different story) but wouldn’t it be useful if we could conduct some of these tests ou ..read more

All code in this post can be found in our GitHub repo https://github.com/SQLUndercover/UndercoverToolbox This is a question that’s come up twice this morning, firstly where can we find a history of database snapshots and secondly where can we find a history of restores from snapshot? Frustratingly, SQL doesn’t make this at all easy for us and if this is something that you want to record, you’re going to have a do a little extra work. Let’s take a look at each part in turn. Getting a History of Database Snapshots Taken In my head, a database snapshot is a form of backup. At least that’s how I ..read more