gregdodd

[gregdodd]

SPN's for Linked Servers

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, you provide anyone who can access the source server access to the linked server. Sure, you can lock it down…

[gregdodd]

Create Extended Events Session to Capture a Single Stored Procedure

Need to track a single stored proc in Extended Events? Here's how I do it.

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 was proved to be very difficult. What if, instead of trying to guess what…

[gregdodd]

Selecting from a list of Values

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 VALUES: SELECT * FROM (VALUES(1,2,3)) dataset(col1, col2, col3) Which spits us out a dataset with 1 record and 3…

[gregdodd]

What is Log Send Queue, Redo Queue and Redo Rate in Availability Groups?

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. Log Send Queue This is how much data is sitting on the primary server…

[gregdodd]

Selecting Max Value from multiple columns

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: CREATE TABLE SampleData ( ID INT PRIMARY KEY IDENTITY(1, 1) ,ForeignKey INT NOT NULL ,Value1 INT NOT…

[gregdodd]

Shadow session in RDP

Shadow session in RDP

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: query session /server:[PC Name] Output of sessions Find the session ID with the…

[gregdodd]

Backups failing due to DiskChangeFileSize

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 the shared folder, I can see the…

[gregdodd]

Included Columns on Unique Clustered Indexes are not part of the Unique Constraint

Included Columns on Unique Clustered Indexes are not part of the Unique Constraint

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: CREATE TABLE Greg ( ID INT PRIMARY KEY IDENTITY(1, 1), Col1 INT NOT NULL, Col2 INT NOT NULL, IncludedValue VARCHAR(5)…

[gregdodd]

Distinct List in String_Agg

How do you get a distinct list of strings when using String_Agg?

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 list of Display Names and we’re going to group it based on Location: SELECT…

[gregdodd]

Passing the "Default" value to a Stored Procedure

Passing the “Default” value to a Stored��Procedure

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 NULL then pass in the keyword DEFAULT. The Code assumed that if I had…

[gregdodd]

Clustered Scheduled Tasks tied to a Resource

Clustered Scheduled Tasks tied to a Resource

In a previous post, we saw how you can create a task to run on any available node in a Windows Cluster. That’s great when all of the nodes in the cluster can do the job, but what if you need the task to run on a particular node? Or more importantly, what happens if you need the scheduled task to happen on the node currently hosting a particular process. Maybe it’s a scheduled task that has to…

[gregdodd]

Elecom Huge Trackball

My thoughts after I replaced my Logitech Optical Trackman with an Elecom Huge Trackball

In one of my first fulltime jobs, I started experiencing shoulder and neck pain, and after talking to some medical people and older IT people, I realised that the cause was my mouse. The crappy little mice that come free with your computer are an ergonomic disaster. There’s plenty of research that goes in to what makes a good mouse, but I came to the conclusion that I needed to move away from a…

[gregdodd]

Recording of Adding Parameter info to sp_BlitzWho - Part 3

Recording of Adding Parameter info to sp_BlitzWho – Part 3

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. Greg Dodd – Working on the First Responders Kit – YouTube

[gregdodd]

Recording of Fixing bug in sp_DatabaseRestore from SQL Server First Responder Kit

Recording of Fixing bug in sp_DatabaseRestore from SQL Server First Responder Kit

I found a bug in the sp_DatabaseRestore code in the First Responder kit, so I decided to fix it and while I did I found another bug. Watch along on the recording.

[gregdodd]

Recording of Adding Parameter info to sp_BlitzWho – Part 2 I did some more work on sp_BlitzWho recently. Check out the clip on YouTube below. If you missed part 1, you can find it here.

[gregdodd]

Recording of Adding Parameter info to sp_BlitzWho I did some work on sp_BlitzWho recently. Check out the clip on YouTube. Part 2 will hopefully come soon:

[gregdodd]

When are different Strings equal?

When working on the First Responder Kit recently, I needed to compare two strings and came across a bit of an oddity on how SQL compares strings. Let’s check it out.

First, put the following stored proc in to your database:

CREATE OR ALTER PROCEDURE AreStringsTheSame( @String1ToCheck NVARCHAR(10), @String2ToCheck NVARCHAR(10) ) AS BEGIN IF @String1ToCheck = @String2ToCheck BEGIN SELECT…