I have a small (200 rows / 400kb) table with 4 columns - nvarchar(MAX), nvarchar(50), and two ints. I'm having a problem with one particular row in which I can select and update the int fields, but when I attempt to select or update the nvarchar fields, the query runs indefinitely (at least 45 minutes before I cancel). I'm also unable to delete this row, or even truncate the table (again, the query runs indefinitely).
我有一个小的(200行/ 400kb)表,有4列 - nvarchar(MAX),nvarchar(50)和两个整数。我遇到一个特定行的问题,我可以在其中选择并更新int字段,但是当我尝试选择或更新nvarchar字段时,查询无限期运行(至少在我取消前45分钟)。我也无法删除此行,甚至截断表(同样,查询无限期运行)。
Any ideas? One of the int fields has a primary key, but there are no foreign keys.
有任何想法吗?其中一个int字段有一个主键,但没有外键。
3 个解决方案
#1
Looks like you have an uncommitted transaction locking things down.
看起来你有一个未提交的事务锁定事情。
You can free them up through the Activity Monitor. It is located in the Management folder of the database you are looking at.
您可以通过活动监视器释放它们。它位于您正在查看的数据库的Management文件夹中。
Expand that, right click on Activity Monitor, and select View Processes. You can right click on processes and kill them there.
展开它,右键单击Activity Monitor,然后选择View Processes。您可以右键单击进程并在那里将其终止。
This isn't always the best solution, especially with a production database. You generally want to figure out why the transaction is not committed, and either manually roll it back or commit it.
这并不总是最佳解决方案,尤其是对于生产数据库。您通常想要弄清楚事务未提交的原因,并手动回滚或提交它。
#2
Are you sure that row isn't locked? Open a new connection, run your select query, note the SPID from either the top of the window 2000 / 2005 and the bottom 2008 management studio. In another window run sp_who2. Find the spid from the query running the record.
你确定没有锁定行吗?打开一个新连接,运行您的选择查询,从窗口顶部2000/2005和底部2008管理工作室记下SPID。在另一个窗口中运行sp_who2。从运行记录的查询中查找spid。
If you don't care about uncommitted data, or just want to test the row, do:
如果您不关心未提交的数据,或者只是想测试该行,请执行以下操作:
select * from table with (nolock) where key = 'mykey'
#3
if DBCC CHECKDB doenst't help like Thirster42 said
如果DBCC CHECKDB没有帮助像Thirster42说的那样
i would just copy data except problem row into newTable and drop the oldTable...
我只是将问题行以外的数据复制到newTable并删除oldTable ...
also i would check size of your TempDB.
我也会检查你的TempDB的大小。
Check Profiler, maybe there is trigger being fired?
检查Profiler,可能有触发器被触发?
See Execution plan for anything unusual..
查看任何异常的执行计划..
#1
Looks like you have an uncommitted transaction locking things down.
看起来你有一个未提交的事务锁定事情。
You can free them up through the Activity Monitor. It is located in the Management folder of the database you are looking at.
您可以通过活动监视器释放它们。它位于您正在查看的数据库的Management文件夹中。
Expand that, right click on Activity Monitor, and select View Processes. You can right click on processes and kill them there.
展开它,右键单击Activity Monitor,然后选择View Processes。您可以右键单击进程并在那里将其终止。
This isn't always the best solution, especially with a production database. You generally want to figure out why the transaction is not committed, and either manually roll it back or commit it.
这并不总是最佳解决方案,尤其是对于生产数据库。您通常想要弄清楚事务未提交的原因,并手动回滚或提交它。
#2
Are you sure that row isn't locked? Open a new connection, run your select query, note the SPID from either the top of the window 2000 / 2005 and the bottom 2008 management studio. In another window run sp_who2. Find the spid from the query running the record.
你确定没有锁定行吗?打开一个新连接,运行您的选择查询,从窗口顶部2000/2005和底部2008管理工作室记下SPID。在另一个窗口中运行sp_who2。从运行记录的查询中查找spid。
If you don't care about uncommitted data, or just want to test the row, do:
如果您不关心未提交的数据,或者只是想测试该行,请执行以下操作:
select * from table with (nolock) where key = 'mykey'
#3
if DBCC CHECKDB doenst't help like Thirster42 said
如果DBCC CHECKDB没有帮助像Thirster42说的那样
i would just copy data except problem row into newTable and drop the oldTable...
我只是将问题行以外的数据复制到newTable并删除oldTable ...
also i would check size of your TempDB.
我也会检查你的TempDB的大小。
Check Profiler, maybe there is trigger being fired?
检查Profiler,可能有触发器被触发?
See Execution plan for anything unusual..
查看任何异常的执行计划..