How to solve SQL Server Error 1222 i e Unlock a SQL Server table

Question

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  282     My SQL Server version is 2008 R2

User · Answer

To prevent this  make sure every BEGIN TRANSACTION has COMMIT  The following will say successful but will leave uncommitted transactions   BEGIN TRANSACTION BEGIN TRANSACTION  lt SQL CODE  COMMIT   Closing query windows with uncommitted transactions will prompt you to commit your transactions  This will generally resolve the Error 1222 message

User · Answer

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  lt SPID gt  exec sp lock  lt SPID gt    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

User · Answer

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

User · Answer

I had these SQL behavior settings enabled on options query execution  ANSI SET IMPLICIT TRANSACTIONS checked  On execution of your query e g create  alter table or stored procedure  you have to COMMIT it   Just type COMMIT and execute it F5

[sql-server] How to solve SQL Server Error 1222 i.e Unlock a SQL Server table

Examples related to sql-server

Examples related to database

Examples related to sql-server-2008-r2

Examples related to locking

Examples related to deadlock