Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I've been passed a sql server scheduled job that's recently started failing.

It basically runs a stored proc that runs a query, the query joins a bunch of tables together and populates a derived table with the results. The query pulls back about 5 years worth of data which weighs in at about 23 million rows.

According to the DBA's the job is filling the transaction log to capacity and then falling over. I'm not really surprised to be honest with that amount of data, but I've been explicitly told not to limit the results being copied into the table.

So my question is, is there any strategy I could use to populate the derived table without making the sql server instance explode in a shower of sparks?

Thanks in advance.

share|improve this question
If your transaction log is indeed full, then you would have more failure in the database than just the job. What does the SQL Server Agent error log say about the job?? What's the exact error message? – Thomas Stringer Dec 1 '11 at 14:30
Is this MSSQL Server 2005 or 2008? – BryceAtNetwork23 Dec 1 '11 at 14:30
Sounds more like Temp space is being exceeded to me. Is it possible to run the query more than once for different date ranges? – Lumpy Dec 1 '11 at 14:39
1  
Can you post the query? Some operators use excessive tempdb (like DISTINCT and ORDER BY) and could be eliminated or reduced. – JNK♦ Dec 1 '11 at 15:26
It's a sql server 2000 instance. I think it is the temp db filling up not the transaction log yes. – PhilH Dec 1 '11 at 15:54

migrated from stackoverflow.com Dec 1 '11 at 14:32

2 Answers

up vote 8 down vote accepted

Ideas:

  • Put the derived table into a separate database that runs in simple recovery mode (still has to be logged for rollback though)
  • Batch the write based on, say data
  • Offload processing to SSIS which can batch it for you
share|improve this answer
When you say batch the write, do you mean something like splitting the results set up into years, and inserting each years worth of data individually? – PhilH Dec 1 '11 at 16:18
@PhilH: yes. Break it down into several smaller write – gbn Dec 1 '11 at 16:45
+1 Anytime the tran log is filling up, you either have to find a way to minimally log (sqlmag.com/article/tsql3/minimally-logged-inserts) or split the processing up into manageable chunks. You can use the advanced destination manager in ssis to seamlessly split the batch up, but this is still part of the manageable chunks arguement. – brian Dec 2 '11 at 3:34
up vote 0 down vote

If it's possible, try temporarily changing the database to simple recovery mode, and make sure to do explicit transactions to break up the data loads. For example, if you know something will insert 1 million+ records, break it up to insert 100k at a time instead.

This should keep the log file from getting ginormous from the data being processed.

share|improve this answer
4  
Then you need a full backup afterwards because the LSN sequence is now bollixed. Changing recovery model is almost always a bad idea – gbn Dec 1 '11 at 15:29
@gbn, true, didn't think about that – DForck42 Dec 1 '11 at 16:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.