October 30, 2009 at 3:24 am
Hi,
I have this code below which should delete a list of tables in TempTable one at a time.
The problem is the date in the tables are so huge and as a result the transaction log is getting filled up.
Is there a way where I can improve this code so that it deletes certain amount of data each time so that the transaction log
doesn't get filled up half way through.
DECLARE
@TableName varchar (50)
,@Owner varchar (50)
,@System_Source varchar (50)
,@Syn varchar (50)
,@UW_Team varchar (50)
,@RowCounter INT
,@UpperLimit INT
,@sql varchar(1000)
,@Account_Month varchar (50)
,@Min_RowID INT
,@Max_RowID INT
SELECT
@Min_RowID = @Min_RowID,
@Max_RowID = @Max_RowID,
@RowCounter = 1
--Populate temp table with total number of records that will be used
IF OBJECT_ID (N'Tempdb..#Temp', N'U') IS NOT NULL
BEGIN
DROP TABLE #Temp
END
SELECT DISTINCT
TABLEID
,TableName
,Synd
,UW_Team
,SystemSource
,Owners
,Account_Month
INTO #Temp
FROM TempTable
WHERE TABLEID BETWEEN @Min_RowID AND @Max_RowID
--Get the total rows into a variable
SET @UpperLimit = @@ROWCOUNT
--SELECT @Max_RowID
WHILE @RowCounter <= @UpperLimit
BEGIN
SELECT
@TableName = TableName
,@Owner = Owners
,@System_Source = SystemSource
,@Syn = Synd
,@UW_Team = UW_Team
,@Account_Month = Account_Month
FROM #Temp
WHERE TABLEID = @RowCounter
-- Concatenate print the current account month in the variables.
PRINT 'START: DELETE ' + CAST(@TableName AS CHAR(50))
--Delete tables
IF @Owner = 'Test'
BEGIN
BEGIN
TRAN
SET @sql = 'DELETE FROM ' + @TableName + 'WHERE ' + @System_Source + ' = ''S227''
AND '+ @Syn + ' IN (''0155'',''2457'') OR ('+ @System_Source + '= ''S885'' AND '+ @Syn + ' IN (''0223''))
AND Account_Month_no = 200903'
EXEC (@SQL)
COMMIT TRAN
PRINT 'END ' + 'Data DELETED in ' + CAST(@TableName AS CHAR(50))
END
-- Move to the next row by adding 1 to the row counter
SET @RowCounter = @RowCounter + 1
END
October 30, 2009 at 3:58 am
If youare deleting all rows from the tables, Use Truncate command to accomplish your task.
OR
Try deleting in Batches.
This option is discussed in the following link.
October 30, 2009 at 4:03 am
I'm deleting a certain records in the table...also the link you provided doesn't work unfortunately
October 30, 2009 at 4:07 am
felix-659856 (10/30/2009)
I'm deleting a certain records in the table...also the link you provided doesn't work unfortunately
I have fixed the link that was provided to you.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 30, 2009 at 4:10 am
This is one way to work with batches:
If you don't switch to simple recovery, you log files may get filled up and even grow !
If you switch to simple recovery, first think about the consequences !!
-- Here's an example that you should be able to customize for your needs:
Declare @BatchSize int
Set @BatchSize = 5000 -- Modify as needed !!!
Set nocount on
declare @RowsDeleted bigint
Declare @MyRowcount bigint
set @RowsDeleted = 0
while 0 = 0
begin
DELETE top ( @BatchSize )
FROM <yourtable>
WHERE <your criteria>
set @MyRowcount = @@rowcount
if @MyRowcount = 0 break
select @RowsDeleted = @RowsDeleted + @MyRowcount
-- % = modulo
if 0.000 = @RowsDeleted % 100000.000 print convert(varchar(26), @RowsDeleted) + ' - ' + convert(varchar(26), getdate(),121)
end
Print '[' + cast(@RowsDeleted as varchar(15)) + '] Rows deleted.'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 30, 2009 at 4:13 am
the other thing is, what is your recovery model set to.
If it is set to simple, after every transaction/batch, the log will truncate/checkpoint and keep the log size down. if it is full you would need to do a log backup after each batch, to remove the log entries.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 30, 2009 at 4:15 am
The recovery mode is set to SIMPLE
Thanks
October 30, 2009 at 6:20 am
I'm getting compilation error when use your example code and the reason is due to 'DELETE top ( @BatchSize )', I'm using sql server 2000 and I believe this doesn't support this unlike sql server 2005.
Is there any way around this?
October 30, 2009 at 6:33 am
I posted my SQL2005 script, because you posted in the SQL2005 forum !
for sql2000 you can use:
SET NOCOUNT ON
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
DELETE TableName WHERE <Your Condition Here>
IF @@ROWCOUNT = 0 BREAK
CHECKPOINT
END
SET ROWCOUNT 0
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply