Превышено время ожидания запроса на блокировку microsoft sql server ошибка 1222

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.

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.

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

In my case, I was trying to disable a trigger on a table when I received error 1222 «Lock request time out period exceeded.»

I followed suggestions in this answer:

1. Open two query windows in SSMS.
2. In the first, type/paste the command that is timing out (due to a lock). In the lower right hand corner of SSMS, you should see the username and (in parentheses) the SPID of the connection you’re using. Note the SPID of this query window connection. Don’t execute this query just yet.
3. In the second query window, type/paste SELECT * FROM sysprocesses WHERE spid = <SPID you noted in step 2>
4. Execute the first query that is timing out, and while it is executing (but before it times out) switch over to the second query window and execute it (the SELECT * from sysprocesses... one)
5. You should get some results in the results pane. Look at the ‘blocked’ field in the results. In my case, it contained the SPID of the process that was locking the table.
6. Research the locking process further by executing SELECT * FROM sysprocesses WHERE spid = <SPID from the ‘blocked’ field in step 5>.
7. If the locking process can be safely terminated, kill it with kill <locking SPID>

answered May 25, 2021 at 21:30