Transaction Log 1

[cricketer1]

I am running huge update/insert queries on a very large dataset. Sometime into the query running process I get a message saying "due to low resources some of the records could not be committed"
also the size of my trasaction log has blown up. How do I manage this process. The DB server that I am running my queries on has lot of free hard drive space and is a double processor. Why am I getting these problems then??

How can I truncate the trasaction log more often without any problems?

Can anyone help

Thanks
Cricketer1

 

[orangey]

hi cricketer,

are you connecting to the server or running the query right on the server?

have you considered creating a stored procedure or dts package to run these queries with?

 

[Terpsfan]

There are different things you can do.

1- Allocate more space to the transaction log...and make sure you select Automatically Grow File.

2- In database options select Truncate log on checkpoint. This will truncate the log more often.

3- Back up the transaction log more often.

 

[cricketer1]

orangey,
thanks for your reply...
I have a terminal service connection to the database server from my desktop

and yes, I have enclosed the queries in stored procedures, but not a DTS package. Is DTS package any better, becuase I didn't feel the need for a DTS packiage in my case, the sp were doing the job.....please advice?

Though I am making use of cursors a lot....any advice on that, (the cursor fetches from a table of 1500 bytes and 200,000 rows)?
---------------------------
Omeg36,
thanks for your reply...
I have set the Transaction log to grow automatically, and there is a lot of disk space for it to grow

but i will set the select Truncate log on checkpoint in db options....

is there a way to have the transaction log backup itself after some time?

also I will put the same question to you, that I am making use of cursors which is fetching from a table of over 1500bytes in length and approx 200,000 rows.....would that have a signinficant performance drain on resources?

Thanks
cricketer1

 

[RickCole]

Your queries are fine, as they are n stored procedures.

Cursors are very inefficient and I would recommend using them only at a last resort and there is no other way to achieve what you want.

You can schedule a job or jobs to run log backups as and when you want using SQL Sever Agent.

Rick.

 

[stoolpigeon]

Just a quick note about Truncate on Checkpoint.

What this does is when transactions complete, the log drops them. They will not get backed up- they are lost.

Microsoft does not recommend using this option on a production database.

You can truncate your log by dumping it to backup. This allows you, if need be to recover up to your last transaction log back up.

 

[tlbroadbent]

When a Transaction Log is Truncated or Backed up only the "inactive portion of the log" will be truncated. Therefore, in order for these techniques to be effective transactions must be committed to the database. It is more effiecient to run "batches" of updates rather than attempting to update tens of thousands (or millions) of rows in one transaction.

If your SP does all inserts first, enclose the inserts in a transaction (Begin Trans ... Commit Trans). Upon commit, much of the transaction log will be cleared. To further reduce log size, backup the log following the commit. Then enclose the updates in a separate transaction followed by a log backup if needed.

I find that large insert, delete or update processes run even faster when I further reduce batch sizes. I will often set the ROWCOUNT to a value (i.e. 10000) and allow 10000 inserts in a single transaction. Then commit the transaction and process the next batch and so forth. Of course you need to be able to identify records already processed so they won't be inserted or updated again.

It usually isn't necessary to backup the log after each commit. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cricketer1]

tlbroadbent, thanks for your invaluable advice!!!.....you really helped me save a lot of time and also learn something new....

Thanks
cricketer1