February 3, 2009 at 5:41 am
how can i delete records in sql without entry in transaction log, coz entrying in transaction makes the deletion process slower, but is there any way to directly delete records without entry in transaction log?
February 3, 2009 at 6:19 am
You want deletes in the transaction log. That's how you can do a rollback if there is a problem. Plus, transaction processing is an essential part of the system maintaining data integrity.
If you want to remove data without logging, you can't be selective, you can use the TRUCATE statement, but there are all kinds of limits on that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2009 at 6:42 am
If you are deleting an entire table you could use TRUNCATE TABLE, which only logs the page deallocations rather than having an entry for each row deleted. I'm not aware of any way to do deletions without any transaction logging at all.
Derek
February 3, 2009 at 7:25 am
Derek Dongray (2/3/2009)
I'm not aware of any way to do deletions without any transaction logging at all.
All data modifications in SQL are logged, one way or another. It's not possible to do any modification without logging it.
If it was it would not be possible to roll such a statement back and, if the DB crashed part way through, the result would be a transactionally inconsistent database.
hdpanjabi: Why do you think that the tran log is the bottleneck? Have you followed all recommendations regarding the placing and sizing of the log?
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
February 3, 2009 at 12:38 pm
[font="Verdana"]I'm guessing we need some idea of why you need to delete records without having the delete logged. If we understand the why, we may be able to give you more options.
You can use truncate table to delete every record, but only in the case where no other table has a referential constraint pointing to that table.
[/font]
May 13, 2009 at 5:19 pm
Hello,
I have the same problem, I need to delete thousands and thousands of records, I dont want to truncate the table...
the problem is that the log reach the 100GB and that slow down the DB
how I can delete without logging and no truncate the table? any option?
thanks in advanced,
May 13, 2009 at 5:45 pm
Hi Danky,
I think in your case the problem is that you're deleting all records in a single delete statement. SQL treats is as a single transaction and it's logged all together.
If your database is (or can be set) in simple recovery model you can try to split the deletion in smaller transactions. One way would be to use "set rowcount " to limit the number of records affected by a single statement execution. Then you run the statement over and over again until all the records are gone. You can check for @@rowcount=0 to see if there are no more records being deleted. Transaction log will be automatically truncated after each run.
May 14, 2009 at 7:57 am
if you don't run with full recovery you can do:
1. select into some temp table all the data you don't want to delete.
2. truncate your table.
3. select into your table the data from the temp table (if you don't have indexes and etc. you can only rename it and in step 2 you can simply drop the table).
May 14, 2009 at 8:59 am
Well, I for one agree with the OP - I have wanted the ability to do completely unlogged operations for a long time now. There are times or situations where it is truly unnecessary and wasted effort.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 5, 2009 at 11:43 am
We also have a situation where periodically we are need to bulk-delete an entire table (about 3 million records) and bulk-reload it from a file we receive from elsewhere. At this point it looks like the TRUNCATE solution will work for us. Unless somebody has a better idea.
(Also DROP TABLE with a re-creation is another option.)
October 5, 2009 at 11:48 am
It's easy to say I want to do this, but think about the times where you have an issue. What if you get halfway through and power dies? You could be in a problematic situation.
I understand that it seems unnecessary, but disk space is relatively cheap, even for a few TBs. I'm not sure it's worth taking the chance of being in a situation you can't easily recovery from?
October 5, 2009 at 11:51 am
wodom (10/5/2009)
We also have a situation where periodically we are need to bulk-delete an entire table (about 3 million records) and bulk-reload it from a file we receive from elsewhere.
Use TRUNCATE TABLE. That's precisely what it's there for - wiping an entire 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
October 5, 2009 at 3:43 pm
Steve Jones - Editor (10/5/2009)
It's easy to say I want to do this, but think about the times where you have an issue. What if you get halfway through and power dies?
If power dies, or other issue, no problem. Just start over with either the TRUNCATE or DROP, whichever is being used.
October 5, 2009 at 5:00 pm
I've heard a lot of people who should know better ask for this option. If MS ever implements it I think it should be:
SET CORRUPT_MY_DATABASE_AT_THE_WORST_POSSIBLE_TIME = True
October 5, 2009 at 6:38 pm
David Webb-200187 (10/5/2009)
I've heard a lot of people who should know better ask for this option. If MS ever implements it I think it should be:SET CORRUPT_MY_DATABASE_AT_THE_WORST_POSSIBLE_TIME = True
Ha!
Good one. And way too accurate. Although, I think you got the syntax wrong. Shouldn't it be set to = 1?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply