question

AJ123987 avatar image
AJ123987 asked

Joining large tables - transactional log full due to active transaction error

2 large tables Table 1 60 million records Table 2 10 million records I want to join both tables together however I keep getting error message the process runs for 3 hours then comes back with the error message the transaction log for database is full due to 'active_transaction' the autogrowth is unlimited and I have set the DB recovery to simple The size of the log drive is 50 GB I am using SQL server 2008 r2 Can anyone help me resolve this ? My SQL query I am using is Select * into betdaq.[dbo].temp3 from ( Select XXXXX, XXXXX, XXXXX, XXXXX, XXXXX from XXX.[dbo].temp1 inner join XXX.[dbo].temp2 on temp1.Date = temp2.[Date] and temp1.cloth = temp2.Cloth and temp1.Time = temp1.Time ) a
tsql
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Shouldn't that be `temp1.time = temp2.time`? Is that a typo here, or in your query?
0 Likes 0 ·
AJ123987 avatar image AJ123987 commented ·
apologies that was a typo, you are correct still have the transactional log full due to active transaction issue any way you can help would be much appreciated
0 Likes 0 ·

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
One thought re: transaction log. Just because the database is set to SIMPLE recovery, doesn't mean that the transaction log doesn't get used. The Log does get used, but the space is automatically reclaimed at the end of each transaction. So, if your transaction log can't grow to a big enough size to cope with the transaction you're attempting, then you'll get a failure. Now then, as to how to fix this problem... I reckon the options include: 1. Give the server the disk space required. 2. Is it possible to reduce the amount of data being joined by, for example, a few WHERE clauses? 3. Chunk the process - Limit the data being transferred at any one time - for example, run the query but only for specific date ranges, eg a year at a time
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.