Article ID: 2674882 - View products that this article applies to.
Consider the following scenario:
If you enable trace flag 1222 to redirect the deadlock information into the SQL Server Error Log, you receive an error message that resembles one of the following:
Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
update MSreplication_subscriptions set transaction_timestamp = cast(@P1 as binary(15)) + cast(case datalength(transaction_timestamp) when 16 then isnull(substring(transaction_timestamp, 16, 1), 0) else 0 end as binary(1)), "time" = @P2 where UPPER(publisher) = UPPER(@P3) and publisher_db = @P4 and publication = @P5 and subscription_type = 0
update MSreplication_subscriptions set transaction_timestamp = cast(@P1 as binary(15)) + cast(substring(transaction_timestamp, 16, 1) as binary(1)), "time" = @P2 where UPPER(publisher) = UPPER(@P3) and publisher_db = @P4 and publication = @P5 and subscription_type = 0 and (substring(transaction_timestamp, 16, 1) = 0 or datalength(transaction_timestamp) < 16)
This issue occurs if the rowcount estimate for the number MSreplication_subscriptions system table is incorrect. If the rowcount estimate is incorrect, the SQL Server database engine may use an incorrect method to update the database.
Note Typically, the correct rowcount estimate is equal to the number of subscriptions in the database. If you use the Subscription Streams feature, the rowcount estimate is equal to the number of subscriptions multiplied by the number of configured streams for each subscription.
To resolve this issue, use one of the following methods.
Method 1: Use the DBCC UPDATEUSAGE command
To resolve this issue, update the incorrect rowcount value. To do this, run the following command:
DBCC UPDATEUSAGE (subscriber_database_name, 'MSreplication_subscriptions') WITH COUNT_ROWSNote The DBCC UPDATEUSAGE command determines the correct values for rows, used pages, reserved pages, leaf pages, and data page counts for each partition in a table. If these values are correct, the DBCC UPDATEUSAGE command returns no data. If inaccurate values are found and corrected, DBCC UPDATEUSAGE returns the rows and columns that are updated.
Method 2: Use the ALTER INDEX statement
To resolve this issue, rebuild the indexes that are associated with the MSreplication_subscriptions table. To do this, use the following statement:
ALTER INDEX ALL ON [dbo].[MSreplication_subscriptions] REBUILD
When the issue that is mentioned in the "Symptoms" section occurs, the rowcount estimate for the MSreplication_subscriptions system table can be as high as 4,294,967,296. To check the rowcount value, use one of the following methods.
Method 1: Use SQL Server Management Studio
To use SQL Server Management Studio to check the rowcount value for the MSreplication_subscriptions system table, follow these steps:
To check the rowcount value for the MSreplication_subscriptions system table, run the following query:
SELECT rows, * FROM sys.partitions WHERE object_id = object_id('MSreplication_subscriptions')
For more information about detecting and ending deadlocks in SQL Server, visit the following TechNet website:
http://technet.microsoft.com/en-us/library/ms178104.aspxFor more information about the ALTER statement, visit the following TechNet website:
http://technet.microsoft.com/en-us/library/cc879314.aspxFor more information about the DBCC UPDATEUSAGE command, visit the following MSDN website:
Article ID: 2674882 - Last Review: March 23, 2012 - Revision: 1.0
Contact us for more help
Connect with Answer Desk for expert help.