Disk usage question

[djj55]

Hello, SQL 2000, if I do an update query on a large table should the disk usage go to 100 percent for the whole time it is running?

It seems to keep other activities from happening i.e. the Access front end times out.

Thank you,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[SQLBill]

It can...a lot depends on how powerful your system is. And don't forget that an UPDATE is really a DELETE and an INSERT. SQL Server has to keep track of everything in case you need to rollback the changes before they are committed.

I suggest 'breaking' down large transactions into smaller batches. Do a couple thousand at a time and COMMIT the changes before you start the next batch.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[djj55]

Thank you for the replay.

How would I go about "breaking up" the following.

UPDATE mytable
SET mycolumn = LEFT(mycolumn, LEN(mycolumn)-1)
WHERE ISNUMERIC(RIGHT(mycolumn, 1)) = 1

I know how to use the TOP command on a SELECT but do not know how to handle breaking up an UPDATE.

Thank you,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Take a look here for some ideas.

faq183-3141

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thanks George.
That should get me going. I have several places that will come in handy. As I said the insert I understood the update was more complicated.

Thanks everyone,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Let me know if you need more help on this.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Hello, does the SET ROWCOUNT affect all users or just the session that calls it?

Thank you,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Just the session that calls it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thanks

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!