Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

delete records in sql without entry in transaction log Expand / Collapse
Author
Message
Posted Tuesday, February 3, 2009 5:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 16, 2009 1:14 AM
Points: 1, 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?
Post #648742
Posted Tuesday, February 3, 2009 6:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 13,885, Visits: 28,281
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #648771
Posted Tuesday, February 3, 2009 6:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:17 AM
Points: 1,274, Visits: 1,985
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
Post #648793
Posted Tuesday, February 3, 2009 7:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 40,193, Visits: 36,598
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 2008, MVP
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

Post #648832
Posted Tuesday, February 3, 2009 12:38 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
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.
Post #649221
Posted Wednesday, May 13, 2009 5:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 29, 2010 1:12 PM
Points: 14, 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,
Post #716565
Posted Wednesday, May 13, 2009 5:45 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:27 AM
Points: 1,174, Visits: 566
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.



Post #716576
Posted Thursday, May 14, 2009 7:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 17, 2009 5:33 AM
Points: 8, 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).
Post #716963
Posted Thursday, May 14, 2009 8:59 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 4,400, Visits: 6,261
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 at GMail
Post #717046
Posted Monday, October 5, 2009 11:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:35 PM
Points: 134, Visits: 192
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.)



Post #798089
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse