CODEWITHSUNDEEP
sql-server-2008
vbNewbie
vbNewbie

Reputation: 3345

sql log file growing too big

I have a table with 10 million records with no indexes and I am trying to dedupe the table. I tried the inserts with select where either using a left join or where not exists; but each time I get the error with violation of key. The other problem is that the log file grows too large and the transaction will not complete. I tried setting the recovery to simple as recommended online but that does not help. Here are the queries I used;

insert into temp(profile,feed,photo,dateadded)
select distinct profile,feed,photo,dateadded from original as s
  where not exists(select 1 from temp as t where t.profile=s.profile)

This just produces the violation of key error. I tried using the following:

insert into temp(profile,feed,photo,dateadded)
select distinct profile,feed,photo,dateadded from original as s 
left outer join temp t on t.profile=s.profile where t.profile is null

In both instances now the log file fills up before the transaction completes. So my main question is about the log file and I can figure out the deduping with the queries.

Upvotes: 0

Views: 310

Answers (2)

Diego
Diego

Reputation: 36176

the bigger the transaction, the bigger the transaction log will be.

The log is used for uncommitted recovery of an open transaction so if you’re not committing frequently and your executing a very large transaction, it will cause the log file to grow substantially. Once it commits, then the file will become free space. This is to safe guard the data in case something fails and roll back is needed.

my suggestion would be to run the insert in batches, committing after each batch

Upvotes: 1

HLGEM
HLGEM

Reputation: 96648

You may need to work in batches. Write a loop to go through 5000 (you can experiment with the number, I've had to go as far down as 500 or up to 50,000 depending on the db and how busy it was) records or so at a time.

What is your key? Likely your query will need to pick using an aggreagate function on dataadded (use the min or the max function).

Upvotes: 2

Related Questions