April 22, 2010 at 11:45 pm
Hello Experts,
I have a job running at night everyday.
It pulls 2.5 million data from an oracle linked server and insert into tables in SQL Server 2005 datbase.
My question is What all problems it can cause apart from being slow.
I mean... Can it cause major problems like server crash etc..??
Thanks.
April 23, 2010 at 12:29 am
Joy Smith San (4/22/2010)
Hello Experts,I have a job running at night everyday.
It pulls 2.5 million data from an oracle linked server and insert into tables in SQL Server 2005 datbase.
My question is What all problems it can cause apart from being slow.
I mean... Can it cause major problems like server crash etc..??
Thanks.
Server crash depends on the processing power of the sytem upto some extent, or there may be other reasons
do check the transaction logs time to time
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
April 23, 2010 at 12:42 am
Thanks
Server crash depends on the processing power of the sytem upto some extent
You mean to say CPU capacity..?
Idealy what should be the configuration in cases like this?
April 25, 2010 at 11:01 pm
Dear All,
I am still waiting for more responses ..!!!!
Thanks.
April 27, 2010 at 2:35 am
Still nobody has responded..!
Steve, Gila.. No comment on this topic.?
Thanks.
April 28, 2010 at 12:26 pm
In terms of "what could go wrong," besides being slow, I'd be worried about space in your transaction log. I can't tell from your description but if you're using a single INSERT based off a single query, you're going to have a very large log file and could potentially run out of disk space on the hard drive where it resides.
The alternative is to batch up the inserts in a loop, first setting the database recovery mode to Simple. If you do this (and we're assuming you're not replicating the table), then the log space used will be as much as required for one pass through the loop.
You'd have to play with the loop to see what a good batch size is in your circumstances. For large inserts like this I always try inserting 10,000 records at a time and try alternate (higher or lower) batch sizes if performance is terrible.
April 28, 2010 at 10:14 pm
In terms of "what could go wrong," besides being slow, I'd be worried about space in your transaction log.
That's OK. It runs only at night, when users are not using the application at all. Transaction log is also maintained by client's DBAs properly.
Any other issues can happen.? As I asked server crash etc..?
April 30, 2010 at 6:04 am
The problem is, you're asking the question in a vacuum of information. Maybe if you tell us what point you're trying to prove or disprove, we could assist you better.
Server crashes usually have so many other issues associated with them that one cannot pinpoint that X problem will cause one alone. And SQL Server is designed to handle high volumes of input.
If you're worried about information overload and your database Recovery Mode is FULL, change your it to Bulk-Logged during the import, then flip it back to FULL after the import is done. That will help with the processing issue at least.
As far as problems, all I can say is don't borrow trouble before it decides to haunt you. The record count you're listing is almost average for a VLDB data warehouse job (or below average even). Lots of people do even bigger data transfers without issues.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply