Borrado de tablas sin que afecta el Log de SQL Server

[RsOfT]

Como puedo borrar una tabla sin que me llene el Log de mi Servidor SQL Server.

No puedo usar Truncate al borrar, ya que mi base de datos replica.
¿Qué ideas tienen?

[Mithrandir]

¿Te sirve/interesa el LOG? Si no, entonces puedes pasar la DB a Simple y no tendrás problemas con el LOG.

Si la DB es replica no creo que tenga utilidad el log, puesto que tienes un lugar de donde volver a obtener el 100% de los datos.

Yo en tu lugar cambiaría el Recovery Model a Simple

[RsOfT]

Si me interesa el Log.
Lo que pasa es que la replicación la tengo a nivel de Hand Held no a nivel de otro servidor.

Te explico. Estas tablas que quiero borrar sin que me afecte el Log es las tablas que sincronizan al Hand Held y no me interesan que graben el Log porque las borro todos los días y las lleno con información nueva.

A ver que otras ideas tienen!

[Mithrandir]

Creo que si no puedes hacer el TRUNCATE no te escaparás de que lo registre el LOG.

Creo que tampoco lo permite, ¿pero has visto la opcion de Bulk-Logged? no registra las operaciones de Bulk (BCP, INSERT INTO), con un poco de suerte el DELETE sin WHERE tampoco lo registra (aunque lo dudo)

[RsOfT]

Los Delete los hago sin where condition y si llena el log.

Por otro lado, no se a que te refieres con Bulk-Logged, nuna lo había escuchado. Si pudieras darme una pequeña explicación.

Muchas Gracias por la ayuda!

[Mithrandir]

No lo he usado nunca, porque para mis necesidades es un punto de inconsistencia. Te cito lo que viene en los manuales de SQL (que por lo que leo no te servirá):

Bulk-Logged Recovery
The Bulk-Logged Recovery model provides protection against media failure combined with the best performance and minimal log space usage for certain large-scale or bulk copy operations. These operations are minimally logged:

SELECT INTO.


Bulk load operations (bcp and BULK INSERT).


CREATE INDEX (including indexed views).


text and image operations (WRITETEXT and UPDATETEXT).
In a Bulk-Logged Recovery model, the data loss exposure for these bulk copy operations is greater than in the Full Recovery model. While the bulk copy operations are fully logged under the Full Recovery model, they are minimally logged and cannot be controlled on an operation-by-operation basis under the Bulk-Logged Recovery model. Under the Bulk-Logged Recovery model, a damaged data file can result in having to redo work manually.

In addition, the Bulk-Logged Recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes. Point-in-time recovery is not supported.

In Microsoft® SQL Server™ 2000, you can switch between full and bulk-logged recovery models easily. It is not necessary to perform a full database backup after bulk copy operations complete under the Bulk-Logged Recovery model. Transaction log backups under this model capture both the log and the results of any bulk operations performed since the last backup.

The backup strategy for bulk-logged recovery consists of:

Database backups.


Differential backups (optional).


Log backups.
Backing up a log that contains bulk-logged operations requires access to all data files in the database. If the data files are not accessible, the final transaction log cannot be backed up and all committed operations in that log will be lost.

To recover in the event of media failure

Back up the currently active transaction log. For more information, see Transaction Log Backups.


Restore the most recent full database backup.


If differential backups exist, restore the most recent one.


Apply in sequence all transaction log backups created since the most recent differential or full database backup.


Manually redo all changes since the most recent log backup.


Important If the active transaction log is lost (for example, due to hardware failure on the disk containing the transaction log files), all transactions in that log are lost. To prevent loss of the active transaction log, place the transaction log files on mirrored disks.