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 […]
Sometimes you have a list of values or parameters that you’d like to run a select statement over the top of to get a result. There’s a few ways of doing this, usually I see people create a Temporary Table and insert the data. But is there an easier way? Yes, we can select from […]
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 […]
Sometimes I need to shadow a user’s session and the tool we usually use sometimes doesn’t work. In those cases, we can try connecting to the user’s session using Microsoft Remote Desktop. Here are the steps: Run the following command to get the users session which will give an output of all active sessions: Find […]
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 […]
