xp_cmdshell is an extended SQL stored proc that allows users to run Windows command prompt commands from within SQL. Sound scary? It might, but is xp_cmdshell really a security risk? Well a lot of people think so, many DBAs and IT departments will insist that it's always disabled and many auditors and pen testers will... Continue Reading →
Using SQL Alerts to Spot Suspicious Activity in SQL
SQL doesn't really give us too many tools out of the box to allow us to spot when someone may be up to no good. We can look at the number of failed login attempts in SQL's error log. If you start seeing multiple login attempts, especially for SA or any other suspicious looking user... Continue Reading →
Encryption, Comparing your Options in SQL Server
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 →
Database Mirroring login attempt failed with error: ‘Connection handshake failed. An OS call failed: (80090302) 0x80090302(The function requested is not supported).
Photo by Pixabay on Pexels.com Here's a quick one for today and is an issue that had me stumped for a while. It's not one that I'd come across before and there isn't really all that much out there on the internet about it. The Issue as I Saw It So, what was the issue?... Continue Reading →
DELETE TOP x rows with an ORDER BY
Photo by Pixabay on Pexels.com This was an interesting question that I was asked yesterday and something that I'd never really thought of before. Can you delete the top x number of rows based on an ORDER BY? Why would you want to do that? Well let's just assume that we have a 'people' table... Continue Reading →
Attach All SQL Datafiles in a Directory
Have you ever had the need to attach a large number of database in one go? There's no way to attach multiple databases in SSMS or via script, so you're probably going to be left with the slow, arduous task of doing them one by one. I recently had to deal with a DR situation... 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 →
Resource Hog! What’s my busiest database?
Image by Belinda Cave from Pixabay Imagine that we're seeing very heavy load on our SQL server, be it CPU or IO and we need to find out which database is the driver. I'm sure we've all been there at some point, sometimes it's easy to tell. We might only have a handful of databases... Continue Reading →
BlitzBlockingChain, Getting Blocking Chains from sp_BlitzWho
Photo by Joey Kyber on Pexels.com I'm going to assume that most people will have at least heard of Brent Ozar's brilliant First Responder Kit. If you haven't then you've been missing out on some serious (and FREE) SQL troubleshooting goodness and I'd suggest you check it out. Now I use these scripts all 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 →