Breaking up a data load to prevent log file growth

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Breaking up a data load to prevent log file...

12 posts, Page 2 of 2

««1 2

Breaking up a data load to prevent log file growth

Rate Topic

Display Mode

Topic Options

Author

Message

CapnHector

Posted Tuesday, December 04, 2012 5:25 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 935, Visits: 1,709

Jeff Moden (12/4/2012)

capnhector (12/3/2012)

Im coming here to get some options for breaking up a data load. the situation is that i have to expand a set of numbers to cover every number from 000 to 999 (1 becomes 1000-1999). there are 258 thousand input numbers and if i just do a cross apply to the numbers table it causes the log to balloon (a problem because i dont want to have a "one time shrink" every month). This is being loaded into a VARCHAR(16) column which is why the data types are CHAR()

I have to ask.... why are you creating 1,000 rows of storage for every row of input? What is the business reason for that? I'm asking so I can see if there's an alternative to loading so much data that might not get used.

its a business rule. we are expanding blocks of phone numbers where we get the first 7 of the phone number and need every number in the block. currently our dev team is in the process of trying to avoid this process by changing the code but as i have something in place that allows us to keep using the old method other fires are getting put out first.

in the old method our input file all ready had the blocks expanded when the provider sent us the data. this method was the easiest to get in place to contain the issue so we could look at it another day.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler

Post #1392747

CapnHector

Posted Thursday, December 06, 2012 11:00 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 935, Visits: 1,709

well the process ran yesterday evening with no ill affects. unfortunately i don't believe i can speed it up any more because of business rules about the data. Thanks for the help guys. if i get time in the next couple of months i may play with it a little more.

Post #1393654

« Prev Topic | Next Topic »

12 posts, Page 2 of 2

««1 2

Permissions