Update

Question

Post reply

Update

yvr4

Default port

Points: 1450
More actions
October 20, 2003 at 5:50 am

#83334

The following update command running more than 40 hours, still running
Update LFGData1.dbo.[Main Data Table]
Set [Remove from List] = 1,
RemoveFromDialer = 1,
[OriginatOR ID] = 978 ,
[Lead Date] =GetDate()
from LFGSQL2.NoCallList.dbo.aaGLOBAL KY, LFGData1.dbo.[Main Data Table] MDT
Where KY.PhoneNumber = MDT.Phone
Main Data Table has 95 million records and aaGlobal as 52 millions records and both table in phone column have non cluster index.
I am doing some thing wrong, i don't know. any body help me. this is very urgent.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

http://support.microsoft.com/?id=309182

In particular, this:

quote:
The query must be a simple UPDATE/DELETE against a single remote table.
All selection criteria (in other words, predicates in the WHERE clause) must be remotable. For example, if the query includes the filter "WHERE col1 = 'abc'" and the remote server is not collation-compatible, SQL Server 2000 can't remote the UPDATE as a Remote Query because it can't trust the remote server to use the correct collation when deciding which rows to update.
The local server must be SQL Server 2000, and the remote server must be either SQL Server 7.0 or 2000.

--Jonathan

yvr4 Default port Points: 1450 More actions · Answer 2

Both servers Sql server7.0 Standard Edition and Both server as raid 10. both as linked server. Can stop this process and how much time will take for rollback.

yvr4 Default port Points: 1450 More actions · Answer 3

Experts, please give me any suggestions for me. can i stop process or continue? Please help me.

Jonathan SSC-Insane Points: 20427 More actions · Answer 4

quote:
Both servers Sql server7.0 Standard Edition...

I thought something like that was the case. A quote from the article I linked to:

quote:
For linked server DELETEs or UPDATEs, SQL Server retrieves data from the table, performs any filtering that is necessary, and then performs the deletes or updates through the OLEDB rowset. This processing can result in a round-trip to the remote server for each row that is to be deleted or updated.

quote:
Can stop this process and how much time will take for rollback.

You can kill the processes of course. Rollback should be shorter than the time you've now spent, as there would be no linked server involved. It would be faster to just restore to the state previous to the query if you're in single-user mode.

Perhaps the best workaround in SQL Server 7 would be to bulk copy the aaGLOBAL table onto the LFGData1 server first.

--Jonathan

yvr4 Default port Points: 1450 More actions · Answer 5

Thanks for reply. I cancelled process just now. now roll back running. i don't know, how long run this roll back process.

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 6

Because of time constraint and the number of record in each table, you could also copy the table over to the server that you want to grind. I was able to move 1 million row per minutes. once you get the table over you could easily join them. This way it could be much less time then you could think. by the way I didn't use dts. I would create a new table, and then do

insert table_a

select * from linkserver.database..table

Note: You also want to slice down your number of rows to about 2 or 3 million per query this way it won't failed. To make life better it is better if you write a script that would divide the record up and also insert. Then it is done you would have the full table. Here is a sample code for you to use to divide it up and also does an insert.

declare @max-2 int, @min-2 int

SELECT @max-2 =max(Day_id) ,@min = min(Day_id)

from linkserver.dbname.dbo.history

while @min-2 <= @max-2

begin

insert data_historyXXXX

select * from linkserver.dbname.dbo.historyxyz

where day_id >= @min-2

and day_id < @min-2 + 1000000

set @min-2 = @min-2 + 1000000

select @min-2

end

Good luck.

mom

yvr4 Default port Points: 1450 More actions · Answer 7

Still roll back running, process stopped around 6:30am.If i stop and start the Sql services services , how long it will take recover database. Database size is 94GB.

Jonathan SSC-Insane Points: 20427 More actions · Answer 8

quote:
Still roll back running, process stopped around 6:30am.If i stop and start the Sql services services , how long it will take recover database. Database size is 94GB.

Recovery would just need to finish the rollback anyway.

--Jonathan

yvr4 Default port Points: 1450 More actions · Answer 9

Jonathan,

Can i wait until finish roll back? any advice.

Thanks for hreply.

Jonathan SSC-Insane Points: 20427 More actions · Answer 10

quote:
Jonathan,
Can i wait until finish roll back? any advice.
Thanks for hreply.

If you cannot restore a backup, you must wait out the rollback.

--Jonathan

cneuhold Hall of Fame Points: 3375 More actions · Answer 11

hi!

in general, you should break up updates *that* big into poritions of a considerable size, say 1 million rows each. do this by setting rowcount before your update statement:

set rowcount 1000000

make sure your update statement has a predicate that will not allow updating rows that are already in the state you want. after your batch is complete, reset rowcount by setting it to 0, otherwise the limit will stay on.

best regards,

chris.

yvr4 Default port Points: 1450 More actions · Answer 12

Thanks for reply. Still Roll Back running.I don't how long i will wait.

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 13

yvr4,

I know this is old but I am wondering how are you are your database doing? were you able to get your stuff done?

mom