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

In a database for a forum I mistakenly set the body to nvarchar(MAX). Well, someone posted the Encyclopedia Britanica, of course. So now there is a forum topic that won't load because of this one post. I have identified the post and ran a delete query on it but for some reason the query just sits and spins. I have let it go for a couple hours and it just sits there. Eventually it will time out.

在论坛的数据库中，我错误地将主体设置为nvarchar（MAX）。当然，有人发布了英国百科全书百科全书。所以现在有一个论坛主题因为这一篇文章而无法加载。我已经确定了帖子并对其进行了删除查询，但由于某种原因，查询只是坐下并旋转。我已经放了几个小时，它只是坐在那里。最终会超时。

I have tried editing the body of the post as well but that also sits and hangs. When I sit and let my query run the entire database hangs so I shut down the site in the mean time to prevent further requests while it does it's thinking. If I cancel my query then the site resumes as normal and all queries for records that don't involve the one in question work fantastically.

我也尝试过编辑帖子的主体，但也可以坐着并挂起。当我坐下来让我的查询运行时，整个数据库都会挂起，所以我会同时关闭该网站以防止进一步的请求，而它正在思考。如果我取消我的查询，那么该网站将恢复正常，并且所有不涉及该问题的记录的查询都会非常有效。

Has anyone else had this issue? Is there an easy way to smash this evil record to bits?

其他人遇到过这个问题吗？是否有一种简单的方法可以将这个邪恶的记录粉碎成碎片？

Update: Sorry, the version of SQL Server is 2008.

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

Here is the query I am running to delete the record:

这是我正在运行的删除记录的查询：

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

I have also tried deleting the topic itself which has a relationship to replies and deletes on topics cascade to the related replies. This hangs as well.

我还尝试删除主题本身，该主题与回复和删除相关回复的主题上的回复和删除有关。这也挂起了。

3 个解决方案

#1

Option 1. Depends on how big the table itself and how big are the rows.

选项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

尽管需要时间，但在复制过程中不应锁定表
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

删除之前： - 脚本当前索引和触发器，以便您以后可以重新创建它们 - 脚本并将所有外键删除到表
Rename the new table

重命名新表
```
sp_rename 'tempTable', 'replies'
```
Recreate all the foreign keys, indexes and triggers.

重新创建所有外键，索引和触发器。

Option 2. Partitioning.

选项2.分区。

Add a new bit column, called let's say 'Partition', set to 0 for all rows except the bad one. Set it to 1 for bad one.

添加一个新的位列，名为“Partition”，对于除坏的行之外的所有行都设置为0。对于坏的，将其设置为1。
Create partitioning function so there would be two partitions 0 and 1.

创建分区功能，因此会有两个分区0和1。
Create a temp table with the same structure as the original table.

创建一个与原始表具有相同结构的临时表。
Switch partition 1 from original table to the new temp table.

将分区1从原始表切换到新的临时表。
Drop temp table.

删除临时表。
Remove partitioning from the source table and remove new column.

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

Partitioning topic is not simple. There are some examples in the internet, e.g. Partition switching in SQL Server 2005

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

#2

Start by checking if your transaction is being blocked by another process. To do this, you can run this command..

首先检查您的交易是否被其他流程阻止。为此，您可以运行此命令..

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

Replace {spid} with the correct spid number of the connection running your DELETE command. To get that value, run SELECT @@spid before the DELETE command.

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

If the column sys.dm_os_waiting_tasks.blocking_session_id has a value, you can use activity monitor to see what that process is doing.

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

To open activity monitor, right-click on the server name in SSMS' Object Explorer and choose Activity Monitor. The Processes and Resource Waits sections are the ones you want.

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

#3

Since you're having issues deleting the record and recreating the table, have you tried updating the record?

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

Something like (changing "body" field name to whatever it is in the table):

类似的东西（将“body”字段名称更改为表格中的任何内容）：

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

Once you clear out the text from that single reply record you should be able to alter the data type of the column to set an upper bound. Something like:

清除单个回复记录中的文本后，您应该能够更改列的数据类型以设置上限。就像是：

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

#1