Tengo una base de datos, [Mi DB], que tiene la siguiente información:

SQL Server 2008

MDF tamaño: 30 GB

LDF tamaño: 67 GB

Quería para reducir el tamaño del archivo de registro tanto como sea posible y así empecé mi búsqueda para averiguar cómo hacerlo. Advertencia: yo no soy un DBA (o incluso acercarse a un DBA) y han ido avanzando a través de los sentimientos a través de esta búsqueda.

Primero, yo sólo fui en SSMS, DB propiedades, Archivos y editar el Tamaño Inicial (MB) el valor de 10. Que reduce el archivo de registro de 62 GB (no se exactamente el 10 MB que he entrado). Así que, os adjunto el Analizador de SQL, vio que DBCC SHRINKFILE estaba siendo llamado. Luego he entrado a ese comando en el editor de consultas y he aqui el resultado.

DBCC SHRINKFILE (N'My DB_Log' , 10)

Y el resultado 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.

Entonces hice algunas investigaciones en las que e encontrado esto:

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

Que dice que tengo una copia de seguridad del archivo de registro antes de la shrinkfile, de modo que los archivos de registro virtuales serán liberados y el shrinkfile puede hacer su trabajo – no sé lo que eso significa… solo estoy parafraseando aquí 🙂

Así que pensé que me gustaría probar una copia de seguridad del archivo de registro y, a continuación, hacer un DBCC SHRINKFILE (y he cambiado el archivo de registro nuevo tamaño de hasta 12800 ya que fue el MinimumSize identificados en la salida de la anterior comando 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. Que sólo se puede obtener el archivo de registro a 62 GB.

No estoy seguro de lo que estoy haciendo mal y lo que debe tratar la siguiente.

  • para evitar que esto vuelva a ocurrir, usted debe ejecutar copias de seguridad del registro o establecer el modo de recuperación simple.
  • Mi problema era una PAUSA de REPLICACIÓN / creación de REFLEJO de la instalación-parece sql no se encoge tlogs ajeno si piensa que necesita para la replicación. Probablemente esto no será muy muchas personas del problema, pero podría ayudar a algunos.
InformationsquelleAutor Ed Sinek | 2011-08-25

8 Comentarios

  1. 39

    Además de los pasos que ya se han tomado, usted tendrá que establecer el modo de recuperación simple antes de que usted puede reducir el registro.

    ESTA NO ES UNA PRÁCTICA RECOMENDADA para los sistemas de producción… va a perder su capacidad para recuperar a un punto en el tiempo desde el anterior copias de seguridad de archivos de registro.

    Ver ejemplo B en este DBCC SHRINKFILE (Transact-SQL) página de msdn para obtener un ejemplo, y la explicación.

    • Sólo por curiosidad, ¿hay una manera de ver lo que el modo de recuperación es ir a través de la SSMS interfaz de usuario? Miré a través de DB Propiedades, pero no se la ve. El más cercano era el de Propiedades/Opciones de la página que muestra la Recuperación/PageVerify valor.
    • Encontró que no se encuentra en la lista de «Otras opciones». Es en la parte superior de la página – una de las tres listas desplegables. Thx.
    • Yo iba a sugerir que ejecuta esta consulta: select recovery_model_desc, de sys.bases de datos where name = ‘nombre de base de datos’
    • Gracias. Cambiar el modelo de recuperación fue la clave 🙂
  2. 121

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

    Dentro de su base de datos, busque la file_id del archivo de registro con la siguiente consulta.

    SELECT * FROM sys.database_files;

    En mi caso, el archivo de registro se file_id 2. Ahora queremos localizar los registros virtuales en uso, y hacerlo con el siguiente comando.

    DBCC LOGINFO;

    Aquí usted puede ver si los registros virtuales están en uso por ver si el estado es 2 (en uso), o 0 (gratis). Cuando la reducción archivos, vaciar registros virtuales están físicamente alejados de partida en el final del archivo hasta que llegue el primer estado usado. Esta es la razón por la reducción del tamaño del archivo de registro de transacciones a veces se encoge es parte del camino, pero no quita todo gratis registros virtuales.

    Si usted nota un estado de la 2 que se producen después de 0, este es el bloqueo de la reducción de la plenamente la reducción del archivo. Para conseguir alrededor de esto, hacer otra copia de seguridad del registro de transacciones, y de inmediato a ejecutar estos comandos, el suministro de los file_id se encuentra por encima, y el tamaño que usted quiere que su archivo de registro para ser reducido.

    -- DBCC SHRINKFILE (file_id, LogSize_MB)
    DBCC SHRINKFILE (2, 100);
    DBCC LOGINFO;

    Esto se le mostrará el archivo de registro virtual de la asignación, y espero que te darás cuenta de que se ha reducido un poco. Debido a que los archivos de registro virtuales no siempre se asignan en orden, usted puede tener una copia de seguridad del registro de transacciones de un par de veces y ejecutar esta última consulta de nuevo; pero que normalmente se puede reducir hacia abajo dentro de una copia de seguridad o dos.

    • Trabajó como un encanto. Copia de seguridad de 99% libre de registro sólo tomó un segundo, y el archivo de inmediato fue de más de 22.000 MB a 200 MB. Este debe marcar la respuesta correcta.
    • Agradezco la información. Todavía estoy atascado, sin embargo, porque creo que su solución se reduce a la solución de que el cartel original intentado y no podía ir a trabajar. Es la solución básicamente para ejecutar el «REGISTRO de COPIA de seguridad… IR DBCC SHRINKFILE…» varias veces hasta que funcione? He intentado varias veces sin éxito, y parece que el OP lo intentó sin éxito. Sólo quería aclarar, a ver si me falta algo en su respuesta.
    • ACTUALIZACIÓN: mi problema era que una pausa de replicación/reflejo de la instalación de cierre de la tlog. Probablemente un caso extremo.
    • sí si desea comprimir el archivo en el menor tamaño posible, usted tiene que hacer una copia de seguridad, retráctil, copia de seguridad y se reducen, en un solo paso. La razón (creo que es la intención), es que la reducción sólo se reduce el tamaño del archivo de registro para el tamaño de la ‘utiliza las páginas desde la última copia de seguridad; probablemente para minimizar la necesidad de ampliar el archivo de registro, útil para el entorno de producción donde los habituales de la carga y de las copias de seguridad se producen. El de arriba intenta demostrar y depuración de esto. Si usted tiene ‘0 en el archivo de registro, puede ser reducido aún más. Si no, entonces puede haber problemas con la publicación de páginas de la copia de seguridad de los registros.
  3. 12

    Puedo utilizar esta secuencia de comandos 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
    • Y, si usted hace eso, usted ha ROTO su cadena de copia de seguridad de registro de transacciones – de tal forma que si hay un desastre después de restablecer la PLENA recuperación de… tendrás el 100% de perder datos a MENOS que se saque de un COMPLETO o DIFF de copia de seguridad después de restablecer la RECUPERACIÓN COMPLETA.También, CON NO_WAIT significa dar transacciones en curso NO hay tiempo para completar. Una mejor opción sería CON la REVERSIÓN DESPUÉS de 10 SEGUNDOS (o algo similar).
  4. 7

    Probar este

    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) 
  5. 4

    La reducción del tamaño del Archivo de Registro de

    Para los archivos de registro, el Motor de Base de datos utiliza 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. Tamaño de destino para el registro entero se traduce en el tamaño final de cada archivo de registro. DBCC SHRINKFILE intenta comprimir cada archivo de registro físico a su tamaño de destino inmediatamente.

    Sin embargo, si una parte del registro lógico se encuentra en los registros virtuales más allá de la meta de tamaño, el Motor de Base de datos libera tanto espacio como sea posible, y, a continuación, emite un mensaje informativo.

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

    Porque un archivo de registro sólo puede ser reducido a un archivo de registro virtual de límite, la reducción del tamaño del archivo de registro a un tamaño más pequeño que el tamaño de un archivo de registro virtual podría no ser posible, incluso si no está siendo utilizado. El tamaño del archivo de registro virtual es elegido de forma dinámica por el Motor de Base de datos cuando se crean archivos de registro o extendida.

    • Solución De Problemas: El Archivo No Se Reduce

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

    Ejecutar la siguiente consulta.

    SELECCIONE el nombre ,el tamaño/128.0 – CAST(FILEPROPERTY(nombre, ‘SpaceUsed’) COMO
    int)/128.0 COMO AvailableSpaceInMB DE sys.database_files;

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

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

    • Normalmente es el archivo de registro que aparece no se reduce. Este es generalmente el resultado de un archivo de registro que no ha sido truncada.

    • Puede truncar el registro por la configuración de la base de datos modelo de recuperación SIMPLE, o por copia de seguridad del registro y, a continuación, ejecuta el DBCC SHRINKFILE de nuevo la operación.

    Ejemplo :

    La reducción del tamaño del archivo de registro para un tamaño de destino especificado

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

    De Transact-SQL

    USO de AdventureWorks2012;

    IR

    — Truncar el registro de cambiar el modelo de recuperación SIMPLE.

    ALTERAR la BASE de datos AdventureWorks2012

    LA RECUPERACIÓN SIMPLE;

    IR

    — Reducir el trunca el archivo de registro a 1 MB.

    DBCC SHRINKFILE (AdventureWorks2012_Log, 1);

    IR

    — Restablecer la base de datos del modelo de recuperación.

    ALTERAR la BASE de datos AdventureWorks2012

    LA RECUPERACIÓN COMPLETA;

    IR

    Cuando se utiliza DBCC SHRINKFILE(Logfile, tamaño) sólo se trunca a partir del final del archivo de registro tan lejos como puede ir. Cuando se alcanza el máximo de registro virtual todavía en uso, no se puede reducir más. Esto se describe en los Libros de SQL Server en Línea en:

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

    Así, una vez que el extremo superior de la corredera es claro, se puede reducir en tamaño. De nuevo, eso dependerá de qué parte de la sesión está todavía en uso. El registro se puede borrar las copias de seguridad, pero las copias de seguridad no borrar las transacciones incompletas, por lo que el registro puede permanecer en una de gama alta VLF, incluso después de varias copias de seguridad.

    Con respecto al aumento y la disminución de Vlf, ¿cuán grande era el archivo de registro creado al principio y cuál es la configuración para el archivo de registro de crecimiento? Si crece por sólo una pequeña cantidad se va a crear más Vlf que nadie deseos.

    Un patrón común para la reducción del tamaño del archivo de registro de punto de control, COPIA de seguridad, SHRINKFILE, punto de control, COPIA de seguridad, SHRINKFILE, etc, hasta que se obtienen resultados. Hay muchas razones por las que el registro no puede ser retráctil, incluyendo un gran reversión.

    De conmutación de lo Simple a Plena tiene un Problema:

    Hay reglas y excepciones aquí. Vamos a hablar mucho de transacciones en profundidad a continuación.

    Pero una advertencia a tener en cuenta para el Modo de Recuperación Completa es esta: Si usted acaba de cambiar al modo de Recuperación Completa, pero nunca tome una Copia de seguridad Completa inicial, SQL Server no va a cumplir con su solicitud para ser en el modelo de Recuperación Completa. El registro de transacciones seguirán funcionando como lo ha hecho en Simpleuntil cambiar a Modelo de Recuperación Completa Y tomar su primera Copia de seguridad Completa.

    Modelo de Recuperación completa sin copias de seguridad del registro es malo:

    Así que esa es la razón más común para el incontrolado crecimiento de registro? Respuesta: Está en modo de Recuperación Completa sin tener copias de seguridad del registro.

    Esto sucede todo el tiempo a la gente.

    ¿Por qué esto es un error común?

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

    Modelo inicial de la configuración del modelo de recuperación es siempre el Modelo de Recuperación Completa – hasta y a menos que alguien los cambios que. Así que se podría decir que el «Modelo de Recuperación predeterminado» es Completa. Muchas personas no son conscientes de ello y tienen sus bases de datos que se ejecuta en el Modelo de Recuperación Completa sin copias de seguridad del registro, y por lo tanto un archivo de registro de transacciones mucho más grande de lo necesario. Esto es por qué es importante para cambiar los valores predeterminados cuando ellos no trabajan para su organización y sus necesidades)

  6. 2

    He intentado de muchas maneras pero esta funciona.

    Código de ejemplo 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
  7. -1

    Gracias a @user2630576 y @Ed.S.

    la siguiente trabajado tratar:

    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

Dejar respuesta

Please enter your comment!
Please enter your name here