Sql-server – sp_whoisactive @get_locks parameter xml error

sp-whoisactivesql server

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.

Best Answer

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!

Related Solutions

Sql-server – SQL Server 2005 database corruption. Advise

As you have an old backup with the correct schema, the problem database is online and you've successfully queried several tables, I'd be inclined to try get a dump of the raw data as fast as possible.

Shut down the applications accessing the database.
BCP export the data, table by table to files. As disk corruption could be the source of your woes, export the data to a portable drive or to a fileshare on another machine.
Restore your old backup to a known good server.
Depending on the complexity of the database schema either truncate the data from your restored backup or script out the schema and create a new empty database.
BCP the data back in.

The data may contain inaccuracies, it may be missing rows or contain spurious values. But with any luck you'll end up with more than was in the month old backup.

Sql-server – How to avoid using Merge query when upserting multiple data using xml parameter

Whether the source is XML or a TVP does not make a huge difference. The overall operation is essentially:

UPDATE existing rows
INSERT missing rows

You do it in that order because if you INSERT first, then all rows exist to get the UPDATE and you will do repeated work for any rows that were just inserted.

Beyond that there are different ways to accomplish this and various ways to tweak some additional efficiency out of it.

Let's start with the bare minimum. Since extracting the XML is likely to be one of the more expensive parts of this operation (if not the most expensive), we don't want to have to do that twice (as we have two operations to perform). So, we create a temp table and extract the data out of the XML into it:

CREATE TABLE #TempImport
(
  Field1 DataType1,
  Field2 DataType2,
  ...
);

INSERT INTO #TempImport (Field1, Field2, ...)
  SELECT tab.col.value('XQueryForField1', 'DataType') AS [Field1],
         tab.col.value('XQueryForField2', 'DataType') AS [Field2],
         ...
  FROM   @XmlInputParam.nodes('XQuery') tab(col);

From there we do the UPDATE and then the INSERT:

UPDATE tab
SET    tab.Field1 = tmp.Field1,
       tab.Field2 = tmp.Field2,
       ...
FROM   [SchemaName].[TableName] tab
INNER JOIN #TempImport tmp
        ON tmp.IDField = tab.IDField
        ... -- more fields if PK or alternate key is composite

INSERT INTO [SchemaName].[TableName]
  (Field1, Field2, ...)
  SELECT tmp.Field1, tmp.Field2, ...
  FROM   #TempImport tmp
  WHERE  NOT EXISTS (
                       SELECT  *
                       FROM    [SchemaName].[TableName] tab
                       WHERE   tab.IDField = tmp.IDField
                       ... -- more fields if PK or alternate key is composite
                     );

Now that we have the basic operation down, we can do a few things to optimize:

capture @@ROWCOUNT of insert into temp table and compare to @@ROWCOUNT of the UPDATE. If they are the same then we can skip the INSERT
capture the ID values updated via the OUTPUT clause and DELETE those from the temp table. Then the INSERT doesn't need the WHERE NOT EXISTS(...)
IF there are any rows in the incoming data that should not be synced (i.e. neither inserted nor updated), then those records should be removed prior to doing the UPDATE

CREATE TABLE #TempImport
(
  Field1 DataType1,
  Field2 DataType2,
  ...
);

DECLARE @ImportRows INT;
DECLARE @UpdatedIDs TABLE ([IDField] INT NOT NULL);

BEGIN TRY

  INSERT INTO #TempImport (Field1, Field2, ...)
    SELECT tab.col.value('XQueryForField1', 'DataType') AS [Field1],
           tab.col.value('XQueryForField2', 'DataType') AS [Field2],
           ...
    FROM   @XmlInputParam.nodes('XQuery') tab(col);

  SET @ImportRows = @@ROWCOUNT;

  IF (@ImportRows = 0)
  BEGIN
    RAISERROR('Seriously?', 16, 1); -- no rows to import
  END;

  -- optional: test to see if it helps or hurts
  -- ALTER TABLE #TempImport
  --   ADD CONSTRAINT [PK_#TempImport]
  --   PRIMARY KEY CLUSTERED (PKField ASC)
  --   WITH FILLFACTOR = 100;


  -- optional: remove any records that should not be synced
  DELETE tmp
  FROM   #TempImport tmp
  INNER JOIN [SchemaName].[TableName] tab
          ON tab.IDField = tmp.IDField
          ... -- more fields if PK or alternate key is composite
  WHERE  tmp.ModifiedDate < tab.ModifiedDate;

  BEGIN TRAN;

  UPDATE tab
  SET    tab.Field1 = tmp.Field1,
         tab.Field2 = tmp.Field2,
         ...
  OUTPUT INSERTED.IDField
  INTO   @UpdatedIDs ([IDField]) -- capture IDs that are updated
  FROM   [SchemaName].[TableName] tab
  INNER JOIN #TempImport tmp
          ON tmp.IDField = tab.IDField
          ... -- more fields if PK or alternate key is composite

  IF (@@ROWCOUNT < @ImportRows) -- if all rows were updates then skip, else insert remaining
  BEGIN
    -- get rid of rows that were updates, leaving only the ones to insert
    DELETE tmp
    FROM   #TempImport tmp
    INNER JOIN @UpdatedIDs del
            ON del.[IDField] = tmp.[IDField];

    -- OR, rather than the DELETE, maybe add a column to #TempImport for:
    -- [IsUpdate] BIT NOT NULL DEFAULT (0)
    -- Then UPDATE #TempImport SET [IsUpdate] = 1 JOIN @UpdatedIDs ON [IDField]
    -- Then, in below INSERT, add:  WHERE [IsUpdate] = 0

    INSERT INTO [SchemaName].[TableName]
      (Field1, Field2, ...)
      SELECT tmp.Field1, tmp.Field2, ...
      FROM   #TempImport tmp
  END;

  COMMIT TRAN;

END TRY
BEGIN CATCH
  IF (@@TRANCOUNT > 0)
  BEGIN
    ROLLBACK;
  END;

  -- THROW; -- if using SQL 2012 or newer, use this and remove the following 3 lines
  DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage, 16, 1);
  RETURN;
END CATCH;

I have used this model several times on Imports / ETLs that either have well over 1000 rows or maybe 500 in a batch out of a total set of 20k - over a million rows. However, I have not tested the performance difference between the DELETE of the updated rows out of the temp table vs just updating the [IsUpdate] field.

Please note regarding the decision to use XML over TVP due to there being, at most, 1000 rows to import at a time (mentioned in the question):

If this is being called a few times here and there, then quite possibly the minor performance gain in TVP might not be worth the additional maintenance cost (needing to drop the proc before changing the User-Defined Table Type, app code changes, etc). But if you are importing 4 million rows, sending 1000 at a time, that is 4000 executions (and 4 million rows of XML to parse no matter how it is broken up), and even a minor performance difference when executed only a few times will add up to a noticeable difference.

That being said, the method as I have described does not change outside of replacing the SELECT FROM @XmlInputParam to be SELECT FROM @TVP. Since TVPs are read-only, you wouldn't be able to delete from them. I guess you could simply add a WHERE NOT EXISTS(SELECT * FROM @UpdateIDs ids WHERE ids.IDField = tmp.IDField) to that final SELECT (tied to the INSERT) instead of the simple WHERE IsUpdate = 0. If you were to use the @UpdateIDs table variable in this manner, then you could even get away with not dumping the incoming rows into the temp table.