Error 1222 Lock Request Time Out Period Exceeded When Set up Replication
February 21st, 2008 by Andrew Chen
One of the SQL Server that I manage has a couple non-updatable transactional publications defined in a database. I needed to push those publications to a new subscribing SQL Server. Usually the process is relative simple.
However when I clicked on the “Finish” button today SQL Server just hung at the step to create subscription and never finish the task of setting up replication. The following screen shot shows where the step SQL Server was hanging at.
When setting up replication Enterprise Manager was running some store procedures on the database behind the scene. Most likely SQL Server was hanging on that step because the store procedure it was executing was waiting to acquire locks on the tables being published. That means some other process was locking the tables being published.
In order to find which process lock up a table I usually go to Enterprise Manager, right click on “Current Activit” under Management, click refresh and then expand “Locks/Object”. You will see which table was locked by which process there. However when I try to do that I got the message “Error 1222: Lock Request Time Out Period Exceeded”
How to deal with that? Keep waiting is not a solution. Fortunately to see what process lock up which table you don’t have to use Enterprise Manager. The following query will do that.
select distinct object_name(a.rsc_objid), a.req_spid, b.loginame
from master.dbo.syslockinfo a (nolock) join
master.dbo.sysprocesses b (nolock) on a.req_spid=b.spid
where object_name(a.rsc_objid) is not nullThe query will show which table was locked, the process that lock the table and the login name used by the process. Once you find out which process lock the tables you can issue a “kill @spid” statement to kill those processes (make sure if it is save to do that first). Don’t kill the process trying to create the subscription though. Once the other process is killed Enterprise Manager will be able to finish its job to cerate the subscription.
If you experience this problem then most likely you will experience the same thing when the snapshot job of the publication runs in order to initialize subscriber. In that case the same trick can solve the problem. Use the query to find out which process lock up the tables being published and kill them. But again it will be up to you to fine out whether it is save to kill those process that hang your replication set up.
Related Posts:
(4 votes, average: 4 out of 5)
Loading ...
jabugnunummdxunawell, hi admin adn people nice forum indeed. how’s life? hope it’s introduce branch
Thank you! This post is a life-saver! MS’s own incompetant help page doesn’t even tell you how to fix this (http://msdn.microsoft.com/en-us/library/aa337412.aspx).
:)) thank you. it works…
good dude.. keep rocking… thanks a lot
Hi this is really helped me. Thanks for sharing.
Great tip
Thanks dude,it is really help me.
[…] my Alter Table command from running. For that I used a query I found on this blog post Error 1222 Lock Request Time Out Period Exceeded When Set up Replication by Andrew […]