Transaction log growth - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Transaction log growth

Author

Topic

cradaa
Starting Member

2 Posts

Posted - 2011-04-05 : 11:29:59

There is a job running that does an INSERT of all rows from a view into a table.
The transaction log is growing out of control because of this job.

If I had them create an SSIS job to EXPORT the data from a view to the table, would that have the same effect on the transaction log?

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-04-05 : 12:12:04

There are several factors to this cause:

1. your recovery model is set to Full
2. you have very large transactions against this table / database
3. you have a lot of uncommitted or opened transactions, you can also have unreplicated transactions or even logshipping transactions waiting or database mirroring,
4. you have opened restoration of old logs,
5. you might be constantly shrinking your database
6. you might have some snapshots

cradaa
Starting Member

2 Posts

Posted - 2011-04-05 : 13:08:33

I am not actually responsible for the code of the job, just the server it self.

The job does an INSERT into an empty table via SELECT * from a_view.
The view is probably 4 million rows.

Someone suggested doing a BULK INSERT...

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-04-05 : 15:37:04

If you converted over to either BCP or SSIS, you can control the batch commit size which will shorten the size of the transaction. This would allow the checkpoint process (when in simple recovery), or a backup log (full/bulk_logged recovery) to mark that space in the transaction log as reusable.

The other option is to batch the insert into statement manually. This would be something like:

Declare @rowCount int;
Set @rowCount = 1;

While @rowCount > 0
Begin
Insert Into a_table (...)
Select Top(200000) ...
From a_view av
Left Join a_table at On at.PK = av.PK
Where at.PK Is Null;

Set @rowCount = @@rowcount;

-- Add a checkpoint or backup log here, if needed
End

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources