Can I/Should I turn off logging on some tempdb transactions?

I am doing some tempdb work for reporting only, and the trans log is getting to 5GB and I dont really see why I need rollback.

If it fails I can just tell the user and the #temp tables will be deleted when the SP returns anyway.

So, can I tell SQL Server not to bother with the transaction logging on these tempdb inserts/updates.

Have to admit something feels a bit iffy about doing this, but I cant see the problem logically.

Someone please tell me I'm being supid!!

There is noway to turn off logging. Logging is essential to maintaining data consistency and integrity. Though, there are cases where you do not need full logged, you might consider bulk or simple logged (for user database). See book online for more info.

Also, take a look at the following kb. This article lists a few reason why tempdb gets bloated and how you could shrink it. http://support.microsoft.com/kb/307487/en-us

|||

TempDb is set for a 'Simple' recovery model, and you cannot change the Recovery model in TempDb.

TempDb requires space, as you noticed, for the users to do their work. that space is released when the connection is released. It is often a 'struggle' to find the 'right' size for TempDb for normal operations. Reports can take a lot of space for gathering the data.

|||

actually, i was thinking of bulk-load/copy (aka minimal logged operations). so, i'm wrong on speaking of the recovery model. tks.

|||

Presumably I can only use bulk copy load for loading from external sources. I'd like to use it for inserting the return data from a table valued UDF.

Can someone explain WHY I need the transaction log in my case?

|||if I cant turn it off entirely, is there anyway of ensuring it is truncated between sections of code (I could then break up huge operations into smaler operations and truncate between them)|||

You can issue this command. Do your work in batches, and then truncate the log (I will sometimes batch 50k/100k rows at a time).

If other users are active, or if there are open transactions, this may not have any noticible effect.

Code Snippet

BACKUP Log TempDb
WITH TRUNCATE_ONLY