April 1, 2014 at 8:09 am
Can we change Simple recovery model to bulk logged recovery model in runtime, and again bring back to simple mode after the deletion process is over?
since while deleting the records my transaction files grows huge.
Is changing simple recovery model to bulk logged recovery model is good option in runtime for standalone database?
Thanks!
April 1, 2014 at 9:45 am
you can do that but its not going to help, log growth will be the same.
You need to break the delete up into smaller transactions.
---------------------------------------------------------------------
April 1, 2014 at 9:58 am
Please refer to this excellent article regarding Transaction Logs: http://www.sqlservercentral.com/articles/Administration/64582/
Bulk-logged is very similar to full recovery, except that in bulk-logged, bulk operations are minimally logged. When operations are minimally logged, much less information is written to the transaction log compared to when the operation is fully logged.
The advantage of bulk-logged recovery is that if there are bulk operations occurring, the impact those operations have on the transaction log is less than it would be if the database was in full recovery mode. However the transaction log backups may be much larger than the transaction log itself since the log backups include all data pages modified by bulk operations since the previous log backup.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 1, 2014 at 9:59 am
yuvipoy (4/1/2014)
Can we change Simple recovery model to bulk logged recovery model in runtime, and again bring back to simple mode after the deletion process is over?since while deleting the records my transaction files grows huge.
Is changing simple recovery model to bulk logged recovery model is good option in runtime for standalone database?
Thanks!
Break the delete transaction in to smaller batches and leave it in simple recovery mode itself.
--
SQLBuddy
April 1, 2014 at 10:14 am
yuvipoy (4/1/2014)
Can we change Simple recovery model to bulk logged recovery model in runtime, and again bring back to simple mode after the deletion process is over?
You can, but it's kinda a silly thing to do. All operations which can be minimally logged (which does not include deletes) is minimally logged in both simple and bulk-logged recovery. All that switching to bulk-logged gives you is an additional requirement to back the transaction log up (if you take a full backup)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply