sql server - transacciones - DBCC SHRINKFILE en el archivo de registro que no reduce el tamaño incluso después de BACKUP LOG TO DISK

truncate log sql server 2012

Question

Tengo una base de datos, [Mi DB], que tiene la siguiente información:
SQL Server 2008
Tamaño de MDF: 30 GB
Tamaño de LDF: 67 GB

Quería reducir el archivo de registro tanto como fuera posible, así que comencé mi búsqueda para descubrir cómo hacerlo. Advertencia: no soy un DBA (o incluso me estoy acercando a un DBA) y he progresado con la sensación a través de esta búsqueda.

Primero, ingresé a SSMS, propiedades de DB, Archivos y edité el valor de Tamaño inicial (MB) a 10. Eso redujo el archivo de registro a 62 GB (no exactamente los 10 MB que ingresé). Entonces, adjunté SQL Profiler, vi que se llamaba a DBCC SHRINKFILE. Luego ingresé ese comando en el editor de consultas y aquí están los resultados.

DBCC SHRINKFILE (N''My DB_Log'' , 10)

Y la salida fue:

Cannot shrink log file 2 (My DB_Log) because the logical log file located at the end of the file is in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
8 2 8044104 12800 8044104 12800
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Luego hice una investigación sobre eso y encontré esto:

http://support.microsoft.com/kb/907511

Lo que dice que necesito hacer una copia de seguridad del archivo de registro antes del archivo shrink para que se liberen los archivos de registro virtual y el archivo shrinkfile haga su trabajo. No sé lo que eso significa ... Solo estoy parafraseando aquí :)

Entonces, pensé que trataría de hacer una copia de seguridad del archivo de registro y luego hacer un DBCC SHRINKFILE (y cambié el nuevo tamaño de archivo de registro a 12800 ya que era el MinimumSize identificado en el resultado del comando anterior DBCC SHRINKFILE)

BACKUP LOG [My DB] TO DISK = ''D:/SQLBackup/20110824-MyDB-Log.bak''
GO
DBCC SHRINKFILE (N''My DB_Log'' , 12800)
GO

El resultado fue el mismo que la primera vez. Solo puedo bajar el archivo de registro a 62 GB.

No estoy seguro de qué estoy haciendo mal y qué debo intentar a continuación.

Answer 1

Además de los pasos que ya ha realizado, deberá establecer el modo de recuperación en simple antes de poder reducir el registro.

ESTA NO ES UNA PRÁCTICA RECOMENDADA para los sistemas de producción ... Perderá su capacidad de recuperación a un punto en el tiempo de las copias de seguridad / archivos de registro previos.

Vea el ejemplo B en esta página msdn de DBCC SHRINKFILE (Transact-SQL) para ver un ejemplo y una explicación.

Answer 2

Paul Randal tiene una excelente discusión sobre este problema en su blog: http://www.sqlskills.com/blogs/paul/post/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to -stop-it.aspx

Answer 3

Gracias a @ user2630576 y @ Ed.S.

Lo siguiente funcionó:

BACKUP LOG [database] TO DISK = ''D:/database.bak''
GO
ALTER DATABASE [database] SET RECOVERY SIMPLE
use [database]
declare @log_File_Name varchar(200)
select @log_File_Name = name from sysfiles where filename like ''%LDF''
declare @i int = FILE_IDEX ( @log_File_Name)
dbcc shrinkfile ( @i , 50)
ALTER DATABASE [database] SET RECOVERY FULL

Answer 4

De acuerdo, aquí hay una solución para reducir el tamaño físico del archivo de transacción, pero sin cambiar el modo de recuperación a simple.

Dentro de su base de datos, ubique el archivo_id del archivo de registro usando la siguiente consulta.

SELECT * FROM sys.database_files;

En mi caso, el archivo de registro es file_id 2. Ahora queremos ubicar los registros virtuales en uso y hacer esto con el siguiente comando.

DBCC LOGINFO;

