Por: Sergey Gigoyan | Actualizado: 2015-11-18 | Comentarios (9) | Relacionados: Más > Administración de bases de datos
Problema
SQL Server permite usar más de un archivo de registro de transacciones, pero surge la pregunta de si es necesario y cuál es el beneficio de tener varios archivos de registro de transacciones. Hay una idea errónea entre algunos desarrolladores de que tener archivos de registro de multipletransacción puede aumentar el rendimiento porque SQL Server puede usarlos en paralelo. SQL Server solo usa un archivo de registro de transacciones en este momento y no hay paralelismo en este caso. A veces, se necesita más de un archivo de registro para solucionar problemas. Por lo tanto, normalmente no hay necesidad de tener más de un archivo de registro.Consideremos un caso, cuando nuestra base de datos tiene más de un archivo de registro y deberíamos conservar solo uno.Este consejo tiene como objetivo describir cómo eliminar correctamente los archivos de registro innecesarios y conservar solo uno.
Solución
Antes de comenzar a ilustrar cómo eliminar archivos de registro innecesarios, describamos brevemente cómo funciona SQL Server con archivos de registro: cuando la base de datos tiene más de un archivo de registro, SQL Server sigue escribiendo en el primero hasta que se llena,luego cambia al segundo y así sucesivamente. Después de que el último archivo de registro esté lleno, SQL Server vuelve al primero y el ciclo continúa.Sin embargo, como mencionamos, a veces se puede requerir más de un archivo de registro. Por ejemplo, cuando el disco, donde se encuentra el archivo de registro, se llena y necesitamos crear el segundo archivo de registro en otra ubicación, pero después de solucionar el problema,debemos eliminar el segundo archivo de registro, porque no sirve de nada tener más de un archivo de registro.Ahora, supongamos que tenemos una base de datos con dos archivos de registro, y nuestra tarea es eliminar el segundo.El siguiente script crea la base de datos TestDB con dos archivos de registro y una tabla TestTable. Para ejecutarlo usted mismo, tendrá que reemplazar»D:\SQL Datos» con una ruta de carpeta existente.
USE GOCREATE DATABASE ON PRIMARY ( NAME = N'TestDB', FILENAME = N'D:\SQL DATA\TestDB.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'TestDB_log1', FILENAME = N'D:\SQL DATA\TestDB_log1.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%), ( NAME = N'TestDB_log2', FILENAME = N'D:\SQL DATA\TestDB_log2.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOUSE TestDBGOCREATE TABLE TestTable(ID INT IDENTITY(1,1),Value BIGINT)--Change recovery model for TestDB to FULL (if your model database is in FULL recovery model, TestDB has been already created in this mode)ALTER DATABASE TestDB SET RECOVERY FULL
Ahora vamos a entender la estructura física del registro de transacciones: El archivo de registro de transacciones interno consiste en Archivos de Registro virtuales (VLF), que son la unidad de administración en el archivo de registro de transacciones. Significa que cuando el motor de base de datos crece o reduce el archivo de registro, lo hace con VLFS completos (por ejemplo, no puede reducir la mitad del VLF). El tamaño de los archivos de registro virtuales, así como su número en el archivo de registro físico, no es fijo y es administrado dinámicamente por el motor de base de datos. Para monitorear el archivo de registro de transacciones internamente, usamos el comando «DBCC LOGINFO», que proporciona información sobre los archivos de registro virtuales. En el script de abajo usamos este comando para nuestra base de datos:
--Getting log files infoDBCC LOGINFO('TestDB')
Y el resultado es el siguiente:
fileID es un ID de archivo de registro para nuestra base de datos. El estado indica que es ornot reutilizable VLF (valores posibles: 0-sí, 2-no). Como podemos ver, solo hay un VLF withStatus = 2. Ahora, cuando insertaremos datos en la mesa de pruebas y monitorearemos cómo están creciendo los archivos de registro:
USE TestDBGO--Checking log information before insertionSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files--Inserting data into TestTable;WITH ValueTable AS(SELECT 1 nUNION ALL SELECT n+ 1FROM ValueTableWHERE n
Con este ejemplo podemos ver que ambos archivos de registro crecieron y ahora hay VLFs con «Status=2» en el segundo archivo de registro (fileID=3) también:
Ahora necesitamos eliminar TestDB_log2.archivo ldf. Tenga en cuenta que solo podemos eliminar los archivos de registro secundarios. SQL Server no permite la eliminación del archivo de registro principal.Cada base de datos solo tiene un archivo de registro primario y el primer archivo de registro que se crea en el script de creación de la base de datos se considera el principal.Si intentamos eliminar el segundo archivo de registro:
USE masterGO--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
Recibiremos el siguiente mensaje:
Podemos eliminar el archivo de registro de transacciones solo cuando está vacío, por lo tanto, primero necesitamos vaciarlo it.To haz eso, deberíamos hacer una copia de seguridad del registro de transacciones. Dado que nuestra base de datos» TestDB » se ha creado recientemente y no hay copias de seguridad completas, necesitamos emitir una copia de seguridad completa de la base de datos de TestDB, después de lo cual podemos emitir una copia de seguridad del registro de transacciones:
--Full backupBACKUP DATABASE TestDB TO DISK =N'D:\SQL DATA\TestDB.bak'--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'
La copia de seguridad del registro de transacciones trunca el archivo de registro (hay algunas excepciones, que están fuera del alcance de esta sugerencia). El truncamiento de registros elimina los archivos de registro virtuales inactivos desde el inicio del registro lógico y libera espacio en el archivo de registro. Sin embargo, el truncamiento no reduce el tamaño de un archivo de registro físico. Solo libera espacio en él, que puede ser reutilizado. Volvamos a ejecutar «DBCC LOGINFO» :
Como podemos ver, no hay archivos de registro virtuales en «TestDB_log2.archivo ldf » con Estado = 2 y ahora nuestro archivo de registro está vacío y listo para ser eliminado:
--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
La eliminación es exitosa.
Sin embargo, cuando comprobemos registrar la información de nuevo, veremos que el archivo de registro lógico todavía existe:
--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files
Si hacemos otra copia de seguridad de registro, el archivo se eliminará:
--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files
Pasos siguientes
- Tenga en cuenta este consejo para determinar el uso del registro de transacciones y cómo eliminar un archivo de registro innecesario.
- Consulte estos recursos:
- Administración de bases de datos | Registro de transacciones
- https://technet.microsoft.com/en-us/library/ms191433(v = sql.105).aspx
- https://technet.microsoft.com/en-us/library/ms179355(v = sql.105).aspx
- https://technet.microsoft.com/en-us/library/ms345414(v = sql.105).aspx
Sobre el autor
Sergey Gigoyan es un profesional de bases de datos con más de 10 años de experiencia, con un enfoque en el diseño, desarrollo, ajuste de rendimiento, optimización, alta disponibilidad, diseño de BI y DW de bases de datos.Ver todos mis consejos
Artículo Última actualización: 2015-11-18