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

avatar image

Satish
11 1 1 3

(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

avatar image

anishmarokey
131 1 2 1

(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

avatar image

David 1
1.8k 3 5

(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

avatar image

Håkan Winther
16.5k 36 45 57

(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

avatar image

Stuart Grace
33 1 1 3

(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

avatar image

Blackhawk-17
12k 30 35 42

(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.

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:

x162
x79

asked: Nov 03, 2009 at 02:42 AM

Seen: 11581 times

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

Copyright 2016 Redgate Software. Privacy Policy