Aquí puede ver si hay registros virtuales en uso al ver si el estado es 2 (en uso) o 0 (gratis). Cuando se reducen los archivos, los registros virtuales vacíos se eliminan físicamente empezando por el final del archivo hasta que alcanza el primer estado utilizado. Esta es la razón por la que la reducción de un archivo de registro de transacciones a veces lo reduce en parte, pero elimina todos los registros virtuales gratuitos que pueda esperar.

Si observa un estado 2 que ocurre después de 0, esto está bloqueando el encogimiento para que no encoja completamente el archivo. Para evitar esto, realice otra copia de seguridad del registro de transacciones e inmediatamente ejecute estos comandos, proporcionando el archivo_id que se encuentra arriba y el tamaño en el que desea que se reduzca su archivo de registro.

DBCC SHRINKFILE (file_id, LogSize_MB)

DBCC SHRINKFILE (2, 100);
DBCC LOGINFO;

Esto mostrará la asignación del archivo de registro virtual y, con suerte, notará que se ha reducido un poco. Debido a que los archivos de registro virtual no siempre se asignan en orden, es posible que tenga que hacer una copia de seguridad del registro de transacciones un par de veces y ejecutar esta última consulta nuevamente; pero normalmente puedo reducirlo dentro de una copia de seguridad o dos.

Artículo completo: http://radderz.me.uk/2014/02/physically-shrinking-sql-transaction-log-files/

Answer 5

Prueba esto

ALTER DATABASE XXXX SET RECOVERY SIMPLE
use XXXX
declare @log_File_Name varchar(200)
select @log_File_Name = name from sysfiles where filename like ''%LDF''
declare @i int = FILE_IDEX ( @log_File_Name)
dbcc shrinkfile ( @i , 50) 

Answer 6

Yo uso este script en sql server 2008 R2.

USE [db_name]
ALTER DATABASE [db_name] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([log_file_name]/log_file_number, wanted_size)
ALTER DATABASE [db_name] SET RECOVERY FULL WITH NO_WAIT

Answer 7

Reducir un archivo de registro

Para archivos de registro, Database Engine usa target_size para calcular el tamaño de destino para todo el registro; por lo tanto, target_size es la cantidad de espacio libre en el registro después de la operación de reducción. El tamaño de destino para todo el registro se traduce al tamaño objetivo para cada archivo de registro. DBCC SHRINKFILE intenta reducir cada archivo de registro físico a su tamaño objetivo de inmediato.

Sin embargo, si parte del registro lógico reside en los registros virtuales más allá del tamaño objetivo, el motor de base de datos libera tanto espacio como sea posible y luego emite un mensaje informativo.

El mensaje describe qué acciones son necesarias para mover el registro lógico fuera de los registros virtuales al final del archivo. Después de realizar las acciones, DBCC SHRINKFILE se puede utilizar para liberar el espacio restante.

Como un archivo de registro solo puede reducirse a un límite de archivo de registro virtual, es posible que no se pueda reducir el tamaño de un archivo de registro a un tamaño menor que el de un archivo de registro virtual, incluso si no se está utilizando . El tamaño del archivo de registro virtual se elige dinámicamente por el Motor de base de datos cuando se crean o amplían los archivos de registro.

• Solución de problemas: el archivo no se contrae

Si la operación de reducción se ejecuta sin error, pero el archivo no parece haber cambiado de tamaño, verifique que el archivo tenga suficiente espacio libre para eliminar realizando una de las siguientes operaciones:

Ejecute la siguiente consulta.

SELECCIONAR nombre, tamaño / 128.0 - CAST (FILEPROPERTY (nombre, ''SpaceUsed'') AS int) /128.0 AS AvailableSpaceInMB FROM sys.database_files;

Ejecute el comando DBCC SQLPERF para devolver el espacio utilizado en el registro de transacciones.

• Si no hay suficiente espacio libre disponible, la operación de reducción no puede reducir el tamaño del archivo más.

• Normalmente, es el archivo de registro que parece no encogerse. Esto suele ser el resultado de un archivo de registro que no se ha truncado.

• Puede truncar el registro configurando el modelo de recuperación de base de datos en SIMPLE , o haciendo una copia de seguridad del registro y luego ejecutando de nuevo la operación DBCC SHRINKFILE .

Ejemplo:

Reducir un archivo de registro a un tamaño objetivo específico

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 al configurar el modelo de recuperación de la base de datos en SIMPLE.

Transact-SQL

UTILICE AdventureWorks2012;
IR
- Truncar el registro cambiando el modelo de recuperación de la base de datos a SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
IR
- Reducir el archivo de registro truncado a 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
IR
- Restablecer el modelo de recuperación de la base de datos.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
IR

Cuando utiliza DBCC SHRINKFILE (archivo de registro, tamaño) solo se trunca desde el final del archivo de registro hasta donde puede. Cuando alcanza el registro virtual más alto aún en uso, no puede reducirse aún más. Esto se describe en los Libros en pantalla de SQL Server en:

http://technet.microsoft.com/en-us/library/ms189493.aspx

Por lo tanto, una vez que el extremo superior del registro está despejado, puede reducirse en tamaño. De nuevo, eso dependerá de la cantidad de registro que todavía esté en uso. El registro se puede borrar mediante copias de seguridad, pero las copias de seguridad no borrarán las transacciones incompletas, por lo que el registro puede permanecer en un VLF de gama alta incluso después de repetidas copias de seguridad.

Con respecto al aumento y disminución de los VLF, ¿cuán grande fue originalmente el archivo de registro creado y cuál es la configuración para el crecimiento del archivo de registro? Si crece solo en una pequeña cantidad, creará más VLF de lo que cualquiera desea.

Un patrón común para reducir un archivo de registro es CHECKPOINT, BACKUP, SHRINKFILE, CHECKPOINT, BACKUP, SHRINKFILE, etc. hasta que obtenga resultados. Hay muchas razones por las que el registro puede no ser contraíble, incluida una reversión muy grande.

El cambio de simple a completo tiene un problema:

Aquí hay reglas y excepciones. Hablaremos sobre las transacciones de larga ejecución en profundidad a continuación.

Pero una advertencia a tener en cuenta para el modo de recuperación total es la siguiente: si simplemente cambia al modo de recuperación total, pero nunca realiza una copia de seguridad completa inicial, SQL Server no respetará su solicitud de estar en el modelo de recuperación total. Su registro de transacciones continuará funcionando como lo ha hecho en Simpleuntil al cambiar al Modelo de Recuperación Completa Y tomar su primera Copia de Seguridad Completa.

El modelo de recuperación completa sin copias de seguridad de registro es malo:

Entonces, ¿esa es la razón más común para el crecimiento incontrolado del registro? Respuesta: estar en modo de recuperación total sin tener ninguna copia de seguridad de registro.

Esto sucede todo el tiempo para las personas.

¿Por qué es este un error tan común?

¿Por qué sucede todo el tiempo? Debido a que cada nueva base de datos obtiene su configuración de modelo de recuperación inicial mirando la base de datos modelo.

La configuración del modelo de recuperación inicial del modelo es siempre el modelo de recuperación completa, hasta que alguien lo cambie. Entonces, podría decir que el "Modelo de recuperación predeterminado" está lleno. Muchas personas no son conscientes de esto y tienen sus bases de datos ejecutándose en Full Recovery Model sin copias de seguridad de registros, y por lo tanto un archivo de registro de transacciones mucho más grande de lo necesario. Es por eso que es importante cambiar los valores predeterminados cuando no funcionan para su organización y sus necesidades)

Answer 8

Intenté de muchas maneras, pero esto funciona.

El código de muestra está disponible en DBCC SHRINKFILE

USE DBName;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DBName
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DBName_log, 1); --File name SELECT * FROM sys.database_files; query to get the file name
GO
-- Reset the database recovery model.
ALTER DATABASE DBName
SET RECOVERY FULL;
GO