¿Cómo eliminar datos grandes de la tabla en SQL sin registro?

Tengo una gran tabla de datos. Hay 10 millones de registros en esta tabla.

Cuál es la mejor manera para esta consulta

Delete LargeTable where readTime < dateadd(MONTH,-7,GETDATE()) 

  1. Si está borrando todas las filas en esa tabla, la opción más simple es Truncar tabla, algo así como

     TRUNCATE TABLE LargeTable GO 

    La tabla truncada simplemente vaciará la tabla, no puede usar la cláusula WHERE para limitar las filas que se eliminarán y no se dispararán los activadores.

  2. Por otro lado, si está eliminando más del 80-90 por ciento de los datos, decir si tiene un total de 11 millones de filas y desea eliminar 10 millones de otra manera sería Insertar estas 1 millón de filas (registros que desea conservar) ) a otra mesa de ensayo. Trunque esta tabla grande e inserte estas 1 millón de filas.

  3. O si los permisos / vistas u otros objetos que tienen esta tabla grande como su tabla subyacente no se ven afectados al eliminar esta tabla, puede obtener esta cantidad relativamente pequeña de las filas en otra tabla soltar esta tabla y crear otra tabla con el mismo esquema e importar estos vuelve a entrar en esta tabla ex-Large.

  4. Una última opción que puedo pensar es cambiar el Recovery Mode to SIMPLE tu base de datos Recovery Mode to SIMPLE y luego eliminar las filas en lotes más pequeños usando un ciclo while algo así …

     DECLARE @Deleted_Rows INT; SET @Deleted_Rows = 1; WHILE (@Deleted_Rows > 0) BEGIN -- Delete some small number of rows at a time DELETE TOP (10000) LargeTable WHERE readTime < dateadd(MONTH,-7,GETDATE()) SET @Deleted_Rows = @@ROWCOUNT; END 

y no te olvides de volver a cambiar el modo de recuperación y creo que debes hacer una copia de seguridad para que sea completamente efectivo (los modos de cambio o recuperación).

La respuesta de @m-ali es correcta, pero también tenga en cuenta que los registros podrían crecer mucho si no se compromete la transacción después de cada fragmento y se realiza un punto de control. Así es como lo haría y tome este artículo http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes como referencia, con pruebas de rendimiento y gráficos:

 DECLARE @Deleted_Rows INT; SET @Deleted_Rows = 1; WHILE (@Deleted_Rows > 0) BEGIN BEGIN TRANSACTION -- Delete some small number of rows at a time DELETE TOP (10000) LargeTable WHERE readTime < dateadd(MONTH,-7,GETDATE()) SET @Deleted_Rows = @@ROWCOUNT; COMMIT TRANSACTION CHECKPOINT -- for simple recovery model END 

También puede usar GO + cuantas veces desea ejecutar la misma consulta.

 DELETE TOP (10000) [TARGETDATABASE].[SCHEMA].[TARGETTABLE] WHERE readTime < dateadd(MONTH,-1,GETDATE()); -- how many times you want the query to repeat GO 100 

Esta variación de M.Ali está funcionando bien para mí. Elimina algunos, borra el registro y se repite. Estoy viendo el registro crecer, caer y comenzar de nuevo.

 DECLARE @Deleted_Rows INT; SET @Deleted_Rows = 1; WHILE (@Deleted_Rows > 0) BEGIN -- Delete some small number of rows at a time delete top (100000) from InstallLog where DateTime between '2014-12-01' and '2015-02-01' SET @Deleted_Rows = @@ROWCOUNT; dbcc shrinkfile (MobiControlDB_log,0,truncateonly); END 

@Francisco Goldenstein, solo una pequeña corrección. El COMMIT debe utilizarse después de establecer la variable; de ​​lo contrario, WHILE se ejecutará una sola vez:

 DECLARE @Deleted_Rows INT; SET @Deleted_Rows = 1; WHILE (@Deleted_Rows > 0) BEGIN BEGIN TRANSACTION -- Delete some small number of rows at a time DELETE TOP (10000) LargeTable WHERE readTime < dateadd(MONTH,-7,GETDATE()) SET @Deleted_Rows = @@ROWCOUNT; COMMIT TRANSACTION CHECKPOINT -- for simple recovery model END 

Si está dispuesto (y puede) implementar particiones, esa es una técnica efectiva para eliminar grandes cantidades de datos con poca sobrecarga de tiempo de ejecución. Sin embargo, no es rentable para un ejercicio de una sola vez.

Pude borrar 19 millones de filas de mi tabla de 21 millones de filas en cuestión de minutos . Aquí está mi enfoque.

Si tiene una clave principal de incremento automático en esta tabla, puede utilizar esta clave principal.

  1. Obtenga el valor mínimo de la clave principal de la tabla grande donde readTime

  2. Inserte todas las filas que tienen la clave principal> min_primary en una tabla de etapas (tabla de memoria si no hay filas grandes).

  3. Suelta la gran mesa.

  4. Recrea la mesa. Copie todas las filas de la tabla de etapas a la tabla principal.

  5. Suelta la tabla de etapas.

Puede eliminar lotes pequeños usando un ciclo while, algo como esto:

 DELETE TOP (10000) LargeTable WHERE readTime < dateadd(MONTH,-7,GETDATE()) WHILE @@ROWCOUNT > 0 BEGIN DELETE TOP (10000) LargeTable WHERE readTime < dateadd(MONTH,-7,GETDATE()) END 

Otro uso:

 SET ROWCOUNT 1000 -- Buffer DECLARE @DATE AS DATETIME = dateadd(MONTH,-7,GETDATE()) DELETE LargeTable WHERE readTime < @DATE WHILE @@ROWCOUNT > 0 BEGIN DELETE LargeTable WHERE readTime < @DATE END SET ROWCOUNT 0 

Opcional;

Si el registro de transacciones está habilitado, deshabilite los registros de transacciones.

 ALTER DATABASE dbname SET RECOVERY SIMPLE; 

Sintaxis más corta

 select 1 WHILE (@@ROWCOUNT > 0) BEGIN DELETE TOP (10000) LargeTable WHERE readTime < dateadd(MONTH,-7,GETDATE()) END