INF: Cómo reducir el registro de transacciones de SQL Server

Seleccione idioma Seleccione idioma
Id. de artículo: 256650 - Ver los productos a los que se aplica este artículo
Este artículo se publicó anteriormente con el número E256650
Expandir todo | Contraer todo

En esta página

Resumen

Existen varias razones que pueden hacer que el registro de transacciones no se reduzca al usar los comandos DBCC SHRINKFILE o DBCC SHRINKDATABASE. Los temas de los Libros en pantalla de SQL Server "DBCC SHRINKFILE" y "DBCC SHRINKDATABASE" ofrecen información detallada, pero a continuación le proporcionamos un breve resumen.

Más información

  • En Microsoft SQL Server 7.0, los comandos SHRINKFILE y SHRINKDATABASE establecen un tamaño máximo hasta el que se debe reducir el archivo. Estos comandos marcan cada archivo de registro, pero cuando realmente se intenta reducir el tamaño de los archivos es al hacer una copia de seguridad o al truncar el registro. Por tanto, después de usar los comandos SHRINKFILE o SHRINKDATABASE es necesario ejecutar un comando que trunque el registro para que exista la posibilidad de reducir el tamaño del archivo.
  • No se puede reducir un registro a un tamaño inferior al permitido según el siguiente criterio:

    • Para reducir un registro a un tamaño inferior a su tamaño original debe reducir los archivos individuales con DBCC SHRINKFILE. No se puede utilizar DBCC SHRINKDATABASE para reducir un registro a un tamaño inferior a su tamaño original o a un tamaño explícitamente definido. El tamaño original está definido como el tamaño del registro asignado al con la ejecución de CREATE DATABASE más el asignado con la ejecución explícita de cualquier comando ALTER DATABASE. El tamaño original no incluye el crecimiento automático del registro.

    • El archivo físico de registro nunca puede ser más pequeño que el espacio que está siendo utilizado por el archivo de registro. Puede usar el comando DBCC SQLPERF (LOGSPACE) para supervisar la cantidad de espacio utilizado.

    • El tamaño actual del registro de la base de datos model es el tamaño mínimo de registro de cualquier base de datos de ese servidor. De manera predeterminada, el tamaño del registro de la base de datos model es inferior a 1 MB.

    • Dado que un registro sólo se puede reducir hasta el límite de un archivo de registro virtual (VLF, Virtual Log File), no es posible reducir el tamaño de un archivo de registro a un tamaño inferior que el de un archivo de registro virtual (VLF) aunque ese espacio no esté siendo utilizado. De igual forma, si una parte de un archivo de registro virtual está siendo utilizada, no se puede reducir el espacio de ese archivo VLF. Para obtener más información, consulte los temas "Archivos de registro virtual" y "Arquitectura física del registro de transacciones" en los Libros en pantall de SQL Server.


  • El registro de transacciones es "circular". Esto quiere decir que en cualquier momento puede haber archivos VLF con espacio "libre" o "reutilizable" al comienzo, en medio, o al final del registro. Para reducir el registro debe haber espacio "libre" al final del registro, no sólo espacio libre en cualquier otra parte del registro. Además, sólo se pueden reducir archivos VLF enteros. Para reducir el registro de transacciones los archivos VLF que están al final del archivo de registro deben estar marcados como inactivos y truncados. Para obtener información detallada, consulte el tema "Truncamiento del registro de transacciones" en los Libros en pantalla de SQL Server.
