There’s lots of problems with linked servers and lots of reasons to not use it, but let’s assume you want to join 2 servers together, how do you manage the security? The main issue with the security is when you map a login on the source server to a destination server. When you do this, […]
Do you ever have a stored procedure that you know is performing badly and needs tuning, but you’re struggling to capture when it is run or what parameters were passed in?I had this problem recently, I knew that a particular stored procedure was running slowly for some parameters, but figuring out what the bad combination […]
A quick description of 3 metrics that SQL tracks in Availability Groups. These metrics are important when evaluating the health of your Availability Group, and knowing what sort of data loss you might face in a failover. Remember, just because you told SQL to make your AAG Synchronous, doesn’t mean you won’t have data loss. […]
Have you ever had 2 columns in a table that you need to select the maximum value from? Not the maximum value from each column, but the biggest value after comparing the columns? Usually where I have this problem, I’d use a CASE statement, but there’s another way. Let’s setup some test data: So how […]
I hit an error recently on a server that caused backups to fail. The database was backing up to a UNC path. Looking in the SQL Log file and Event Viewer, I found the following error: The operating system returned the error ‘121(The semaphore timeout period has expired.)’ while attempting ‘DiskChangeFileSize’ on ‘\\uncpath\folder\databasename.bak’. Looking in […]
I needed to add a Unique Constraint today to a table. We could go ahead and just add a constraint, but the data I wanted to constrain on was already indexed. Could I just make that a Unique Index and be done with it? Let’s find out with the following table: Nice simple table. Let’s […]
SQL introduced the new STRING_AGG feature in SQL 2017, and it works just like it suggests it would: it’s an aggregate function that takes all of the string values and joins them together with a separator. To see how it works, I’m using the StackOverflow users table, and let’s say we want to create a […]
If you’ve done work with stored procedures, you are probably aware that stored procedures have parameters, and that the parameters can be defaulted when you declare them. I was recently caught out due to some application code that checked when a parameter was specified for a stored procedure, if the value for the parameter was […]
How do you left join to a table ONLY if it inner joins to another?
After a bug report from my previous work on sp_BlitzWho, I’m finally fixing it properly. Check out the clip below on YouTube, or if you want to see the original work, check out part 1 and part 2.
