12

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
CatDadCode
  • 58,507
  • 61
  • 212
  • 318
  • Not sure why I get a close vote. Where else should I ask a sql server related question? – CatDadCode Jun 23 '11 at 02:28
  • It's quite big unfortunately. I'm also not a SQL guru and am unfamiliar with how to migrate data, but if that is my only option then I will get familiar. – CatDadCode Jun 23 '11 at 02:31
  • You can make a quick copy of the table without the offending record as Alex points out using: `SELECT * INTO MyTable_GoodData FROM MyTable WHERE id != 2010; TRUNCATE TABLE MYTable; INSERT INTO MyTable SELECT * FROM MyTable_GoodData;` – Eric Tuttleman Jun 23 '11 at 02:32
  • @Alex 53,423 rows. Not sure if that's a lot or a little relative to other databases. – CatDadCode Jun 23 '11 at 02:35
  • You may want to see if there's any locks that are causing operations to wait. You can get a quick idea by looking at the Activity Monitor in SQL Management Studio. You can also use TSQL commands like sp_lock – Eric Tuttleman Jun 23 '11 at 02:37
  • @Alex and indexes, also would have a problem with sps / views. It can go either way, but it's probably safest to dump data and bring it back. – Eric Tuttleman Jun 23 '11 at 02:38
  • FYI: `TRUNCATE TABLE` does not work if the table is referenced by foreign keys. – Jon Seigel Jun 23 '11 at 02:39
  • @Eric Tuttleman - yes it is faster to recreate all these. – Alex Aza Jun 23 '11 at 02:40
  • @Eric, where might I find the activity monitor? I have SSMS open, but I'm not terribly familiar with it. – CatDadCode Jun 23 '11 at 02:40
  • @Chevex once you connect to a sql server, right-clicking on it should bring up a context-menu with an Activity Monitor option – Eric Tuttleman Jun 23 '11 at 02:42
  • @Eric, I see. This is a shared SQL server with other people that pay for shared hosting accounts. I opened it up but it tells me I do not have permissions to access that stuff. Hmmm, I suppose I will have to contact their support. – CatDadCode Jun 23 '11 at 02:44
  • It could take much time because of the logging of the delete. – Andriy M Jun 23 '11 at 02:45
  • Which means that logging could temporarily be disabled (along with shutting down the site, of course.) – Andriy M Jun 23 '11 at 02:46
  • @Andriy how do you temporarily disable logging? I ran into issues with that in the past while trying to delete over 30,000 records at once. – CatDadCode Jun 23 '11 at 02:49
  • Oops, if it's a shared instance, than probably the idea isn't very good. – Andriy M Jun 23 '11 at 02:49
  • Ah, I see. Thanks anyway Andriy. – CatDadCode Jun 23 '11 at 02:49
  • Did I miss where you told us why you don't just do `UPDATE [u413].[replies] SET BODY = NULL WHERE replyID=13461`? – John Saunders Jun 23 '11 at 02:50
  • @John, yes you did. First sentence of second paragraph. – CatDadCode Jun 23 '11 at 02:54
  • @John: I read [somewhere](http://stackoverflow.com/questions/5346989/sql-server-what-happens-when-a-row-in-a-table-is-updated) that UPDATE is internally a DELETE followed by an INSERT. – Andriy M Jun 23 '11 at 02:55
  • Do you probably want to try delete with nolock option. It is just a guess without confident background knowledge. Or maybe wrap your statement into procedure and run procedure. – user194076 Jun 23 '11 at 02:57
  • @Andriy: I believe you've misread that. The fact that the location changes (when the table has a clustered index) doesn't mean that the row is deleted and then inserted again. Don't confuse internal implementation with semantics or performance, especially since setting the column to NULL seems unlikely to move the row or affect indexes (unless maybe you have that column indexed?) – John Saunders Jun 23 '11 at 03:00
  • @John, misread or not, the same result occurs. It hangs on update or delete. – CatDadCode Jun 23 '11 at 03:03

3 Answers3

4

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

Alex Aza
  • 76,499
  • 26
  • 155
  • 134
  • In the first solution, if he does `TRUNCATE` instead of `DROP` and then repopulates the table, he won't have to worry about indexes and constraints. – Andrew Lazarus Jun 23 '11 at 03:32
  • @Andrew Lazarus - this way table will be blocked for quite a long time, while copying data, while sp_rename is instant. Also you don't want to have indexes why you are populating the table, you would at least disable them. I would rather drop and recreate. – Alex Aza Jun 23 '11 at 03:47
  • This sounds great except for one problem. I get an error when I try to drop the replies table. http://www.codetunnel.com/content/droperror.jpg. I get the same error if I try to delete a foreign key on that table as well. – CatDadCode Jun 23 '11 at 04:22
  • 1
    @Chevex - as far as I can tell there are active connections that are using the table. Can you make sure the server is not publicly available for a few minutes? – Alex Aza Jun 23 '11 at 04:24
  • Still same error. Creating the temp table was successful though :) – CatDadCode Jun 23 '11 at 04:28
  • Can you check who is blocking it? `select distinct object_name(a.rsc_objid), a.req_spid, b.loginame from master.dbo.syslockinfo a (nolock) join master.dbo.sysprocesses b (nolock) on a.req_spid=b.spid where object_name(a.rsc_objid) is not null` – Alex Aza Jun 23 '11 at 04:29
  • I can drop and recreate the temp table just fine. – CatDadCode Jun 23 '11 at 04:30
  • you can kill the open sessions using `kill @processId`. – Alex Aza Jun 23 '11 at 04:30
  • What do I put for @processId? – CatDadCode Jun 23 '11 at 04:31
  • btw, the long query you gave me tells me `The user does not have permission to perform this action.` Probably another downside of it being a shared server. – CatDadCode Jun 23 '11 at 04:33
  • 1
    Hmmm... it is difficult to resolve issues like this without having permissions. The problem is that your table is locked right now, you need to find out by which process. This is probably the same process that wasn't letting you to delete the row in the first place. – Alex Aza Jun 23 '11 at 04:35
  • Hmmmm, I'm not sure if this means anything, but I tested something. I copied the data from the replies table into a new temp table, but I did not leave out the offending record. It copied successfully. I then deleted the offending record from the temp table. It deleted successfully. Weird. – CatDadCode Jun 23 '11 at 04:39
  • Even more interesting. I cannot delete ANY record in that table that is related to the same topic. But I CAN delete other records for different topics. – CatDadCode Jun 23 '11 at 04:44
  • 1
    Can you contact db admin and ask to kill all active sessions for your database? – Alex Aza Jun 23 '11 at 04:47
  • Yeah I emailed the host already. They are slow to respond though :/ – CatDadCode Jun 23 '11 at 04:53
  • This definitely sounds like blocking. If you can get the lock released, you should be fine. – Bob Probst Jun 23 '11 at 17:35
3

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.

Phil Helmer
  • 1,230
  • 8
  • 8
  • Thank you for this answer. Unfortunately this is a shared instance of SQL server and I don't have access to all this information. I will have to contact their support. I will keep this in mind however in the future. If this gets enough upvotes I will just go ahead and accept it. – CatDadCode Jun 23 '11 at 02:53
1

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)
Matt Donahue
  • 76
  • 3
  • [Facepalm1](http://www.CodeTunnel.com/Content/images/facepalm1.jpg) & [Facepalm2](http://www.CodeTunnel.com/Content/images/facepalm2.jpg) – CatDadCode Jun 23 '11 at 17:37
  • Sorry, read that as editing the post from the UI. Thought a direct update might work around that. – Matt Donahue Jun 23 '11 at 18:57