sql - todos - ¿Cómo puedo eliminar los datos caducados de una tabla enorme sin tener el archivo de registro fuera de control?

recuperar datos de una tabla sql server

Question

Tengo una gran tabla (3 mil millones de filas), que desafortunadamente contiene datos caducados. Quiero simplemente eliminar todas estas filas caducadas y guardar el resto.

Puedo ejecutar una declaración como esta:

delete from giganticTable where exp_date < getDate()

El plan de ejecución de alguna manera estima que se eliminarán alrededor de 400 millones de filas.

Cuando se ejecuta, no solo no termina después de una hora, sino que el archivo de registro de transacciones de la base de datos también está creciendo de 6 GB a 90 GB. Tenga en cuenta que la base de datos estaba en un modelo de recuperación de registro masivo mientras esto sucede. Eventualmente cancelé esta consulta, ya que estoy seguro de que debe haber una mejor manera de hacerlo.

Tengo varias tablas para las que necesito realizar una operación similar. ¿Cuál es la forma más rápida y eficiente de eliminar estas filas si no tengo ningún deseo de recuperarlas?

Tenga en cuenta que estoy usando Microsoft SQL Server 2005.

Answer 1

He encontrado que es útil hacer borrados de la tabla con un gran número de filas para eliminar filas en lotes de aproximadamente 5000 (generalmente pruebo para ver qué valor funciona más rápido, a veces son 5000 filas, a veces 10,000, etc.) . Esto permite que cada operación de eliminación se complete rápidamente, en lugar de esperar mucho tiempo para que una declaración elimine 400 millones de registros.

En SQL Server 2005, algo así debería funcionar (por favor, prueba primero, por supuesto):

WHILE EXISTS ( SELECT * FROM giganticTable WHERE exp_date < getDate())
BEGIN
  DELETE TOP(5000) FROM giganticTable WHERE exp_date < getDate()
END

Vería lo que hace eliminar en los lotes al tamaño del archivo de registro. Si todavía está explotando los registros, podría intentar cambiar el Modelo de recuperación a Simple , eliminar los registros y luego volver a Bulk Logged, pero solo si el sistema puede tolerar la pérdida de algunos datos recientes. Definitivamente haría una copia de seguridad completa antes de intentar ese procedimiento. Este hilo también sugiere que puede configurar un trabajo para hacer una copia de seguridad de los registros con truncar solo especificado, por lo que podría ser otra opción. Espero que tenga una instancia con la que pueda probar, pero comenzaría con las eliminaciones por lotes para ver cómo eso afecta el rendimiento y el tamaño del archivo de registro.

Answer 2

Realmente no quiere meterse con intentar nada tonto como desactivar el registro cuando desea trabajar mucho en una tabla, ya que cualquier problema durante la larga tarea podría conducir fácilmente a la corrupción de la base de datos y otros problemas. Sin embargo, hay una forma de resolver su problema.

Cree una tabla temporal que coincida con el esquema de su tabla real. Llénelo con los datos que desea MANTENER. Luego, trunque la tabla original (extremadamente rápido y fácil en los archivos de registro). Finalmente, mueva los datos de la tabla temporal a su tabla original (y ahora vacía).

Si usa claves principales que se incrementan automáticamente, tendrá que forzar el campo para tomar sus claves originales (para que no tenga problemas más adelante).

Answer 3

Deberías haberlo hecho a diario, por lo que no obtienes un trabajo tan grande a la vez.
Ya que estás en la situación, aquí están mis sugerencias:

1. Dividir el trabajo como dice rsbarro. Probablemente no necesite la instrucción while; puede hacerlo en varios días.

2. Escriba la fecha explícitamente como:

   delete from giganticTable where exp_date < ''2013-08-07''

3. No tengo una buena idea sobre el enorme registro, parece que no hay una forma realmente buena de hacerlo.