Solved

SQL delete script without creating huge transaction logs

Posted on 2009-03-30
16
1,570 Views
Last Modified: 2012-05-06
Hi All

I have the following SQL query I would like to run on a very large table to remove any rows more than 2 months old:

delete from tablename
WHERE logTime < dateadd(mm,-2,getdate())

Where tablename is the name of the table and logTime is the date the row was created.

Problem is when I run the query the transaction log grows to be gigantic - is there a way I can run the query without this happening?

Thanks
0
Comment
Question by:Elthamtech
  • 4
  • 4
  • 3
  • +3
16 Comments
 
LVL 7

Expert Comment

by:luani
ID: 24018636
You can put the database in simple mode(so it will not log anything), delete the records and the reset to FULL, this works if U have to do this one time not all the time....
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24018641
this should help:

while exists(select 1 from WHERE logTime < dateadd(mm,-2,getdate()))
begin
delete top(100) from tablename
WHERE logTime < dateadd(mm,-2,getdate())

waitfor delay '0:0:02'
end
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24018642
Sadly not. All delete commands are logged. You cant use truncate table as this clears the lot.

How many records are there in the table and how many are you looking to get rid of?

You could consider inserting all recs > 2 months into a new table; drop the old table and then rename the new table?

If you do the above; Please try it in a development environment first!

HTH
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 37

Accepted Solution

by:
momi_sabag earned 300 total points
ID: 24018679
changing the log mode won't help
you need  to break it into smaller transactions, for example try to perform this :

declare @rowcount int
set @rowCount = 1

while @RowCount > 0
 begin
 with a as (select top 5000 * from tablename
  WHERE logTime < dateadd(mm,-2,getdate())
  )
 delete from a
  select @rowCount = @@rowCount
end
0
 
LVL 7

Expert Comment

by:luani
ID: 24018801
>>changing the log mode won't help
have a try anyway, is the more simple and more fast solution of your problem ....

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24018860
simple and fast doesn't mean it is the best way.  it will cause you to not be able to recover to a point in time for your other transactions.
0
 
LVL 7

Expert Comment

by:luani
ID: 24018938
>>it will cause you to not be able to recover to a point in time for your other transactions.
Totally agree, he can always do a backup before everything ..but let him choose what's best for him......... ;)
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24018982
can also do this:

while exists(select 1 from WHERE logTime < dateadd(mm,-2,getdate()))
begin
delete top(100) from tablename
WHERE logTime < dateadd(mm,-2,getdate())

checkpoint
end
0
 

Author Comment

by:Elthamtech
ID: 24023547
I didn't expect so many responses! Very impressive, Thanks! In answer to a couple of your questions:

1. My database is already in simple mode
2. The table in question currently has 67112479 rows
3. The database is backed up using CommVault every night

I'm going to try a couple of your suggestions shortly, I'll let you know how I go
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24025421
>>My database is already in simple mode<<
At least that settles the why don't you put the database in simple mode for grins solution. :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24025431
>>The table in question currently has 67112479 rows<<
And more importantly how many would remain?
0
 
LVL 7

Expert Comment

by:luani
ID: 24026794
>>Problem is when I run the query the transaction log grows to be gigantic
In simple mode it should not be growing !?
anyway
put the record you want in a temp table,
select * into temp1  from tablename where WHERE logTime < dateadd(mm,-2,getdate())

--truncate table
truncate table tablename
and then re-insert the temp table records back to
insert into tablename select * from temp1
0
 

Author Comment

by:Elthamtech
ID: 24027131
acperkins: I'm guessing about 1/3 of the transactions will remain

luani:The database is definitely in simple recovery mode, and when I tried to execute my query the log file grew to be over 100gb


My concern with both those suggestions is because I'll be moving about a third of the transactions, the log will still get too big. Is there any other way?

Thanks
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 200 total points
ID: 24027940
Yes...the other way is to delete the records in increments like momi and I showed you.  
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24034844
>>I'm guessing about 1/3 of the transactions will remain<<
Copy them to a temporary table T_YourTableName with the same structure without any indexes or contraints.
Delete the original table YourTableName.
Rename the temporary table T_YourTableName to YourTableName
Add the indexes and constraints.

>>The database is definitely in simple recovery mode, and when I tried to execute my query the log file grew to be over 100gb<<
You know what they say: You can lead a horse to water, but you cannot make him drink.  Or would "beating a dead horse" be a better metaphore? :)
0
 

Author Comment

by:Elthamtech
ID: 24035602
Thanks heaps guys

I ran momi_sabag's query on my table last night and the log only grew to 150mb (not 100gb!)

Thanks to chapmandew for helping me release what I had to do.

I appreciate every ones comments but because I was only looking to delete 1/3 of the rows, I felt that copying to a temporary table wouldn't help that much.

Cheers
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Trying to get a Linked Server to Oracle DB working 21 59
Sql server insert 13 29
sql server concatenate fields 10 33
Update a text value in another table 10 39
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question