SQL Server Performance Forum – Threads Archive

No Logging during a job.

Does anyone know how to shut off transaction logging for a specific stored procedure, or job that is scheduled. There are some jobs we want to run, but are not concerned with logging the transactions, therefore for those few jobs we want to turn that option off for those specific ones, and keep logging for other operations…. Thx,
David Roesch

As far I know, you can’t do that.
I suggest to wait for others posts. Luis

Basically, you cannot. If you are doing bulk inserts from a source outside the database, and the db is in the "bulk logged" mode, only minimal logging will occur. If you are concerned about the size of your transaction log, set the db to "Simple" recovery mode in which case the t-log will get truncated each time checkpoint occurs. However, you need to do a full db backup after having your db in simple recovery mode if you want to do further transaction log backups after that. Essentially, if you have a single transaction (e.g. one insert, update or delete statement) which affects a large number of rows, the log needs to be big enough to cope, and nothing will allow it to stay small. T-logging is an fundamental part of an RDBMs which allows atomic transactions to be rolled back … the info about how to roll back a transacation has to come from somewhere, and that somewhere is the t-log. Otherwise, we’d have to use Access. Who’d want that? Tom Pullen
DBA, Oxfam GB

Thank you Tom that was very well explained, and no I dont want to use Access.. LOL We just we have some procedures that when they run we are not concerned about thier access, and therefore dont want the log history, but we do want verbose logging for the others. It does sound like the second option you mentioned would not be good for us, as we would have to turn on/off that option "-simple " then turn it back to normal when done, then have to launch a backup right after. Which sounds like a lot of work in the middle of the night. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Or did I mis-understand that..? Thanks again. David Roesch, San Diego

If it’s an overnight process, you’d need to do this:- 1. Set db to simple recovery mode
2. run your SP or scheduled task
3. Set db back to Full recovery mode
4. Take a full db backup
5. continue as normal, including transaction log dumps if required (and they will be required – because otherwise, the t-log just goes on and on growing under full recovery mode). If you are not concerned about point-in-time recovery, and are happy with nightly full backups, leave your database in simple recovery mode all the time. This is what I do with many of my smaller databases where the business users are happy with the fact that in the unlikely event of a disaster, they may lose a whole day’s work. Hope this helps. Tom Pullen
DBA, Oxfam GB

You can have a differential backup if you wanna save time. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Tom, yes it helps VERY much. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] that is what I had in mind after your initial suggestion. Thanks, David -David Roesch San Diego, Ca

]]>