Home Dashboard Directory Help

Dedicated Transaction Log for a single Query or Transaction by Colt Taylor


 as Won't Fix Help for as Won't Fix

Sign in
to vote
Type: Suggestion
ID: 786110
Opened: 5/2/2013 7:37:44 AM
Access Restriction: Public


When running a large database in an environment challenged by limited hard-drive space, simple mode logging can be used to save space. If you then need to selectively purge a large number of rows using a delete query (not truncate), drive space has to be found for the unavoidable log growth that results from the delete. An obvious solution is to get some more drive space (either physically or in the cloud), detach the log and attach to a new log on the newly acquired drive space. A few minutes later, when the purge is complete, you can then repeat the detach and reattach process, moving the logging back to its original location and then get rid of the additional storage. Aside from the down time and the human effort, that is an acceptable solution.
My suggestion is that SQL Server be enhanced to eliminate both the down time and the human effort.

Sign in to post a comment.
Posted by Microsoft on 5/6/2013 at 11:47 AM
The transaction log is global to all operations in the database. It's not feasible to segregate out the logging related to a single transaction.
However, in addressing your core issue, it is possible to add multiple files to the log. These are utilized as continuations of the previous log file.
An additional log file could be created for the large transaction, and when you are finished, you can use DBCC SHRINKFILE with EMPTYFILE to get rid of the additional log file. These are online operations, so there is no downtime.
Sign in to post a workaround.