studio - El registro de SQL Server 2008 no se truncará

sqlmanagementstudio_x64_esn

Question

Me considero una persona SQL muy experimentada. Pero no estoy haciendo estas dos cosas:

• Reducir el tamaño del registro asignado.

• Truncar el registro.

  DBCC sqlperf (espacio de registro)

devoluciones:

Database Name   Log Size (MB)   Log Space Used (%)  Status
ByBox       1964.25     30.0657         0

Lo siguiente no funciona con SQL 2008

DUMP TRANSACTION ByBox WITH TRUNCATE_ONLY

Ejecutar lo siguiente tampoco hace nada

DBCC SHRINKFILE (''ByBox_1_Log'' , 1)
DBCC shrinkdatabase(N''bybox'')

He intentado copias de seguridad. También he intentado configurar las propiedades de la base de datos: ''Recuperar modelo'' en ''COMPLETO'' y ''SIMPLE'' y una combinación de todo lo anterior. También intenté establecer la compatibilidad con SQL Server 2005 (uso esta configuración porque quiero coincidir con nuestro servidor de producción) y SQL Server 2008.

No importa lo que intente, el registro permanece en 1964.25 MB, con un 30% utilizado, que sigue creciendo.

Me gustaría que el registro disminuya cerca del 0% y reduzca el tamaño del archivo de registro a, digamos, 100 MB, que es suficiente. Mi base de datos debe odiarme; simplemente ignora todo lo que le pido que haga con respecto al registro.

Una nota más. La base de datos de producción tiene bastantes tablas replicadas, que desactivo cuando realizo una restauración en mi cuadro de desarrollo utilizando lo siguiente:

-- Clear out pending replication stuff
exec sp_removedbreplication
go
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL,
     @numtrans = 0, @time = 0, @reset = 1
go

Molesto:

SELECT log_reuse_wait, log_reuse_wait_desc
FROM sys.databases
WHERE NAME=''bybox''

Devoluciones

log_reuse_wait  log_reuse_wait_desc
0   NOTHING

¿Como puedo solucionar este problema?

Viendo this y configurando el modelo de recuperación en COMPLETO, he intentado lo siguiente:

USE master
GO

EXEC sp_addumpdevice ''disk'', ''ByBoxData'', N''C:/<path here>/bybox.bak''

-- Create a logical backup device, ByBoxLog.
EXEC sp_addumpdevice ''disk'', ''ByBoxLog'', N''C:/<path here>/bybox_log.bak''

-- Back up the full bybox database.
BACKUP DATABASE bybox TO ByBoxData

-- Back up the bybox log.
BACKUP LOG bybox TO ByBoxLog

el cual regresó:

Processed 151800 pages for database ''bybox'', file ''ByBox_Data'' on file 3.
Processed 12256 pages for database ''bybox'', file ''ByBox_Secondary'' on file 3.
Processed 1 pages for database ''bybox'', file ''ByBox_1_Log'' on file 3.
BACKUP DATABASE successfully processed 164057 pages in 35.456 seconds (36.148 MB/sec).

Processed 2 pages for database ''bybox'', file ''ByBox_1_Log'' on file 4.
BACKUP LOG successfully processed 2 pages in 0.056 seconds (0.252 MB/sec).

¡Perfecto! Pero no lo es.

Y DBCC SHRINKFILE (''ByBox_1_Log'', 1) ahora regresa con

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
7   2   251425  251425  251424  251424

y DBCC SQLPERF (LOGSPACE) aún reporta un 30% de uso.

Creo que puedo tener que resignarme al hecho de que podría haber un error en SQL Server 2008, o que mi archivo de registro se ha corrompido de alguna manera. Sin embargo, mi base de datos está en buen estado de funcionamiento, lo que me lleva a pensar que hay un error (se estremece al pensarlo) .

Answer 1

¡Cuidado con las implicaciones de cambiar los modelos de recuperación!

Y ahora, un pensamiento más serio para todos los DBAs producción que piensan usar el script:

ANTES DE CAMBIAR EL MODELO DE RECUPERACIÓN DE COMPLETO A SENCILLO ... no se preocupe si se encuentra en un entorno de desarrollo / QA . Pero si se encuentra en un entorno de producción en el que es responsable de garantizar la recuperación completa de los datos en caso de que se produzca un problema, es posible que desee analizar más detenidamente lo que dice BOL sobre esto (consulte BOL en: "Administración de bases de datos "->" Gestión de registro de transacciones "->" Gestión de modelos de recuperación y registro de transacciones "):

Una base de datos se puede cambiar a otro modelo de recuperación en cualquier momento. Sin embargo, cambiar del modelo de recuperación simple, es inusual. Tenga en cuenta que si cambia al modelo de recuperación completa durante una operación masiva, el registro de la operación masiva cambia de registro mínimo a registro completo, y viceversa.

Después de cambiar del modelo de recuperación simple

Si cambia del modelo de recuperación completo o de registro masivo al modelo de recuperación simple, rompe la cadena de registro de respaldo. Por lo tanto, le recomendamos encarecidamente que realice una copia de seguridad del registro inmediatamente antes de cambiar, lo que le permite recuperar la base de datos hasta ese momento. Después de cambiar, debe realizar copias de seguridad periódicas de los datos para proteger sus datos y truncar la parte inactiva del registro de transacciones.

Después de cambiar al modelo de recuperación simple

Si cambia del modelo de recuperación completo o de registro masivo al modelo de recuperación simple, rompe la cadena de registro de respaldo. Por lo tanto, le recomendamos encarecidamente que realice una copia de seguridad del registro inmediatamente antes de cambiar, lo que le permite recuperar la base de datos hasta ese momento. Después de cambiar, debe realizar copias de seguridad periódicas de los datos para proteger sus datos y truncar la parte inactiva del registro de transacciones.

De Verdad? " Le recomendamos encarecidamente que realice una copia de seguridad del registro inmediatamente antes del cambio, lo que le permite recuperar la base de datos hasta ese momento " . No puedo entender por qué este pequeño fragmento de información está oculto en una sección llamada "Después de cambiar al modelo de recuperación simple "haciendo que la mayoría de las" personas normales "piensen que pueden seguir adelante y cambiarlo y luego continuar o regresar y leer esto después de cambiarlo.

Despotricar

Para Microsoft: Por lo tanto, corríjame si me equivoco, si no hago la copia de seguridad de T-log ANTES de cambiar de COMPLETO a SIMPLE y he aquí que mi base de datos se corrompe de alguna manera (¿ha oído hablar de la ley de Murphy ?) Justo antes de que yo Soy capaz de tomar una copia de seguridad ... entonces estoy jodido, ¿verdad? Si cambiar el modelo de recuperación de mi base de datos **** de producción **** de COMPLETO a SENCILLO es algo que puede romper la cadena de registro de respaldo, por lo que si no puedo realizar un respaldo de registro de transacciones antes de hacerlo (como se sugiere arriba) Potencialmente voy a perder datos , entonces, ¿POR QUÉ EL RECONOCIMIENTO NO ESTÁ DESTACANDO QUE EN UN MARQUEE PARPADEANTE HACIENDO UNA OFERTA MÁS GRANDE de lo que parece ser? Literalmente, deberías agarrarme por la camisa y darme palmaditas para llamar mi atención (por así decirlo) y advertirme de la importancia de este ¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡DE VENTAS !!! »con la camiseta de YA!

Answer 2

En mi situación, tenía una base de datos de 650 MB con un archivo de registro de 370 GB en SQL Server 2008. No importa lo que haya intentado, no pude hacer que se redujera. Intenté todo lo que figura en la lista de respuestas, pero aún así, nada funcionó

Finalmente, encontré un comentario muy corto en otro lugar que sí funcionó. Es para ejecutar esto:

BACKUP LOG DatabaseName TO DISK = N''D:/Backup/DatabaseName_log.bak''
GO
DBCC SHRINKFILE(''MyDatabase_Log'', 1)
GO

Esto hizo que el archivo de registro se redujera de 37 GB a 1 MB. ¡Uf!

Answer 3

Encontré DBCC SHRINKFILE (Transact-SQL) (MSDN).

El siguiente ejemplo reduce el archivo de registro en la base de datos AdventureWorks a 1 MB. Para permitir que el comando DBCC SHRINKFILE reduzca el tamaño del archivo, primero se trunca el archivo configurando el modelo de recuperación de la base de datos en SIMPLE.

USE AdventureWorks;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO

Answer 4

Esto puede ser un dolor, y hay muchas cosas que podría ser. Lo primero que debe asegurarse es que no haya una transacción "atascada". Si tiene una transacción que nunca se cierra, nunca puede reducir el registro. Ejecute "DBCC OPENTRAN" para encontrar la transacción de mayor duración.

Además, asegúrese de reorganizar (creo que es el término adecuado) y mueva todo al principio del archivo antes de reducirlo.

Answer 5

Esto suena un poco estúpido, pero me parece que tengo que realizar dos copias de seguridad completas de la base de datos y del archivo de registro para poder reducir la base de datos.

Cuando se usa SQL Server Management Studio , después de hacer una copia de seguridad completa de la base de datos seguida de una copia de seguridad completa del registro de transacciones, la página de archivos de reducción muestra un montón de espacio libre disponible, pero no truncará los archivos de registro.

Sin embargo, cuando ejecuto una segunda copia de seguridad completa tanto de la base de datos como del archivo de registro, encuentro que la copia de seguridad completa del registro de transacciones es mucho más pequeña, ya que parece que solo hace una copia de seguridad de los nuevos cambios desde la última copia de seguridad.

Una vez que se completa esta segunda copia de seguridad, vuelvo a ejecutar la herramienta de reducción dentro del estudio de administración. Todavía muestra que hay un montón de espacio libre disponible, pero esta vez cuando hago clic en Aceptar, el archivo de registro se reduce de tamaño.

Por lo tanto, intente lo siguiente.

1. Realice una copia de seguridad completa tanto de la base de datos como del archivo de registro. Si verifica dentro de la herramienta de reducción, debería ver que ahora hay un montón de espacio libre disponible en el archivo de registro. Sin embargo, hacer clic en Aceptar no eliminará el espacio libre.

2. Realice una segunda copia de seguridad completa de la base de datos y del archivo de registro. Debe encontrar que la copia de seguridad completa de la base de datos es similar en tamaño a la primera copia de seguridad completa. El tamaño de la copia de seguridad completa del registro de transacciones debe ser mucho menor.

3. Ejecute la herramienta de reducción en el archivo de registro y, con suerte, el archivo de registro debería reducir su tamaño. La última vez que hice esto, se redujo de 180 GB a 12 MB y la herramienta de reducción indica que todavía hay 10 MB de espacio libre disponible dentro del archivo.

Answer 6

Finalmente encontré una solución para el problema de reducción del archivo de registro. Todas las opciones anteriores no funcionaron para mí y no redujeron el archivo de registro al tamaño requerido. La solución que encontré es:

• Copia de seguridad de su base de datos
• Establecer el modo de recuperación a simple
• Separar la base de datos con SQL Server Management Studio
• Eliminar el archivo de registro
• Adjunte la base de datos sin el archivo de registro. En la mitad inferior de la "pantalla de agregar" aparece una línea que dice que falta el archivo de registro
• Haga clic en Eliminar para esta línea y en Aceptar para adjuntar.
• Establecer el modo de recuperación de nuevo a completo

Answer 7

Finalmente he llegado a la conclusión de que hay un error en SQLServer 2008.

Lo he intentado todo, y cada combinación que se me ocurre. He hecho una copia de seguridad de la base de datos, la he eliminado, la he vuelto a crear, la he restaurado. Exacto mismo problema.

Yo también corrí:

DBCC CHECKDB
DBCC UPDATEUSAGE (bybox)

Y todo sale bien.

Rollo en el próximo paquete de servicio es todo lo que puedo decir.

Answer 8

Intenta correr

DBCC OPENTRAN

Para comprobar si hay transacciones abiertas.

Answer 9

Leyendo las respuestas, casi no creo que estén escritas por DBA. Reglas básicas de oro:

1. En cualquier base de datos antes de realizar cualquier mantenimiento, incluida la reducción del registro, debe realizar una copia de seguridad completa.

2. Lleve a cabo cualquier mantenimiento de la base de datos, incluida la reducción, cuando no ocurra nada más en esa base de datos en particular durante toda la duración del mantenimiento. Si es necesario, suspender las aplicaciones no esenciales. Siempre tenga en cuenta que la base de datos saludable es el alma de cualquier aplicación que interactúe con ella.

Después de todo esto, los siguientes comandos para reducir el registro de transacciones de la base de datos siempre funcionaron bien conmigo en SQL Server 2005 y versiones posteriores de SQL Server:

USE DatabaseName
GO
-- Truncate the Transaction log
ALTER DATABASE DatabaseName SET RECOVERY SIMPLE
CHECKPOINT
ALTER DATABASE DatabaseName SET RECOVERY FULL
GO
-- Shrink the Transaction Log as recommended my Microsoft.

DBCC SHRINKFILE (''database_txlogfilelogicalname'', [n -size to shrink in MBytes])
GO
 -- Pass the freed pages back to OS control.
DBCC SHRINKDATABASE (DatabaseName, TRUNCATEONLY)
GO
-- Tidy up the pages after shrink
DBCC UPDATEUSAGE (0);
GO
-- IF Required but not essential
-- Force to update all tables statistics
exec sp_updatestats
GO

Answer 10

Otra forma fácil de reducir el tamaño del archivo de registro es:

• Registros de copia de seguridad
• Copia de seguridad completa de la base de datos
• Reducir el archivo de registros
• Copia de seguridad de registros de nuevo
• Reducir el archivo de registros de nuevo

De esta manera, no tiene que modificar ningún parámetro de la base de datos y su archivo de registro tiene un tamaño de 1 MB.

Answer 11

Por favor, corre:

SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases

y vea cuál es el log_reuse_wait para su db problema, si la replicación log_reuse_wait que esto es un error, necesita este valor 0, 2 o 4

Answer 12

Sé lo que quieres decir: el servidor SQL puede ser un poco enloquecedor de esa manera. Aquí hay un código de ejemplo para probar. En esencia, trunca el archivo de registro y luego intenta reducir el tamaño del archivo. Déjame saber cómo funciona Otra cosa ... no tendrías transacciones no comprometidas, ¿verdad?

Use YourDatabase
GO

DBCC sqlperf(logspace)  -- Get a "before" snapshot
GO  

BACKUP LOG BSDIV12Update WITH TRUNCATE_ONLY;  -- Truncate the log file, don''t keep a backup
GO

DBCC SHRINKFILE(YourDataBaseFileName_log, 2);  -- Now re-shrink (use the LOG file name as found in Properties / Files.  Note that I didn''t quote mine).
GO

DBCC sqlperf(logspace)  -- Get an "after" snapshot
GO

Actualización: Simon nota que está recibiendo un error en el comando BACKUP. No me di cuenta de que "Truncate_only" se había interrumpido en SQL Server 2008 cuando respondí antes. Después de un poco de investigación, los pasos recomendados para reducir el archivo de registro son (a) Cambiar el Modelo de recuperación a Simple y luego (b) reducir el archivo usando DBCC ShrinkFile como se indicó anteriormente. Desafortunadamente, mencionó que ya intentó configurar el modelo de recuperación a Simple, así que asumo que también ejecutó el DBCC Shrinkfile después. ¿Es esto correcto? Por favor hagamelo saber.

Answer 13

Siempre he odiado la forma en que SQL Server maneja la reducción física de los archivos de registro. Tenga en cuenta que siempre he hecho esto a través de Enterprise Manager / SQL Server Management Studio, pero parece que cuando reduce / trunca el archivo de registro, el tamaño físico del archivo de registro no se reducirá hasta después de hacer una copia de seguridad completa en la base de datos archivo de datos, y luego una copia de seguridad del archivo de registro de nuevo. Nunca podría precisar el patrón exacto, pero podrías intentar ver cuál es la secuencia exacta. Sin embargo, siempre ha implicado hacer una copia de seguridad completa del archivo de datos.

Answer 14

SQL Server 2012 : tuve un problema en el que ningún archivo de registro (y todos ya estaban en recuperación SIMPLE) se reduciría.

Esto me funcionó ... Reinicié la instancia de SQL Server (porque podía) y todos esos chicos malos se encogieron.

Lo que fuera que lo retenía del encogimiento fue liberado con el reinicio. Esto solo es bueno para emergencias (o cuando se trata de su servidor), no es una solución regular a largo plazo.

Answer 15

Encontré la solución!

Agregué una carga de datos a la base de datos, por lo que el registro se vio obligado a expandirse. Luego eliminé los datos que no se habían cargado para que mi base de datos volviera a ser como era.

Copia de seguridad y voila, un perfecto 0% de registro.

Así que la solución es hacer que el registro se expanda.