Weird Update problem

Question

Weird Update problem

Mark Lines-Davies-427778

Old Hand

Points: 334
More actions
August 7, 2007 at 4:38 am

#180252

I have a weird update problem that I hope somebody can shed some light on...
I have a database creation script that was written by SQL Server Migration Assistant. If I create the database using the New Database option within SQL Server Management Studio Express, and then run the script, all the tables, constraints, foreign keys etc are created as I would expect.
SSMSE reports a few errors as it runs the script, but this is only because SSMA's script tries to add some constraints more than once.
The database is called LBMS and the most important table is called BOOKING.
If I do this after doing a bulk load:
USE LBMS
UPDATE BOOKING SET BOOK_COMMENTS='Hello World' WHERE BOOK_ID=411860
...the update executes forever, and never returns (the longest I've let it run is 40 minutes).
The appropriate record exists, by the way.
I thought it might be something to do with the bulk load, or that somehow the whole table was locked, but that seemed to lead nowhere.
I then discovered that if I drop the database, recreate it with the script and then do this...
USE LBMS
UPDATE BOOKING SET BOOK_COMMENTS='Hello World' WHERE BOOK_ID=1
...it still executes forever, even though the table is empty.
9 other tables have FKs against the BOOK_ID. If I drop these FKs, drop the BOOKING table, recreate it and then recreate the FKs, it then works.
Has anyone any idea what's happening?
regards
Mark

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

michaela SSCertifiable Points: 5649 More actions · Answer 1

What happen in Activity Monitor (under Management) when you run the update? Is there a blocking? What sort of wait type can you see there for the spid that runs the update?

Mark Lines-Davies-427778 Old Hand Points: 334 More actions · Answer 2

Under Process Info, there are three process ids. The one for the UPDATE has Database=LBMS, Status=runnable, Open Txns=1, Command=CONDITIONAL.

Under Locks by Process, this process has

OBJECT, 53575229, IX, LOCK, GRANT, TRANSACTION

DATABASE, 0, S, LOCK, GRANT, SHARED_TRANSACTION_WORKSPACE

Under Locks By Object, I see the same information plus 14 other locks owned by another process

regards

Mark

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 3

Any triggers on the BOOKING table ? is 53575229 the BOOKING table ?

Mark Lines-Davies-427778 Old Hand Points: 334 More actions · Answer 4

There aren't any triggers on the Booking table.

How do I find out if 53575229 is the Booking table?

thanks

Mark

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 5

use

select

object_name(53575229)

in the database that the BOOKING table resides.

Mark Lines-Davies-427778 Old Hand Points: 334 More actions · Answer 6

Thanks for the command.

Yes, 53575229 is the BOOKING table.

regards

Mark

Chris Holt SSC Rookie Points: 45 More actions · Answer 7

What sort of wait type can you see there for the spid that runs the update? There is a wait type column that should have something in it if it is waiting.

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 8

i wonder why the command is "conditional" in activity monitor, doesn't that usually indicate an if statement or a loop ?

Mark Lines-Davies-427778 Old Hand Points: 334 More actions · Answer 9

Wait Time = 0, Wait Type is blank, CPU=591702, Blocked By=0, Blocking=0

...and yet it's still running. 26 minutes so far.

regards

Mark

Chris Holt SSC Rookie Points: 45 More actions · Answer 10

Wow. That is so strange. If you right-click on the row in Activity monitor and choose details what does it say?

Mark Lines-Davies-427778 Old Hand Points: 334 More actions · Answer 11

It just shows the SQL command.

In the Status column it says "runnable". According to SQL Server Books online, this means the process is currently doing no work.

All very odd.

I tried updating on a different column, but it made no difference 🙁

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 12

this sounds very strange. Have you tried creating another table with the same column types but without all the associated FK's and test an update on that ?

Mark Lines-Davies-427778 Old Hand Points: 334 More actions · Answer 13

I tried modifying the creation script so that the other tables' FKs weren't created, and still got the problem until I dropped and recreated the BOOKING table.

However, that's not quite the same as your suggestion, so I'll try creating a second table.

What I have just done is run the creation script on a different machine, which also exhibits the same problem. So at least the problem is easy to reproduce.

thanks

Mark

Mark Lines-Davies-427778 Old Hand Points: 334 More actions · Answer 14

I used SS Management Studio Express to make a create script for the BOOKING table, then modified it to create BOOKING2.

If I try an update against BOOKING2, it works. But an update against the original BOOKING table still runs forever.

One oddity about the BOOKING table is that it has an FK (RPT_ID) against the REPEAT_BOOK table and vice versa. In the existing Oracle database that I ran SSMA against, when the users first add a booking the RPT_ID field in the BOOKING table is null. Then they decide to make this a repeat booking, so a REPEAT_BOOK record is written with an FK to the BOOKING table's BOOK_ID, and then the RPT_ID in the BOOKING table is updated.

I don't know if this might have any bearing on the problem...

regards

Mark