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