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.
Best Answer
In the SQL Server Management Studio, to find out details of the active transaction, execute following command
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
For example, if SPID is 69 then execute the command as
Now , you can kill that process using the following command
I hope this helps :)