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

Can I shrink Trans log even if doing log-shipping?

I have a 2000 DB which is log-shipping to another server. The log has grown huge and needs to be made smaller. Can I use DBCC SHRINKFILE without damaging the log-shipping? I know that truncating the log destroys log-shipping, but shrinking should logically not cause any harm. Do you know? Is there a better way to reduce the size of the log file without damaging log-shipping?

Thanks!

Hi,

DBCC shrinkfile with default options should be ok to shrink the transaction log.

Jag

|||

I don't know if this is functionally the same, but I selected "AutoShrink" in EM / database/properties/Options and the trans log file shrank and log shipping appears to be functioning well.

Thanks.

|||

Hi

The database property autoshrink allows a database to shrink automatically at 30-minute intervals. The effect is the same as doing a DBCC SHRINKDATABASE (dbname, 25). This option leaves 25 percent free space in a database after the shrink, and any free space beyond that is returned to the operating system.

This can be a bit dangerous for transaction logs, as at any point you might have empty transaction log, so the autoshrink will make the transaction log really small. This will result in transaction log full errors or increased amount of shrinks and growths , if you have autogrowth on, which can result in poor perfromance.

The best option is to use DBCC Shrinkfile and specify the required file size.

Jag

|||

jag,

Thank you for a very nice reply. It is very helpful and gives me a lot to follow-up on.

Michael