SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


delete records in sql without entry in transaction log


delete records in sql without entry in transaction log

Author
Message
hdpanjabi
hdpanjabi
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 2
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?
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40134 Visits: 32653
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
StarNamer
StarNamer
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1650 Visits: 1992
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87741 Visits: 45272
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Bruce W Cassidy
Bruce W Cassidy
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1283 Visits: 1033
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.

Danky 7
Danky 7
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 72
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,
bbychkov
bbychkov
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1206 Visits: 576
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.



Linur LiBi
Linur LiBi
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 73
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).
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12523 Visits: 8554
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
wodom
wodom
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 256
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.)



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search