How to delete certain reocrds from a table whitout logging any records in Trans Log file

How to delete certain reocrds from a table whitout logging any records in Trans Log file

Post by Ahmed Jewaha » Fri, 25 Jan 2002 23:49:22



Dear all,

How can I Removes all rows from a table which match certain condition
without logging the any records in Transaction Log file.

I know truncate table which will reomve all rows. I need to remove only
rows which match certain condion.

Appreciate any of your tips and ideas.

Regards,

Ahmed

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

How to delete certain reocrds from a table whitout logging any records in Trans Log file

Post by Dinesh T » Sat, 26 Jan 2002 00:28:56


Ahmed,

You may need to look into the transaction log mechanism to get a clear
picture.That shld help in the long run.
For that pls refer
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q110139

As Sue mentioned in your earlier post ,you can consider doing the delete in
chunks, a number of records at a time..and in tandem monitor the log file
growth.

A very basic example from BOL...

 Use DELETE and a SELECT with the TOP Clause
Because a SELECT statement can be specified in a DELETE statement, the TOP
clause can also be used within the SELECT statement. For example, this
example deletes the top 10 authors from the authors table.

DELETE authors
FROM (SELECT TOP 10 * FROM authors) AS t1
WHERE authors.au_id = t1.au_id

Dinesh.


Quote:> Dear all,

> How can I Removes all rows from a table which match certain condition
> without logging the any records in Transaction Log file.

> I know truncate table which will reomve all rows. I need to remove only
> rows which match certain condion.

> Appreciate any of your tips and ideas.

> Regards,

> Ahmed

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!


 
 
 

How to delete certain reocrds from a table whitout logging any records in Trans Log file

Post by Roberto Carrasco ( » Sat, 26 Jan 2002 00:33:37


Hello Ahmed

A delete will always log information in the transaction log. Truncate table
on the other hand only logs information about the deallocation of the data
pages, which is minimum as compared to a regular delete. Thus, you can't
avoid logging information for a delete that matches certain rows of a
table. You may want to consider 'trunc. log on chkpnt.' (SQL 7.0) or
database recovery model 'simple' (SQL 2000).

Regards


This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 
 
 

How to delete certain reocrds from a table whitout logging any records in Trans Log file

Post by Ahmed Jewaha » Sat, 26 Jan 2002 00:53:33


Thanks for your help and really appreciate.

regards,

Ahmed

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Delete Trans Log File

My transation log file has grown to an enormous size, is
it okay to just delete the file?  Can SQL still run
without it and can it re-create another trans log file  
after one is deleted?

Any ideas?
Thanks...

2. SQL Server with 2 processor

3. DELETED trans log file, NO buckup ...

4. dbexport command

5. exchange and sql on same box ??

6. Two LOG File - how can I delete the 2nd log file

7. Change owner of a user tab|e

8. Log Shipping/Trans Log Backups

9. Trans Log not Logging

10. truncate log while there is a pending trans in the log

11. By-passing the trans log for batch updates/deletes

12. Deleted Trans Log