如何从SQL Server中删除大记录？

Question

在一个论坛的数据库中，我错误地将"body"设置为nvarchar(MAX)。结果，当然有人发了《大英百科全书》。因此，现在有一个论坛主题由于这一帖子而无法加载。我已经确定了这个帖子，并在其上运行了一个删除查询，但出于某种原因，查询只是在那里打转。我让它运行了几个小时，但它仍然停在那里。最终，它会超时。

我也试图编辑帖子的正文，但那也没用。当我让查询运行时，整个数据库都会挂起，所以我暂时关闭了站点，以防止它在思考过程中继续接收请求。如果我取消我的查询，那么站点就会恢复正常，所有不涉及问题记录的查询都可以完美地工作。

还有其他人遇到这个问题吗？有没有一种简单的方法来彻底摧毁这条“邪恶”的记录呢？

更新：对不起，SQL Server的版本是2008。

这是我用来删除记录的查询：

DELETE FROM [u413].[replies] WHERE replyID=13461

我还尝试删除了与回复相关的主题，但是与主题相关的回复级联删除也会导致挂起。

- Chev

---

不确定为什么我会被投票关闭。我应该在哪里提出与SQL Server相关的问题？ - Chev

很不幸，这相当大。我也不是SQL大师，对于如何迁移数据也不熟悉，但如果这是我的唯一选择，那么我会努力熟悉它。 - Chev

您可能想查看是否有锁定正在导致操作等待。您可以通过查看 SQL Management Studio 中的 Activity Monitor 快速了解情况。您也可以使用像 sp_lock 这样的 TSQL 命令。 - Eric Tuttleman

@Chevex 一旦您连接到 SQL Server，右键单击它应该会弹出一个上下文菜单，其中包含“活动监视器”选项。 - Eric Tuttleman

@Eric，我明白了。这是一个共享SQL服务器，有其他人付费使用共享托管账户。我打开了它，但它告诉我我没有权限访问那些东西。嗯，我想我必须联系他们的支持部门。 - Chev

Answer 1

选项1：取决于表格本身的大小以及行的大小。

1. Copy data to a new table:

   SELECT *
   INTO tempTable
   FROM replies WITH (NOLOCK)
   WHERE replyID != 13461
   

   Although it will take time, table should not be locked during the copy process

2. Drop old table

   DROP TABLE replies
   

   Before you drop:
   - script current indexes and triggers so you are able to recreate them later
   - script and drop all the foreign keys to the table

3. Rename the new table

   sp_rename 'tempTable', 'replies'
   

4. Recreate all the foreign keys, indexes and triggers.

选项2：分区。

1. 添加一个名为“Partition”的新位列，对于除了错误行以外的所有行设置为0。对于错误行设置为1。

2. 创建分区函数，使得有两个分区0和1。

3. 创建一个与原始表具有相同结构的临时表。

4. 将原始表中的分区1切换到新的临时表。

5. 删除临时表。

6. 从源表中删除分区并删除新列。

分区主题并不简单。互联网上有一些示例，例如SQL Server 2005中的分区切换。

- Alex Aza

---

在第一种解决方案中，如果他使用TRUNCATE而不是DROP然后重新填充表格，他就不必担心索引和约束。 - Andrew Lazarus

@Chevex - 就我所知，有正在使用该表的活动连接。您能确保服务器在几分钟内不公开可用吗？ - Alex Aza

您可以使用 kill @processId 命令终止打开的会话。 - Alex Aza

嗯...没有权限很难解决这样的问题。问题在于你的表格现在被锁定了，你需要找出是哪个进程导致的。这可能是同一个进程，一开始就不允许你删除行。 - Alex Aza

你能联系数据库管理员并请求关闭所有活动会话吗？ - Alex Aza

Answer 2

首先检查您的交易是否被另一个进程阻止。为此，您可以运行以下命令：

SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id = {spid}

将 {spid} 替换为运行 DELETE 命令的连接的正确 spid 编号。要获取该值，请在 DELETE 命令之前运行 SELECT @@spid。

如果列 sys.dm_os_waiting_tasks.blocking_session_id 有值，您可以使用活动监视器查看该进程正在执行的操作。

要打开活动监视器，请在 SSMS 的对象资源管理器中右键单击服务器名称，然后选择“活动监视器”。您需要查看“进程”和“资源等待”部分。

- Phil Helmer

---

谢谢您的回答。不幸的是，这是一个共享的SQL服务器实例，我无法访问所有这些信息。我将不得不联系他们的支持部门。然而，我会记住这个问题，在未来可能会用到。如果这个回答得到足够的赞，我会直接接受它。 - Chev

Answer 3

由于您在删除记录和重新创建表方面遇到了问题，您是否尝试更新记录？

例如（将“body”字段名称更改为表中的任何内容）：

update [u413].[replies] set body='' WHERE replyID=13461

一旦您清除了单个回复记录中的文本，您就可以更改列的数据类型以设置上限。例如：

alter table [u413].[replies] alter column body nvarchar(100)

- Matt Donahue

---

Facepalm1 & Facepalm2 - Chev

抱歉，我误解了您的意思，以为您是从用户界面编辑帖子。我认为直接更新可能会解决这个问题。 - Matt Donahue