Search code examples
sqlsql-server-2008sql-updatesql-delete

How to delete a large record from 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.

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.

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.

Solution

  • 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

    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.

    Option 2. Partitioning.

    1. 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.

    2. Create partitioning function so there would be two partitions 0 and 1.

    3. Create a temp table with the same structure as the original table.

    4. Switch partition 1 from original table to the new temp table.

    5. Drop temp table.

    6. 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