Replication-Replication Distribution Subsystem: agent ********* scheduled for retry. Transaction (Process ID 76) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Tuesday, December 04, 2012 5:17 AMHi Every one.
Due to some issue we restarted our server.After restarting we are observing the below error.
Replication-Replication Distribution Subsystem: agent *** scheduled for retry. Transaction (Process ID 76 ) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
After some time subscription went to inactive.
Replication-Replication Distribution Subsystem: agent S********failed. The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.
Can you please let me know why the first error created the issue.Previously whenever this error comes we never face an issue but this time Replication is not happening.
In our environment we have set Transactional up datable replication.(sql server 2005 Standard edi)
Now distribution agent job i snot working.Kindly give some inputs.
- Edited by mito access Tuesday, December 04, 2012 5:43 AM
Tuesday, December 04, 2012 5:28 PMModerator
You need to rebuild your indexes on the replication system tables on the subscriber.
Also check to see if an application lock is been held on the subscriber. A reboot of the subscriber will clear it.
This should help you to diagnose it.
SELECT resource_type, (CASE WHEN resource_type = 'OBJECT' THEN object_name(resource_associated_entity_id) WHEN resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN 'N/A' WHEN resource_type IN ('KEY', 'PAGE', 'RID') THEN ( SELECT object_name(object_id) FROM sys.partitions WHERE hobt_id=resource_associated_entity_id ) ELSE 'Undefined' END) AS requested_object_name, request_mode AS lock_type, request_status, request_owner_id AS transaction_id FROM sys.dm_tran_locks where resource_type='APPLICATION'
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
Wednesday, December 05, 2012 4:54 AM
Thanks for your reply.
I executed the query.At lock type its showing "X"(exclusive lock).Reboot means reinitialize.Is it right?
If i rebuild the indexes how it solve this issue.Can You please provide some more information this.
Thank you for your continuous support.
- Edited by mito access Wednesday, December 05, 2012 5:07 AM