Is there any way to exclude a specific table from the transaction log?

The table in question is frequently being deleted/inserted with a massive amount of data. Every time this occurs,the log grows by about 5GB.

Thanks for your inputs!

asked 19 Dec '22, 14:52

mrtd's gravatar image

mrtd
61116
accept rate: 0%


No.

If you are always deleting all rows from the table, you could consider the use of the TRUNCATE TABLE command instead of DELETE, and you could use the LOAD TABLE command instead of INSERT to add data into the table.

TRUNCATE TABLE and LOAD TABLE (by default) do not log individual rows to the transaction log, as can be seen below.

Reg

BEGIN TRANSACTION
go
--SQL-1005-0000009723
truncate table "dba"."admin1"
go
--COMMIT-1005-0000009764-2022-12-19 14:57:10.960751
COMMIT WORK
go
--BEGIN TRANSACTION-1005-0000009843
BEGIN TRANSACTION
go
--BEGIN LOAD TABLE-1005-0000009846: load into table "dba"."admin1" using file 'a1.dat'
--SQL-1005-0000009900
begin
  set temporary option "date_order" = 'YMD';
  set temporary option "nearest_century" = '50';
  set temporary option "time_zone_adjustment" = '-300';
  load into table "dba"."admin1" using file 'a1.dat' encoding 'windows-1252';
  set temporary option "date_order" = ;
  set temporary option "nearest_century" = ;
  set temporary option "time_zone_adjustment" = ;
end

go
--END LOAD TABLE-1005-0000010289
--COMMIT-1005-0000010292-2022-12-19 14:57:31.617576
COMMIT WORK
go
--CHECKPOINT-0000-0000010303-2022-12-19 14:57:41.781593
permanent link

answered 19 Dec '22, 15:03

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.7k343118
accept rate: 37%

edited 20 Dec '22, 08:29

Awesome, thank you very much!

(19 Dec '22, 15:56) mrtd
1

Just to clarify: LOAD TABLE allows for logging of individual rows or file contents via its options, it just does only log the file name by default when using "FROM filename...".

(20 Dec '22, 01:51) Volker Barth
1

If I remember correctly, TRUNCATE TABLE initiates a COMMIT which may interfere with other processes.

(20 Dec '22, 06:13) TimCH
Replies hidden

Just another clarification: TRUNCATE TABLE does only omit individual DELETE statements in the log when doing a "fast truncate". If if cannot do a "fast truncate", it pretty much behaves like a DELETE statement without a WHERE clause AFAIK w.r.t. the log. (AFAIK, there are still other differences to a real DELETE regarding locking and the firing of triggers...) See the docs for the "gory details"...

(21 Dec '22, 03:30) Volker Barth

Has anyone tried using a GLOBAL TEMPORARY TABLE with NOT TRANSACTIONAL clause for this?

From the description in the docs I'd expect it to do something very similar...

permanent link

answered 20 Dec '22, 05:40

tedfroehlich's gravatar image

tedfroehlich
38071123
accept rate: 20%

3

Yup. I use GLOBAL TEMPORARY TABLE with NOT TRANSACTIONAL a lot. You can also use DELACRE LOCAL TEMPORARY TABLE in stored procedures with NOT TRANSACTIONAL.
I use these a lot to manipulate data without hitting the transaction log. You can create indexes on them too which improves performance on joins.

(20 Dec '22, 17:19) TimCH
2

Yes, me too. Very useful with large temporary data sets where recovery is unimportant.

(20 Dec '22, 19:08) Justin Willey
2

...with the SHARE BY ALL clause, if it is menat to replace a base table, probably :)

FWIW, for ETL needs, we do use LOAD TABLE into local temporary tables with NOT TRANSACTIONAL very often, then usually comparing the "freshly imported data" in these temporary tables with that of according permanent tables, then merging only the changes into the latter ones, thereby reducing both the amount of data modifications and of log growth.

(21 Dec '22, 03:08) Volker Barth
Replies hidden
1

Exactly what we do.

(09 Jan '23, 06:31) TimCH
Your answer
toggle preview

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

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×59
×19

question asked: 19 Dec '22, 14:52

question was seen: 644 times

last updated: 09 Jan '23, 06:31