March 22, 2011 at 11:31 pm
Hi,
Is there any way to delete records from a large table in SQL Server 2008 "without writing transaction log"?
With Regards
Navaneeth.S
March 22, 2011 at 11:47 pm
If you want to delete the whole table use truncate stmt which has very minimal transactional logging.
March 22, 2011 at 11:56 pm
All data modifications are logged, always.
For minimal log impact, you could delete in a loop, delete 10000 or 50000 or whatever rows at a time, run the loop until all the rows that you want to remove are gone.
Or, if you're deleting everything in the table and the table is not referenced by foreign keys, truncate the table.
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
March 23, 2011 at 12:07 am
As others have stated, you can't turn off logging.
I wrote an article on deleting records. You should be able to find a link to it in my blog (link below).
March 23, 2011 at 12:24 am
Thanks for all your replies.
I don't want to delete all the records. So I can't use truncate statement.
Please share sample code for delete and truncate log in batches.
With Regards
Navaneeth.S
March 23, 2011 at 12:52 am
hi
Kindly find the sample code...hope this will help u...is there any better way of performing the delete operation, kindly let us know
Declare @count int
set @count =0
while @count<=2000
begin
delete from tablename where id in (select top 200 id from tablename where condition )
set @count=@count+@@rowcount
print @count
waitfor delay '000:00:05.000'
end
Regards
kokila K
March 23, 2011 at 12:56 am
hi
In this case,First u take backup of your database and then change the recovery model to simple
By which transaction log will not get save
After this you can delete the records without logging it into transaction log
Thanks
March 23, 2011 at 1:55 am
$QLdb@ (3/23/2011)
hiIn this case,First u take backup of your database and then change the recovery model to simple
By which transaction log will not get save
After this you can delete the records without logging it into transaction log
Thanks
Wrong, the delete is still logged when a database is using the simple recovery model.
As for sample code, did you go to my blog to find the link to my article that is on ssc?
March 23, 2011 at 2:04 am
$QLdb@ (3/23/2011)
change the recovery model to simpleBy which transaction log will not get save
After this you can delete the records without logging it into transaction log
That is completely incorrect. All data modifications are logged always, in all recovery models. There is no way inSQL to make any form of data change without it being logged.
If an operation could be unlogged, then if that operation failed and required a rollback SQL would be unable to do the rollback (rollbacks are processed from the log). Since a failed operation that can't be rolled back leaves the DB structure and data in an inconsistent state, SQL would have to immediately make the database suspect, requiring a restore.
As one of the SQL dev team has said 'The I in ACID does not stand for Inconsistent'
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
March 23, 2011 at 4:25 am
The only way to really "Delete" without generating log records is to use partitioning. We use this to switch out old data to an archive table then we can truncate the archive table and it's done.
It does need a little research and an initial hit on rebuilding the clustered index of the table in question, if you can do it though it works like a dream.
//Edit Obiously this is easier if you are deleting old records regularly based on an easily partitioned value
Cheers
Mat
March 23, 2011 at 5:01 am
mathew.walters (3/23/2011)
The only way to really "Delete" without generating log records is to use partitioning. We use this to switch out old data to an archive table then we can truncate the archive table and it's done.
Even that is logged, though what's logged is the metadata operation (the partition switch) and the page deallocations (truncate)
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
March 23, 2011 at 5:18 am
GilaMonster (3/23/2011)
mathew.walters (3/23/2011)
The only way to really "Delete" without generating log records is to use partitioning. We use this to switch out old data to an archive table then we can truncate the archive table and it's done.Even that is logged, though what's logged is the metadata operation (the partition switch) and the page deallocations (truncate)
Yes, I should have said it doesn't generate a massive amount of log records for the deletes. I assume the OP is trying to stop his transaction log growing by a large amount while he is performing a delete, if not then no there is no way to have zero log records generated when deleting records.
Cheers
Mat
September 19, 2011 at 12:59 pm
kokila.kondasamy (3/23/2011)
hiKindly find the sample code...hope this will help u...is there any better way of performing the delete operation, kindly let us know
Declare @count int
set @count =0
while @count<=2000
begin
delete from tablename where id in (select top 200 id from tablename where condition )
set @count=@count+@@rowcount
print @count
waitfor delay '000:00:05.000'
end
Regards
kokila K
Great advice, did exactly what I needed, thanks!
September 19, 2011 at 2:17 pm
tim.hulse (9/19/2011)
kokila.kondasamy (3/23/2011)
hiKindly find the sample code...hope this will help u...is there any better way of performing the delete operation, kindly let us know
Declare @count int
set @count =0
while @count<=2000
begin
delete from tablename where id in (select top 200 id from tablename where condition )
set @count=@count+@@rowcount
print @count
waitfor delay '000:00:05.000'
end
Regards
kokila K
Great advice, did exactly what I needed, thanks!
That will delete a specific number of records. 2000 in the sample given here.
select 1;
while @@rowcount > 0
delete top (1000) from dbo.MyTable Where (my where clause);
Something like that will keep deleting, 1000 at a time (which allows for reduced lock time), until there are no records that match the Where clause. If you have the database in Simple Recovery, it will not usually cause any significant log growth. Just make sure, if you change to Simple Recovery, that you take the usual steps to make your backup chain valid again afterwards.
Note that any of these methods are likely to result in significant index fragmentation, as with any large delete operation in a table that leaves some rows in the table. You might want to check on that once you're done with the delete. Stats may also suffer from an operation like this. It's a good idea to check on those afterwards.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 19, 2011 at 2:24 pm
GSquared (9/19/2011)
tim.hulse (9/19/2011)
kokila.kondasamy (3/23/2011)
hiKindly find the sample code...hope this will help u...is there any better way of performing the delete operation, kindly let us know
Declare @count int
set @count =0
while @count<=2000
begin
delete from tablename where id in (select top 200 id from tablename where condition )
set @count=@count+@@rowcount
print @count
waitfor delay '000:00:05.000'
end
Regards
kokila K
Great advice, did exactly what I needed, thanks!
That will delete a specific number of records. 2000 in the sample given here.
select 1;while @@rowcount > 0
delete top (1000) from dbo.MyTable Where (my where clause);
Something like that will keep deleting, 1000 at a time (which allows for reduced lock time), until there are no records that match the Where clause.
I'm sure you're right, but it looks to me like it would delete 200*2000 rows - deleting 200 on every loop. Then again, as my wife points out, I'm usually wrong...
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply