June 14, 2003 at 12:21 am
Is there a way to turn the transaction logging off in T-SQL stored procedure and then turn it back on, after the transaction is complete? I have a stored proc that has several nested cursors inside of it, which seems to overfills the log file. Thanks in advance.
Edited by - levv on 06/14/2003 12:22:18 AM
June 14, 2003 at 2:30 am
You can perform non-logged operations in certains situations, but not for the scenario you've described I'm afraid.
Cheers,
- Mark
June 14, 2003 at 8:08 am
You could try setting the recovery model to simple prior to running the nested transactions, and then resetting the option after the transaction has completed. When the simple recovery model is used, transactions are truncated from the log. SQL Server keeps a track of current transactions, and then removes them from the log on checkpoint. Btw MS recommends that you avoid using simple on production servers.
June 15, 2003 at 4:19 pm
Could not find the thread on this website, but there was a thread discussiong the merits of cursors versus set based solutions.
If time/resources allow maybe worth while looking at the alernative. Sounds like the current solution will hammer your production server aswell.
June 15, 2003 at 4:48 pm
Thank you all, guys. Unfortunately, this particular database is pretty much overnormalized, so for now it's the only solution. We are looking into denormalizing it partialy for reporting purposes. Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply