sp_whoisactive @get_locks parameter xml error

Question

I often receive this error when running the following command

sp_whoisactive @get_locks = 1

Msg 6841, Level 16, State 1, Procedure sp_WhoIsActive, Line 4287 FOR XML could not serialize the data for node 'Lock/@resource_description' because it contains a character (0x0001) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive. Warning: Null value is eliminated by an aggregate or other SET operation.'

I believe it is related to the grabbing the xml of the locks being held, however I notice it happens frequently when there is blocking occurring. Is there any way around this or is it a known issue? Running SQL Server 2012, and latest version v11.32 of sp_whoisactive.

Edit

Following the suggested option, I’m having trouble locating this data. I mostly encounter the message when there is blocking on the server, once I encounter the message I take the parameter off and try to see which object it may be coming from, but it’s difficult because there are many threads running. Is there any easier way of going about it? I initially didn’t think it was data but an object name, because when I see the locks xml it normally only contains object names, I didn’t realize actual data is being put in there that may be resulting in this message?

Edit 2

Following the advice from Erik, I was able to track down the object with the SOH character outputting the results of the #locks table. However ultimately what I found most useful is using the work-around of get_task_info = 2 and get_additional_info = 1, when running these parameters I do not receive the error message and still receive the information I need. I am still going to explore trying to add that character into the replace statements in sp_whoisactive so I can still use the get_locks parameter in the case I need to see the full XML.

Answer 1

I can't fully answer your question as to which object is causing the issue, but I can offer you some troubleshooting, and possible workarounds.

Note that I can't distribute a "fixed" script to you because of its licensing, but you're free to try the edits locally.

Troubleshooting

If you ctrl+f through sp_WhoIsActive for @get_locks = 1, you'll hit this code section:

A bit further down here, you'll see that a rather complicated select inserts into a table called #locks:

If you scroll down to line 1670 or so (assuming you have line numbers enabled in SSMS), you'll reach the end of the SELECT INTO that populates it. You could try adding a simple SELECT * FROM #locks; here to see what ends up in the table. This could at least potentially point you to the correct object.

Workarounds

One workaround, without changing any code might be to run the procedure like this:

EXEC sp_WhoIsActive 
    @get_task_info = 2, 
    @get_additional_info = 1;

The intent of these parameters is documented here.

If that doesn't run without error, you're stuck doing a ctrl+f for FETCH NEXT FROM locks_cursor. This will bring you to the section of code where your error pops up.

Within this block of cursor code, you'll find a dynamic query, a catch block, and an update with nested REPLACE blocks to clean out bad characters (except yours, it appears). You'd have to add an additional nested REPLACE across all of them with NCHAR(0x0001) here I believe, in order to fix it. I haven't tested this, so YMMV.

Hope this helps!