A continuación presentamos algunos detalles que debe recordar:
  • Siempre, antes y después de hacer modificaciones que afecten al sistema, debe realizar copias de seguridad de las bases de datos de sistema y de usuario. Las operaciones realizadas con los comandos DBCC SHRINKFILE y DBCC SHRINKDATABASE no quedan registradas en el registro de transacciones y su ejecución invalida cualquier copia de seguridad que realice posteriormente del registro de transacciones. Debe realizar una copia de seguridad completa después de ejecutar el comando DBCC SHRINKFILE o DBCC SHRINKDATABASE.

  • Asegúrese que no hay ninguna copia de seguridad programada para iniciarse durante el tiempo en el que se supone que se va a realizar la reducción del tamaño del registro.

  • Asegúrese de que no hay transacciones antiguas, que lleven excesivo tiempo ejecutándose o que no hayan sido replicadas. Para ello, utilice código como el siguiente:
    DBCC OPENTRAN (nombre_de_base_de_datos)
  • Ejecute el comando DBCC SHRINKFILE o DBCC SHRINKDATABASE para marcar un punto de reducción. De manera predeterminada, tienen permiso para ejecutar DBCC SHRINKFILE y DBCC SHRINKDATABASE los miembros del rol de servidor sysadmin o los miembros del rol de base de datos db_owner y estos permisos no son transferibles. Para obtener información acerca de las diferencias existentes entre estos comandos, consulte los siguientes temas en los Libros en pantalla de SQL Server (observe los distintos parámetros):

    DBCC SHRINKFILE     (nombre_de_archivo, tamaño_final)
    DBCC SHRINKDATABASE (nombre_de_base_de_datos, porcentaje_final)
  • Cree algunas transacciones para conseguir que el registro de la vuelta y, a continuación, ejecute un comando BACKUP para truncar el registro. La instrucción BACKUP es la que intenta reducir el registro hasta alcanzar el tamaño final marcado.

    A continuación encontrará un ejemplo sobre cómo crear transacciones que permitan dar la vuelta al registro para formar un único archivo de registro lógico y que se trunque, permitiendo así la reducción de tamaño del archivo físico. Modifique el ejemplo según sea necesario para su entorno.
    SET NOCOUNT ON
    DECLARE @NombreLogicoArchivo sysname,
            @MaxMinutos INT,
          
      @NuevoTamano INT
    
    -- Indique aquí su configuración.
    USE    Su_Base_De_Datos -- Nombre de la base de datos para el que se va a truncar el registro
    -- Use sp_helpfile para identificar el nombre de archivo lógico que desea reducir.
    SELECT  @NombreLogicoArchivo = 'Su_registro',  
            @MaxMinutos = 10,    -- Límite de tiempo permitido para dar la vuelta al registro. 
            @NuevoTamano = 2000   -- en MB
    
    -- Configuración e inicio 
    DECLARE @TamanoOriginal int
    SELECT @TamanoOriginal = size -- en páginas de 8 KB 
      FROM sysfiles
      WHERE name = @NombreLogicoArchivo 
    SELECT ' El tamaño original del registro de ' + db_name() + ' es ' + CONVERT(VARCHAR(30),@TamanoOriginal) + ' páginas de 8 KB ó ' + CONVERT(VARCHAR(30),(@TamanoOriginal *8/1024)) + 'MB'
      FROM sysfiles
      WHERE name = @NombreLogicoArchivo 
    CREATE TABLE DummyTrans(DummyColumna char (8000) not null)
    
    
    -- Dar la vuelta al registro y truncarlo. 
    DECLARE @Contador   INT,
            @HoraInicio DATETIME,
            @TruncReg  VARCHAR(255)
    SELECT  @HoraInicio = GETDATE(),@TruncReg = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
    -- Intentar una reducción inicial. 
    DBCC SHRINKFILE (@NombreLogicoArchivo, @NuevoTamano)
    EXEC (@TruncReg)
    -- Dar la vuelta al registro, si es necesario.
    -- no se ha excedido el máximo tiempo establecido 
    WHILE     @MaxMinutos > DATEDIFF (mi, @HoraInicio, GETDATE())
    -- no se ha reducido el registro 
          AND @TamanoOriginal = (SELECT size FROM sysfiles WHERE name = @NombreLogicoArchivo)  
    -- El valor pasado para el tamaño nuevo es más pequeño que el tamaño actual. 
          AND (@TamanoOriginal * 8 /1024) > @NuevoTamano  
      BEGIN -- Bucle externo. 
        SELECT @Contador = 0 WHILE  ((@Contador < @TamanoOriginal / 16) AND (@Contador < 50000))
        
      BEGIN -- Actualización 
    -- Como es un campo de tipo char, inserta 8000 bytes.
         
       INSERT DummyTrans VALUES ('Llenar registro')  
            DELETE DummyTrans
            SELECT @Contador = @Contador + 1
          END   -- Actualización Probar si un truncamiento reduce de tamaño el registro. 
        EXEC (@TruncReg)  
      END   -- Bucle externo
     
    SELECT ' El tamaño final del registro de ' + db_name() + ' es de ' + CONVERT(VARCHAR(30),size) + ' páginas de 8 KB ó ' + CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
      FROM sysfiles 
      WHERE name = @NombreLogicoArchivo
    DROP TABLE DummyTrans
    PRINT '*** RECUERDE: DEBE REALIZAR UNA COPIA DE SEGURIDAD COMPLETA DE LA BASE DE DATOS***'
    SET NOCOUNT OFF
    
    
    Compruebe que el registro se ha reducido con respecto a su tamaño original. Repita los pasos anteriores si fuese necesario. Si el registro no se reduce, vuelva a comprobar lo descrito en el resumen que aparece al comienzo de este artículo por si estuviese ante uno de los casos ahí explicados.
Después de reducir el registro:
  1. Realice una copia de seguridad completa de la base de datos master.
  2. Realice una copia de seguridad completa de la base de datos de usuario. Esto es necesario porque el comando SHRINK no graba sus operaciones en el registro de transacciones e invalida cualquier copia de seguridad de registro de transacciones que hiciese en el futuro, a menos que realice una copia completa de la base de datos.
Para determinar las causas por las que inicialmente creció exageradamente el registro de transacciones, puede revisar si hay transacciones abiertas, transacciones que tardan mucho en ejecutarse, transacciones no replicadas o transacciones que modifican muchos datos.

REFERENCIAS

Para obtener información adicional, haga clic en los números de artículo que aparecen a continuación para ver los artículos en Microsoft Knowledge Base:
110139 INF: Causes of SQL Transaction Log Filling Up
62866 INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
66057 PRB: Running Out of Log Space When Running Large Bulk Loads
80629 PRB: Transaction Log Partially Truncated
Los siguientes temas de los Libros en pantalla de SQL Server: "Arquitectura física del registro de transacciones "; "Optimizar el rendimiento del registro de transacciones"

Propiedades

Id. de artículo: 256650 - Última revisión: jueves, 10 de abril de 2003 - Versión: 1.0
La información de este artículo se refiere a:
  • Microsoft SQL Server 7.0 Standard Edition
Palabras clave: 
kbinfo kbsqlserv700 kbsqlserv KB256650

Enviar comentarios

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com