如何解决SQL Server Error 1222即解锁SQL Server表的问题[英] How to solve SQL Server Error 1222 i.e Unlock a SQL Server table

本文是小编为大家收集整理的关于如何解决SQL Server Error 1222即解锁SQL Server表的问题的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正在数据库中工作,在该数据库中,我通过数据加载程序将数据加载到原始表中.但是今天,由于未知原因,数据加载程序被卡住了.然后,我从Windows Task Manager停止了数据加载程序.但是后来我再次尝试将数据加载到原始表中,但发现了它的锁定,我无法对其进行任何操作.我尝试重新启动SQL Server服务,但未解决.而且我无权杀死该服务器上的进程.

以下是SQL Server显示的消息.

执行Transact-SQL语句或 批. (Microsoft.sqlserver.connectioninfo)

程序位置:

在microsoft.sqlserver.management.common.serverconnection.executenonquery(字符串) sqlcommand,executionTypes executionType)
在Microsoft.sqlserver.management.common.serverconnection.executenonquery(StringCollection) sqlcommands,executionTypes executionType)
在Microsoft.sqlserver.management.smo.executionmanager.executenonquery(StringCollection) 查询)
在Microsoft.sqlserver.management.smo.sqlsmoobject.executenonquery(StringCollection 查询,布尔值包括Bcontext)
在Microsoft.sqlserver.management.smo.namedsmoobject.renameimplworker(字符串) newname)
在Microsoft.sqlserver.management.smo.namedsmoobject.renameimpl(字符串) newname)

================================

锁定要求超出时间期.要么参数@Objname 是模棱两可的,或者声称的@ObjType(对象)是错误的. (.网 sqlclient数据提供商)


服务器名称:162.44.25.59 错误编号:1222
严重性:16状态:56
过程:sp_rename行号:282

我的SQL Server版本是2008 R2.

推荐答案

在SQL Server Management Studio中, 要查找活动交易的详细信息,请执行以下命令

DBCC opentran()

您将获取活动交易的细节,然后从活动交易的蜘蛛中获取有关蜘蛛的细节,使用以下命令

exec sp_who2 <SPID>
exec sp_lock <SPID>

例如,如果蜘蛛为69,则执行命令为

exec sp_who2 69
exec sp_lock 69

现在,您可以使用以下命令

杀死该过程
KILL 69

我希望这会有所帮助:)

其他推荐答案

已经有一段时间了,但是上次我有类似的东西:

ROLLBACK TRAN

或尝试

COMMIT

已经准备好了一切,所以我能够清理一切并重新开始.

其他推荐答案

为了防止这种情况,请确保每个开始交易都有提交

以下会说成功,但将留下未合作的交易:

BEGIN TRANSACTION
BEGIN TRANSACTION
<SQL_CODE?
COMMIT

通过不承诺的交易关闭查询窗口将提示您提交交易.这通常会解决错误1222消息.

本文地址:https://www.itbaoku.cn/post/597456.html

问题描述

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.

推荐答案

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

其他推荐答案

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.

其他推荐答案

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.