query running now for over 3 hours

Question

query running now for over 3 hours

kevinwinters

Say Hey Kid

Points: 696
More actions
July 10, 2009 at 8:05 am

#208094

I am running this query in the query analyzer:
update claimfile set recreason = ''
The computer is not a dog but it has been running for 3 hours - is there a better way to change a field to be "blank" instead of "null" in 3 million records?

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

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

kramaswamy SSCoach Points: 18181 More actions · Answer 1

if you just want to change that field to an empty string, maybe dropping the column and then making a new column with the default value of empty string would be faster? i think it would be faster than 3 hours for sure, though i could be wrong.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

Exec sp_who

Look at the blk column if this is zero it means no blocking, I'm betting your process is being blocked.. You need to know the SPID of the long running process, SSMS will tell you this information, it is near the bottom right where it shows your login name something like "domain\username (55)", that number is the SPID.

I am fairly sure you are getting blocked..

CEWII

Lowell SSC Guru Points: 323518 More actions · Answer 3

with massive updates like that I think it is a lot easier to undo or stop if you did the updates in batches, instead of trying to do the whole table.

something like this:

SET ROWCOUNT 50000

WHILE 1=1

BEGIN

UPDATE myTable

SET SOMEVALUE = ''

WHERE SOMEVALUE = NULL

IF @@ROWCOUNT = 0

BREAK

END

SET ROWCOUNT 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kevinwinters Say Hey Kid Points: 696 More actions · Answer 4

Elliot W,

there is one process in the blk table that has a number - the command is "update", the pid is 53 but I am showing 54 as mine but that has to be it !

Pretty sure it is what you are referring to - what does "being blocked" mean and what could I do about it?

Am I stuck or do I wait it out?

Thanks

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

The way that is read is the row that has the value over zero is being blocked by the SPID listed.

I'm going to cut out a lot of columns for this..

[font="Courier New"]

spid blk

51 0

52 0

53 55

54 0

55 52

[/font]

In this case we see that 53 is blocked by 55, and 55 is blocked by 52, this makes 52 the "head blocker", we need to do something about 52, often times if I know what it is I just kill it.

You can often determine what it a spid is running by using:

DBCC INPUTBUFFER ( [spid] )

Back to our example, if we resolve the issue with 52 it will likely break 55 loose and let it finish, but if it doesn't then we may need to kill it too.

The long and short answer is blk means this spid blocked by spid listed.

CEWII

kevinwinters Say Hey Kid Points: 696 More actions · Answer 6

On my machine is says spid 53 and blk 53

so it looks like it is blocking itself?

ps. One Orange Chip Points: 29422 More actions · Answer 7

On very large tables, updating the table in batches is the best way to avoid blockings. I'd go with Lowell's suggestion to update your table in batches. You can definately control the batch size as defined in the first line.

Pradeep Singh

kevinwinters Say Hey Kid Points: 696 More actions · Answer 8

Could that technique work if I wanted to automate the process?, that is, make sure the whole table was done before I went on to another query. ( this blanking out is part of a 3-step query where this is the first step.

Or would I have to make this a manual process

Thanks

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

kevinwinters (7/10/2009)
On my machine is says spid 53 and blk 53
so it looks like it is blocking itself?

Do you have triggers on this table?

I have to admit I've never seem it blocking itself without triggers..

CEWII

kevinwinters Say Hey Kid Points: 696 More actions · Answer 10

No, no triggers, I simply use SQL Server to do some number crunching through queries and have been trying to automate the process. The query actually took 11 hours to finally complete. I guess it is going to be better to automate the creating of the column and setting the default value to empty string - can you tell me what that command would be ?

Thanks

kramaswamy SSCoach Points: 18181 More actions · Answer 11

well, not really sure what you mean by automate it, but if you want the script to create a column with a default value, it's:

ALTER TABLE [TableName] ADD [ColumnName] DEFAULT [DefaultValue]

Robert Klimes SSCoach Points: 17865 More actions · Answer 12

Do you have triggers on this table?
I have to admit I've never seem it blocking itself without triggers.

That is a very good question. I have seen this type of behavior when there is a trigger on a table updating an audit column for any time any other column is updated.

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

kevinwinters Say Hey Kid Points: 696 More actions · Answer 13

Kramaswamy,

When I do this: alter table test add testcolumn text default ''

I get a colum with "null" instead of blank

This isn't what I wanted, what did I do wrong?

Robert Klimes SSCoach Points: 17865 More actions · Answer 14

I believe that the default constraint will only work for newly inserted row not existing ones.

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]