November 29, 2007 at 3:24 am
Hi,
Does anyone have a script/stored proc that will delete data in small batches at a time so that the transaction logs in the database does not grow very large.
Regards
IC
November 29, 2007 at 3:43 am
Imke Cronje (11/29/2007)
Hi,Does anyone have a script/stored proc that will delete data in small batches at a time so that the transaction logs in the database does not grow very large.
Regards
IC
You could do something like:
set rowcount 100
delete from mytable
set rowcount 0
this will delete 100 rows at a time. However, do you need to delete everything from the table? in which case truncate may be a good option.
What about deleting what you need, and take an extra transaction log backup, or schedule it a few log backups before taking a full backup.
Regards,
Andras
November 29, 2007 at 3:46 am
Thanks... I only want to delete specific data from a particular month
Regards
Imke
November 29, 2007 at 3:53 am
Imke Cronje (11/29/2007)
Thanks... I only want to delete specific data from a particular monthRegards
Imke
In this case "set rowcount" can work. Note that cascading operations are not adding to the affected rowcount limited by the above statement, so if you have foreign keys with "on delete cascade" for example, the referencing rows will be deleted as expected (even if the total number of rows would be over the limit set), so database consistency will be maintained.
Regards,
Andras
November 29, 2007 at 3:57 am
If you want to delete particular month data, use where clause in delete statement.
November 29, 2007 at 4:00 am
I am using a particular month..i just don't want the transaction logs to increase in size
November 29, 2007 at 5:59 am
Hi
I tried using this script below. But I'm getting the following error:
Incorrect syntax near the keyword 'BEGIN'.
create proc sp__Test
as
set rowcount 1000
while (select * from [Table] with (nolock) where createdt between '2007-04-16' and '2007-04-30')
BEGIN
delete from [Table] where createdt between '2007-04-16 00:01:00.000' and '2007-04-16 03:16:02.000'
if @@rowcount = 0
break
else
continue
checkpoint
end
What am I doing wrong? Please help!!!
Regards
IC
November 29, 2007 at 7:14 am
Imke Cronje (11/29/2007)
HiI tried using this script below. But I'm getting the following error:
Incorrect syntax near the keyword 'BEGIN'.
create proc sp__Test
as
set rowcount 1000
while (select * from [Table] with (nolock) where createdt between '2007-04-16' and '2007-04-30')
BEGIN
delete from [Table] where createdt between '2007-04-16 00:01:00.000' and '2007-04-16 03:16:02.000'
if @@rowcount = 0
break
else
continue
checkpoint
end
What am I doing wrong? Please help!!!
Regards
IC
Your condition in the loop was not set (it expects an expression that can be evaluated to a boolean).
Try something like:
while (exists (select * from [Table] with (nolock) where createdt between '2007-04-16' and '2007-04-30'))
Regards,
Andras
November 29, 2007 at 7:19 am
Imke Cronje (11/29/2007)
Hi...
set rowcount 1000
while (select * from [Table] with (nolock) where createdt between '2007-04-16' and '2007-04-30')
BEGIN
delete from [Table] where createdt between '2007-04-16 00:01:00.000' and '2007-04-16 03:16:02.000'
if @@rowcount = 0
break
else
continue
checkpoint
end
What am I doing wrong? Please help!!!
Regards
IC
Hi Imke,
sorry, while the condition would be fixed by my previous post, I'm a bit puzzled about what you are trying to achieve. Setting rowcount would limit the number of rows deleted in a single statement, but if you execute them in a loop, this limit is basically ignored and the benefit is gone. I though that you would like to delete a batch of certain number of rows, wait fore a scheduled log backup, do it again, ...
Doing it the way your stored procedure suggests would add almost the same number of log entries to your transaction log.
Regards,
Andras
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply