CODEWITHSUNDEEP
sqlsql-server-2008sql-updatesql-delete
ChevCast
ChevCast

Reputation: 59231

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.

Upvotes: 12

Views: 1239

Answers (3)

Matt Donahue
Matt Donahue

Reputation: 76

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

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)

Upvotes: 1

Alex Aza
Alex Aza

Reputation: 78477

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

Upvotes: 4

Phil Helmer
Phil Helmer

Reputation: 1240

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.

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.

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.

Upvotes: 3

Related Questions