The question of encryption seems to be coming up a lot recently. I've had a number of people asking me about how to go about encrypting SQL Server. SQL can encrypt our data at a number of different levels and gives us a quite a few options when doing so. I want to use this... Continue Reading →
Opening your SQL Backup Folder using Powershell
Sometimes I find remembering where a particular server sends its backups to a nightmare. You might have servers backing up to different locations, you might have different locations for individual databases and different locations for your fulls, diffs and logs. You might be trying to get your head around a customer's set up, where the... Continue Reading →
Which Processes are waiting on a Spinlock?
Spinlock contention is always a real headache to deal with. I recently saw an issue when spinlock contention on SOS_CACHESTORE was making the server virtually unresponsive. The issue was very intermittent with no obvious pattern but the assumption is that it was caused by a particular process in the application. Finding what that process was... Continue Reading →
External Temp Tables Preventing Plan Reuse in Stored Procedures
Here's an interesting issue that recently came up. We were seeing very high compilations and recompilations on a server to the point that it started causing us some very serious issues (admittedly this wasn't the sole issue but it was certainly a contributing factor, the other factors were also very interesting so I might look... Continue Reading →
sp_RestoreScript 1.9 – a dirty little bug squished!
It look like we had a little bug in sp_RestoreScript that was putting a rogue 0 after the backup file name. That's now been fixed in 1.9. For information and documentation please visit https://sqlundercover.com/2017/06/29/undercover-toolbox-sp_restorescript-a-painless-way-to-generate-sql-server-database-restore-scripts/ Keep an eye out for sp_RestoreScript 2.0 which is currently in development and will bring a while load of new features... Continue Reading →
My Application is Getting SQL Timeout Errors, But What Query is the Problem?
This is something that comes up time and time again. A developer or member of the service desk comes up to me and says "SQL's timing out, you see what the query is?". Well I'll tell you now that SQL's not timing out your query, SQL will happily run a query for a year and... Continue Reading →
Inspector V2.6 now available
As always the code can be found here in our github repoWe updated our sample report hereWe forgot to mention V2.5! We added the ability to add Thresholds per drive for the drivespace module.#270 #279 - Improved the server settings module, if server changes have been made between collections this module will now pick them... Continue Reading →
When Read Committed Snapshot Goes Bad – Version Store Filling TempDB
We all had those calls, it's the middle of the night and we get that phone call that a customer is getting error messages due to their tempDB being full. My first step is usually going to be to have a look and see what sessions have allocations in TempDB, we can do that quickly... Continue Reading →
Encryption in SQL Server #1 – Column Level Encryption
For one reason or another data security and encryption has been coming up quite a bit in my day to day work recently and I've started to realise that it seems to be one of those things that people aren't all that aware of. As I've been meaning to write a little series on encryption... Continue Reading →
Generate a Random Number for Each Row in a Query {a better way}
A couple of years ago I wrote a post on how to generate a random number in SQL Server. https://sqlundercover.com/2017/06/22/generating-random-numbers-for-each-row-over-a-specified-range-and-other-funky-stuff-you-can-do-with-them/ It's ok and it works, it's also a method that I used for quite a while. Why not use RAND() I've heard people say, well the issue with RAND() is that it will generate the... Continue Reading →