November 26, 2007 at 7:34 pm
Hello all,
I am routinely performing transformations and model scoring on tables that are ~150mln records by several hundred columns of extract data that does not change in SQL2005 (Data warehouse situation, not OLTP). All columns are imported as varchars' b/c the extract delivery is different each time I receive it.
My tables have an identity column, and I created a while loop to move along the ID's but than my process extends from ~20hours to 3 days. I am not sure that a Try and Catch will do it either.
When queries crash SQL rolls back everything to its original state. This process can take hours and is a waste of time. I much rather restart the whole process or just drop the last few records. Is there some way that I can achieve this response?
Your inputs are much appreciated!
November 27, 2007 at 5:55 pm
You can't interrupt a query that's rolling back. You have two options:
1) change the query so it doesn't 'crash'. What do you mean by that?
2) break up the operation into smaller batches so that rollback doesn't take so long.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 28, 2007 at 5:32 am
'Crash' means TCP network errors, due to maintenance or power fluctuations, that sort. That stuff happens when queries run a long time.
SQL logfiles running out of space (Iknow this is my mistake, but that too is part of life).
I will be looking into batching some more.
Thanks for the help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply