x

I dont want transaction logs to be created for update statement

I have an update query which is updating 10000+ records. To improve the performance, I want no logs to be created for this update statement. How can I set the transaction log off for this update query. Scheduler regularly runs this query to update data.

Regards, Satish

more ▼

asked Nov 03, 2009 at 02:42 AM in Default

Satish gravatar image

Satish
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

Up to my knowledge you cannot turn off transaction log for update.

If update query affects different columns of each row, if transaction log is not there it won't rollback, if it couldn't be trusted to complete an atomic fashion or even at all if it were not being logged.

read this

more ▼

answered Nov 03, 2009 at 03:05 AM

anishmarokey gravatar image

anishmarokey
131

(comments are locked)
10|1200 characters needed characters left

There is no way to turn off logging.

10,000+ rows doesn't sound like much. If performance is not what you want then take a look at the execution plan to see if creating some indexes could improve things.

more ▼

answered Nov 03, 2009 at 03:36 AM

David 1 gravatar image

David 1
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left

I think you may have other things to worry about than the transaction log. Large updates (or many small ones) may cause index fragmentation and statistics that are out of date. This will affect performance in the end. You need to check for index fragmentation on regular basis. And you cannot rely on auto update statistics, because auto update will be triggered after 20% of the rows have been updated / inserted (a little simplified). Inccorect statistics may cause an ineffective execution plan.

I know, this was not the answer you were looking for, but you cannot turn of logging on updates. You can avoid logging in BULK INSERTS, but not updates or deletes.

more ▼

answered Nov 03, 2009 at 04:56 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

(comments are locked)
10|1200 characters needed characters left

I had a similar problem where I was updating or deleting millions of records. This would cause problems with log files as well as server performance problems when it was running the update/delete statements. As mentione above, updating fewer records is what I would recommend.

I got round the problem by getting the sql code to delete (or update) the records in batches of 5000 records. A loop was used to delete sets of 5000 records until there were no more to delete/update. This made a big difference with the log file and server performace. I used this approach when I was having to delete in excess 60 million records in one delete statement. This was much quicker and there was not a performance issue.

An example of the delete script I used is as follows (this could easily be modified to update as well as delete). So, if I had a millian records to delete, it would still acheive this automatically by deleteing in batches of 5000 records:

USE MyDb

DECLARE @MyCount As bigint

SET @MyCount = 0

WHILE 1 = 1

BEGIN

  DELETE TOP (5000)

  FROM MyTable

  WHERE <My Condition>

  IF @@ROWCOUNT < 5000 BREAK

  IF @MyCount > 250 BREAK

  SET @MYCount = @MyCount + 1

  Print 'Count is: ' + Str(@MyCount)

END

GO

Also notice that the above script has a break out so that if it attempts to delete more that 250 baches of 5000 records it stops.

more ▼

answered Nov 03, 2009 at 03:17 PM

Stuart Grace gravatar image

Stuart Grace
33 1 1 1

Good answer, it will help all the people that will do large update/deletes (10 000 records doesn't qualify as a large update :) but on the other hand the qeustion was about more than 10 000 ). One thing to remember anyway is the index fragmentation. If the columns in the where clause are indexed (I hope they are), it will be fragmented and the last deletes will take longer time to execute. But on the other hand, it may be the only way to delete millions of rows.
Nov 04, 2009 at 05:51 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

On top of the suggestions presented you may want to consider merely updating fewer rows per update.

more ▼

answered Nov 03, 2009 at 10:49 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x132
x68

asked: Nov 03, 2009 at 02:42 AM

Seen: 7240 times

Last Updated: Jan 11, 2013 at 04:05 PM