How to solve SQL Server Error 1222 i.e Unlock a SQL Server table
Solution 1
In the SQL Server Management Studio, to find out details of the active transaction, execute following command
DBCC opentran()
You will get the detail of the active transaction, then from the SPID of the active transaction, get the detail about the SPID using following commands
exec sp_who2 <SPID>
exec sp_lock <SPID>
For example, if SPID is 69 then execute the command as
exec sp_who2 69
exec sp_lock 69
Now , you can kill that process using the following command
KILL 69
I hope this helps :)
Solution 2
It's been a while, but last time I had something similar:
ROLLBACK TRAN
or trying to
COMMIT
what had allready been done free'd everything up so I was able to clear things out and start again.
Solution 3
To prevent this, make sure every BEGIN TRANSACTION has COMMIT
The following will say successful but will leave uncommitted transactions:
BEGIN TRANSACTION
BEGIN TRANSACTION
<SQL_CODE?
COMMIT
Closing query windows with uncommitted transactions will prompt you to commit your transactions. This will generally resolve the Error 1222 message.
Related videos on Youtube
user960340
Updated on July 05, 2022Comments
-
user960340 almost 2 years
I am working in a database where I load data in a raw table by a data loader. But today the data loader got stuck for unknown reasons. Then I stopped the data loader from windows task manager. But then I again tried to load data in the raw table but found its locked and I can't do any operation on it. I tried restarting SQL Server service but it was not resolved. And I have no permission to kill processes on this server.
Below is the message showed by SQL Server.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
at Microsoft.SqlServer.Management.Smo.NamedSmoObject.RenameImplWorker(String newName)
at Microsoft.SqlServer.Management.Smo.NamedSmoObject.RenameImpl(String newName)===================================
Lock request time out period exceeded. Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong. (.Net SqlClient Data Provider)
Server Name: 162.44.25.59 Error Number: 1222
Severity: 16 State: 56
Procedure: sp_rename Line Number: 282My SQL Server version is 2008 R2.
-
askids over 12 yearsDid you try killing the session id which has locked the table? You can check it in the SQL Server Activity Monitor on the server or use combination of sp_lock2 and sp_who to find which session needs to be killed.
-
user960340 over 12 yearsI don't have enough privilege to do it, are there any other solutions please??
-
Ben Thul over 12 yearsNo other solution other than "wait" or "kill". What that error is telling you is that another process is holding a lock on the resource that you're trying to use that's incompatible with the operation that you're trying to do. You can try running again to see where the blocking shows up (specifically, which process is blocking yours), but ultimately, it needs to stop doing what it's doing for you to continue.
-
-
user960340 over 12 yearsI figured out ROLLBACK TRAN/COMMIT Works if that query window is still open that caused table block, otherwise it doesn't works
-
Coops almost 10 yearsLuckily I still had the window open, not sure what happens if you don't
-
GeorgeMR over 7 yearsYou save me----
-
Tony over 7 yearsThe kill command would cause a rollback which may take a while. You can use command
kill 69 with statusonly
to see the updated progress status. You can also use commanddbcc inputbuffer(69)
to find out what SQL command was executing and caused the lock up. -
Tarek El-Mallah almost 7 yearsKILL {process number} ==> fixed my problems. Thanks,
-
Uditsing Khati about 3 yearsThanks, mate. Your answer is very helpful 👍
-
Francesco Mantovani over 2 yearsSo easy and elegant. You teach me something today. Thank you