Encryption, Comparing your Options in SQL Server
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
2w ago
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 post to put together a matrix so you can easily see which method of encryption suits your purpose. I’ll look in to each method in more detail in a future series of posts. Transport Layer Security Transparent Data Encryption (TDE) Backup Encryption Cell Level Encryption Always Encrypted Encrypts Data at Rest Enc ..read more
Visit website
Database Mirroring login attempt failed with error: ‘Connection handshake failed. An OS call failed: (80090302) 0x80090302(The function requested is not supported).
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
2M ago
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? I was setting up an AG, all pretty standard stuff using the wizard in SSMS. I went through the usual setup and when I got to the end, everything seemed to create as I’d expect it, the only issue and first hint that there was an issue was that the wizard just sat there spinning when trying to join a database to the secondary. I ev ..read more
Visit website
DELETE TOP x rows with an ORDER BY
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
5M ago
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 and some strange bug in the application has cause the need to delete the top 10 oldest females for whatever reason (I know, it’s very contrived example and not the situation that my colleague was facing but it’ll do to illustrate the point). We have a table that looks something like this…. It’s easy enough ..read more
Visit website
Attach All SQL Datafiles in a Directory
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
6M ago
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 (I won’t go into details of what happened just yet as things are still quite sensitive, but I might look at it at some point in the future) where I faced exactly that issue. For one reason or another I needed to attach several hundred databases quickly. I didn’t fancy doing that via SSMS or script each one individually so ..read more
Visit website
Opening your SQL Backup Folder using Powershell
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
8M ago
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 backups make no logical sense at all. Whatever you’re up to, at some point, for some reason you’re going to need to access your backup location to get at the files. I used to figure out where that was by querying msdb if I was on a customer’s kit or if onsite w ..read more
Visit website
Resource Hog! What’s my busiest database?
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
9M ago
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 on the server with one known to be the heaviest utilised. But sometimes things might not be so obvious, there could be a large numbers of databases or no obvious resource hog. In those instances we need some way to figure out what how much time each database is spending on the CPU if that’s what we’re interested in o ..read more
Visit website
BlitzBlockingChain, Getting Blocking Chains from sp_BlitzWho
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
1y ago
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 time when checking out SQL Servers. Sometimes I run them as and when I need them but one of the most handy uses for them is to have them running from an Agent job and logging to tables on a regular basis (I usually run them every 15 minutes but might run them more or less regularly as neede ..read more
Visit website
Which Processes are waiting on a Spinlock?
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
1y ago
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 the tricky part, they don’t show up as waiting tasks so your usual scripts for looking for waiting processes may not work here. The place that we can see works who are waiting on a spinlock is in sys.dm_os_workers, specifically the is_sick column. If that ..read more
Visit website
External Temp Tables Preventing Plan Reuse in Stored Procedures
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
1y ago
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 at those in another post). After looking in the plan cache we could see a very high number of single use plans for a particular stored procedure. Now as you probably know, SQL will usually cache an execution plan and use it over and over whenever a particular query runs ..read more
Visit website
Sp_RestoreScript 1.9 – a dirty little bug squished!
SQL Undercover – The Home of the Undercover DBAs
by David Fowler
1y ago
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 and flexibility ..read more
Visit website

Follow SQL Undercover – The Home of the Undercover DBAs on Feedspot

Continue with Google
OR