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

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 786110 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 5/2/2013 7:37:44 AM
Access Restriction Public

Description

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.