Lync issue: export-csuserdata failed with “Data in NULL”

It started when a backup script that I implemented logged a failure on the export-csuserdata process. Running the  powershell command isolated would generate the same error text: “Data is Null. This method or property cannot be called on Null values.”
export-userdataerrorSo, we have a sort of user data corruption and not enough clues to locate which of the 4’000 accounts was. The export-csuserdata generated an incomplete zip file but comparing with the last complete backup wasn’t enough to compare and find out what account was generating the error. With some brainstorming and scripting I finally identified the user, and using the export with the userfilter parameter generate the error.

Time to reverse-engineering the root cause:

Step #1: A network and then a SQL trace on the Backend database, show that the export-csuserdata calls a store procedure of the rtcxds named ‘XdsBackupAllItems’ with the user’s SID on the parameter
export-sqltrace

Step #2: Executing the store procedure confirmed the output data with two NULL values

SQLCMD -S Server249\LYNCCORE -d rtcxds -Q "exec dbo.XdsBackupAllItems @_TenantId='92e2a9d7-41d7-4c43-bbba-46122f5d5ab6', @_ExtensionParameter=null"
 DocumentItemData 
 ----------------------------------------------------------------------------------------------------------------------------------------------------
 <app:DocItem xmlns:app="urn:schema:Microsoft.Rtc.Management.Xds.AppLayer.2008" Name="urn:lcd:M****_K*****@s******.com" ItemId="92E2A9D7-41D7-4C43-BBBA-46122F5D5AB6" Owner="04DDEDCE-ED20-514D-8586-58BEE1904EE8" OwnerPool="pool0101.s*****.com" Signatu 

 <app:DocItem xmlns:app="urn:schema:Microsoft.Rtc.Management.Xds.AppLayer.2008" Name="urn:confs:M****_K*****@s******.com" ItemId="92E2A9D7-41D7-4C43-BBBA-46122F5D5AB6" Owner="04DDEDCE-ED20-514D-8586-58BEE1904EE8" OwnerPool="pool0101.s*****.com" Signatu 

 <app:DocItem xmlns:app="urn:schema:Microsoft.Rtc.Management.Xds.AppLayer.2008" Name="urn:upc:M****_K*****@s******.com" ItemId="92E2A9D7-41D7-4C43-BBBA-46122F5D5AB6" Owner="04DDEDCE-ED20-514D-8586-58BEE1904EE8" OwnerPool="pool0101.s*****.com" Signatu 
 
 <app:DocItem xmlns:app="urn:schema:Microsoft.Rtc.Management.Xds.AppLayer.2008" Name="urn:hcd:M****_K*****@s******.com" ItemId="92E2A9D7-41D7-4C43-BBBA-46122F5D5AB6" Owner="04DDEDCE-ED20-514D-8586-58BEE1904EE8" OwnerPool="pool0101.s*****.com" Signatu  
 
 NULL 
 
 NULL 

Step #3: Manipulating the store procedure we can identify which field(s) is generating the NULL
export-storeprocedure

Step #4: After several attempts stripping down the SQL statement, I found the two records on the table ‘Item’ that didn’t have a reference to any DocId field on the ‘Document’ table.
export-nolinkfields

I don’t know what caused the ‘orphaned’ relationship, since there are tables field constrains to prevent that…. but it’s time to find a solution that doesn’t compromise all existing data.

Simulating on a Lab environment showed that moving the user between pools or even disabling from Lync will not clear the bad records, and once the user was back on the pool database the issue will return.

The fix turn out to be more simple. Since the two records on the ‘Item’ table don’t have the constrain relation with the ‘Record’ table, they could be deleted without problems with the following SQL statements:

DELETE FROM [rtcxds].[dbo].[Item] WHERE ItemId = ’92e2a9d7-41d7-4c43-bbba-46122f5d5ab6′ and DocId = ‘17941959’
DELETE FROM [rtcxds].[dbo].[Item] WHERE ItemId = ’92e2a9d7-41d7-4c43-bbba-46122f5d5ab6′ and DocId = ‘17941967’

 

Advertisements

4 thoughts on “Lync issue: export-csuserdata failed with “Data in NULL”

  1. soder 25/11/2014 / 20:15

    Usual question: did you report to PSS? Seems SQL backend sproc bug.

    • LuisR 26/11/2014 / 19:28

      Could not determine the cause.
      And since I found a solution, no reason for P$$

      • soder 02/12/2014 / 09:20

        “And since I found a solution, no reason for P$$”

        Yes, but what about the other thousands, who may struggle with this issue? If case is opened to PSS, and they confirm its a bug, it may be fixed, and the other thousands will not have to rip their hair out.

  2. LuisR 02/12/2014 / 11:03

    Hello soder
    As I said: “could not determine the cause” so opening a case without any evidences will only cost money for my company…
    I hope my post could help people to keep their hair 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s