July 20, 2006 at 3:50 pm
Are inserts into temp tables (#, ##) logged to the transaction log?
Is it possible to turn off transactionlog logging for a particular insert within a stored procedure? I'm looking for some command that would be issued at the beginning of a proc "do not log the following transactions" and then another command at the end to "turn it back on".
thanks!
July 21, 2006 at 11:34 am
Transaction logging is part and parcel of a relational database. This is what helps you maintain data integrity. Local temporary tables (#) and global temporary tables (##) are created in the tempdb. This database is in 'sinmple' recovery mode by default - I would not change this. If you need a different logging mode, e.g. 'bulk insert' then my advice is to create another database and change from temp tables to cataloged tables.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
July 21, 2006 at 1:02 pm
SELECT INTO #TEMPTABLE is treated just like a bulk insert.
Minimally logged operations (bulk load operations)
Data movement operations that require minimal logging in the transaction log. These operations include bcp, certain Data Transformation Services (DTS) operations, and SELECT INTO. Depending on the recovery model for a database, any of these operations might either be fully logged or minimally logged.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply