Exclude table from transaction log

[former_SQLA_member1695022]

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!

[regdomaratzki · Accepted solution]

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

[former_SQLA_member1695022 · Accepted solution]

Awesome, thank you very much!

[VolkerBarth · Accepted solution]

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

[timcheshire · Accepted solution]

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

[VolkerBarth · Accepted solution]

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

[former_SQLA_member1694901]

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

[timcheshire]

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.

[justin_willey]

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

[VolkerBarth]

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

[timcheshire]

Exactly what we do.