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.
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?
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.