Using Delete and wanting unlogged PART II

Using Delete and wanting unlogged PART II

Post by mike » Fri, 29 Jan 1999 04:00:00



I have a similar problem.  I also want to delete some records from a table
but I don't have a problem with the syslogs necessarily.  This table is
published and I don't want the delete to replicate to the subscribing
servers.  I can't truncate the table, I just want to delete records older
than a specific date.

Does anyone know how to do a "DELETE .... WITH NO_LOG" or equivalent?  

Mike

 
 
 

Using Delete and wanting unlogged PART II

Post by Steve Robinso » Sat, 30 Jan 1999 04:00:00


mike,

you can not do a delete with no log but you can stop them replicating.

Simply do the following out of hours.
    Run the logreader
    Run the distribution tasks to make sure replication has worked (This
must be run until successful)
    Unsubscribe the subscribers from the table in question
    Do the deletes on the table
    Re-subscribe the table with a sync type of none

This would work ok but there can be no one else doing transactions on that
table otherwise they may be lost between the unsubscribe and the resubscribe
with no sync.

Steve Robinson
SQLServer MVP



>I have a similar problem.  I also want to delete some records from a table
>but I don't have a problem with the syslogs necessarily.  This table is
>published and I don't want the delete to replicate to the subscribing
>servers.  I can't truncate the table, I just want to delete records older
>than a specific date.

>Does anyone know how to do a "DELETE .... WITH NO_LOG" or equivalent?

>Mike


 
 
 

1. Using Delete and wanting unlogged

I have SQL server 6.5 running on NT server 4.0 -

I have a 3 million+ row table, and I am wanting to archive off some records.

I created a new database and used a non-logged "select into" to get the
records off that I want to archive.

I then backed this database/table off to tape,  and deleted it.

Now I need to get the records off the original table.

During the course of running a "delete .. where" statement,  my syslogs
filled up,  even though I checked truncate log on checkpoint.

Has anyone out there done anything similar, and discovered a better way?  I
need help !!!!

Thanks in advance.
Steve Rogers
 (remove the "saveme" to reply)

2. running paradox3 on Windows ME

3. Deleting a part with out deleting the fields

4. Autooptimizing the SGA

5. Need Help on SQL Server 6.5 - Part II

6. Sub summary question

7. EXCEPTION_ACCESS_VIOLATION Part II

8. Generate Temp Table Names?

9. Advanced MDX Question: Filter on Member Property Part II

10. Group sometimes, part II

11. Stored Procedure Query Too Slow Part II

12. Selecting counts based on min Part II

13. Help needed on creating SQL-statement (part II)