Q:

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

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.  

A:

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 :)

A:

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.

A:

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.

