|
There are two parameters on the code below you want to control based on your environment, 1) chane batch size: if you delete in small batch by controlling the number, you won't generate a huge transaction log, this will work with the second parameter. The key is to measure up what batch size and what waitime will work best for your server. If the transaction is fast enough, even if it creates lock-escalation to table-lock, other transaction will hardly occurrs the same time. or you can "alter table set lock_escalation disable" to keeo locking at row level. 2) change wait time: if you wait until next transaction log backup occurres (for example, every 15 minutes), the log file space will be again available for rotation. You will not run out of log space. Watch out you backup space though. You can use the same strategy doing insert, insert into select, delete, update, etc. |
use [Database] -- change database
name GO SET
TRANSACTION ISOLATION LEVEL SNAPSHOT set nocount on
--alter table set lock_escalation disable --optional declare @rowcnt
int; declare @rnum
int; declare @rIncnum int;
SET @rnum = 1;
SET @rIncnum = 405;
WHILE (@rnum < = @rIncnum)
BEGIN
begin tran D
delete top(10000) from dbo.mytable where created < convert(datetime, '01/01/2011', 101) -- change batch size
commit tran D
waitfor delay '00:00:30' -- change wait-time
SET @rnum = @rnum + 1;
Continue;
END
|