var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: UPDATE freezing problem
Hi, i am having a problem with UPDATE.
I have the following table with about 4 million rows:
CREATE TABLE [newauth] (
[authnumber] [int] NULL ,
[batchnumber] [int] NULL ,
[accountnumber] [varchar] (20) NULL,
[authcode] [varchar] (10) NULL ,
[authused] [char] (1) NULL ,
[loaddate] [int] NULL ,
[trandate] [int] NULL ,
[trantime] [int] NULL ,
[cardtype] [char] (1) NULL ,
[mcc] [char] (4) NULL ,
[amount] [int] NULL ,
[transactionnumber] [int] NULL
) ON [PRIMARY]
There is an index on the authnumber field. I am calling the following stored procedure to update the transactionnumber.
CREATE PROCEDURE UpdateAuthWithTrans(@lAuth int, @lTrans int)
SET TransactionNumber = @lTrans
WHERE AuthNumber = @lAuth
I need to update about 1 million rows. Approx 50000 calls to this SP work OK. Then the DB freezes. Enterprise mgr shows the following (screen shot attached)
I cant figure out why this is freezing. I have tried update statistics newauth, dbcc checktable, rebuilding the index, rebooting the server.
Any ideas would be appreciated, thanks in advance for any help.
I misread the screen. Another user was jumping and locking the table... my mistake (is it Friday yet?)
Fine...but 50,000 sproc calls?
Where do you get
@lAuth int, @lTrans int
Another table right?
If so, you should be performing an update with a join...
Normally i would, but there was some screwy business logic behind the scenes that was collecting data from a variety of places and systems.