When a temp table is declared with not logging, the dml will not be logged in the transaction logs. However, the creation and dropping of the temp table will. This sound like the declare clause begins a transaction. My question is will a commit end the transaction or does it have to be a drop table statement? I know ending the connection or application will also end the transaction and drop the temp table.

Thanks

Temp tables stay around until either they are explicitly dropped (DROP TABLE SESSION.x) or the connection is terminated.

Andy

Transactions are always ended by a COMMIT or ROLLBACK - nothing else. Both operations can occur implicitly (auto-commit or terminating a connection, which triggers an implicit ROLLBACK).

And as Andy said, the existence of a temp table is tied to the connection (not the transaction).

I have two scenarios below. Which one(s) will caused a transaction full issue.

1. User runs an app which declares a temp table, but does not do a commit or drop table in its code. The user leaves the app open over night. There is enough activity from other apps to fill all the transaction logs. Will this result in a transaction full issue.

2. User runs an app which declares a temp table, but does a commit but not a drop table in its code before returning the result set to the user. The user leaves the app open over night. There is enough activity from other apps to fill all the transaction logs. Will this result in a transaction full issue.


Thanks

Filling up the transaction log is not dependent on whether you use temp tables or not. You will fill up the log by having enough open transactions performing work to fill them up. This can occur with just one transaction if it is large enough. Transactions left open for long periods of time will definitely lead to filling up the transaction log.

Andy