En cada base de datos contiene al menos un archivo de datos y un archivo de registro de transacciones. SQL Server almacena los datos físicamente en el archivo de datos (.mdf y .ndf). El archivo de transacciones (.ldf) almacena los detalles de todas las modificaciones que se realizan sobre la base de datos de SQL Server.
La escritura en el Log de transacciones es secuencial, y esta optimizado para ello. Se podría decir que (por norma general) carece de sentido crear más de un fichero de log de transacciones. Aunque el algoritmo de escritura en los .ldf es algo más complejo: si tuviéramos mas de un fichero, la escritura la haría formando un bucle circular pasando por cada uno de ellos, respetando la secuencialidad en las transacciones. A diferencia de los ficheros de datos, donde si es posible mejorar el rendimiento de una base de datos, aumentado su número.
Es aconsejable ubicar el fichero del log de transacciones en diferente disco donde se encuentren los ficheros de datos.
Modelo de recuperación del log de transacciones en una Base de datos
El objetivo de este punto, no es explicar los procesos de backup y restore, sino hacer un resumen de los distintos estados en que se pueden configurar el log de transacciones.
El modelo de recuperación de una base de datos puede cambiarse en cualquier momento. No es frecuente cambiar de modelo de recuperación. Tenemos tres modos de configurar el log de transacciones: Simple, Full (completo) y , bulk-logged (recuperación optimizado para cargas masivas de registros).
Modelo de recuperación Simple:
Sin necesidad de hacer copias de seguridad del log de transacciones, se reduce automáticamente el espacio de registro, manteniendo al mínimo el espacio del fichero segun termina las transacciones de las consultas. De este modo no es necesario administrar el espacio del log de transacciones.
Los cambios realizados después de la copia de seguridad más reciente no están protegidos. En caso de desastre, es necesario volver a realizar dichos cambios. Sólo se puede recuperar hasta el final de una copia de seguridad.
Modelo de recuperación Completa:
Requiere copias de seguridad del log de transacciones. No se pierde trabajo si un archivo de datos se pierde o resulta dañado. Se puede recuperar hasta cualquier momento, por ejemplo, antes del error de aplicación o usuario.
Si la base de datos resulta dañada, se deben repetir los cambios realizados desde la última copia de seguridad del log de transacciones. Se puede recuperar hasta una determinado momento, siempre que las copias de seguridad se hayan completado hasta ese momento.
Modelo de recuperación bulk-logged:
Requiere copias de seguridad del log de transacciones, para ir liberando espacion en el .ldf. Puede considerarse complemento del modelo de recuperación completa, pero no sustituo, ya que permite operaciones de copia masiva de alto rendimiento, (por ejemplo, operaciones realizadas con BCP.exe, Bulk Insert, etc…), reduciendo el uso del espacio de registro.
Si la base de datos resulta dañada o se han realizado operaciones masivas desde la última copia de seguridad completa, se han de repetir los cambios desde esa última copia de seguridad. Se puede recuperar hasta el final de cualquier copia de seguridad completa. No admite recuperaciones a un momento dado.
El "set recovery…" es la opción que permite elegir el modelo de recuperación entre simple, bulk-logged y completo. Los tres permiten realizar restores, pero sólo el modelo de recuperación completo registra y mantiene en el log de transacciones todas las operaciones e implica la realización de backups del log dentro de la política de backups. Es la opción por defecto y la recomendable (salvo circunstancias excepcionales). Ello permite operaciones como estas (recuperar un backup hasta un punto en el tiempo, hasta una marca). En el modelo de recuperación simple no es así, las operaciones quedan mínimamente logadas, los backups del log no pueden realizarse (carecen de sentido).
A modo de ejemplo muestro como cambiar el modelo de configuración del log de transacciones:
alter database bbdd set recovery simple -- Pone el modelo de recuperación del log a simple
go
alter database bbdd set recovery full -- Pone el modelo de recuperación del log a completa.
go
Si cambias al modelo de recuperación simple, interrumpirá la cadena de copias de seguridad de registros. Por lo tanto, es muy recomendable realizar una copia de seguridad del registro inmediatamente antes de realizar el cambio. De esta manera, podrá recuperar la base de datos hasta ese momento. Tras el cambio, necesitará realizar copias de seguridad completas y periódicas para proteger sus datos y para truncar la parte inactiva del registro de transacciones.
El cambio al modelo de recuperación completa o bulk-logged sólo tiene efecto después de la primera copia de seguridad de base de datos.
backup database TU_bbdd to disk = '<Unida:_ruta> TU_bbdd.bak' with init , NOUNLOAD , NAME = N'Copia de seguridad TU_bbdd', NOSKIP , STATS = 10, NOFORMAT
Si no se quieren sobrescribir los ficheros de backup. Muestro un ejemplo, de cómo crear los backups, teniendo en cuenta el nombre de los ficheros, de esta forma mantendremos una secuencia en los nombres:
declare @fichero varchar(250)
select @fichero = '< Unida:_ruta>bbdd_tlog_' + convert(varchar(20), getdate(),112) + left(replace(convert(varchar(10), getdate(), 114), ':', ''), 4) + '.Bak'
backup database TU_bbdd to disk = @fichero with init;
Las copias de seguridad del log de transacciones son un aspecto fundamental de los modelos de recuperación completa o bulk-logged. Las copias de seguridad de registros permiten que se trunque el registro de transacciones. Si no realiza la copia de seguridad con la frecuencia suficiente, el registro de transacciones se puede expandir hasta quedarse sin espacio en disco. Muestro un ejemplo, de como crear los backups del log de transacciones, teniendo en cuenta el nombre de los ficheros, de esta forma mantendremos una secuencia en los nombres, por si fuera necesario restaurar, en un punto en el tiempo.
declare @fichero varchar(250)
select @fichero = '< Unida:_ruta TU_bbdd_tlog_' + convert(varchar(20), getdate(),112) + left(replace(convert(varchar(10), getdate(), 114), ':', ''), 4) + '.trn'
backup log [TU_bbdd] to disk = @fichero
Si cambia del modelo de recuperación completa o bulk-logged al modelo de recuperación simple, interrumpirá la cadena de copias de seguridad de registros. Por lo tanto, es muy recomendable realizar una copia de seguridad del registro inmediatamente antes de realizar el cambio. De esta manera, podrá recuperar la base de datos hasta ese momento. Tras el cambio, necesitará realizar copias de seguridad periódicas para proteger sus datos y para truncar la parte inactiva del registro de transacciones.
Registro de transacciones lleno (Error 9002)
En este tema se tratan las posibles respuestas a un registro de transacciones lleno y se sugiere cómo evitar esta situación en el futuro. Cuando el registro de transacciones se llena, SQL Server Database Engine (Motor de base de datos de SQL Server) genera un error 9002. El registro se puede llenar cuando la base de datos está en línea o en recuperación. Si el registro se llena cuando la base de datos está en línea, la base de datos seguirá en conexión, pero solo se puede leer y no actualizar. Si el registro se llena durante la recuperación, Motor de base de datos marca la base de datos como RESOURCE PENDING. En ambos casos, es necesaria la intervención del usuario para proporcionar espacio de registro.
Si la base de datos estaba en recuperación cuando se produjo el error 9002, una vez resuelto el problema, recupere la base de datos mediante:
ALTER DATABASE nombreDeBaseDeDatos SET ONLINE.
Las columnas log_reuse_wait y log_reuse_wait_desc de la vista de catálogo sys.database.
La respuesta apropiada a un registro de transacciones lleno depende en parte de la condición o condiciones que han causado que el registro se llene. Para descubrir qué impide el truncamiento del registro en un caso determinado, use las columnas log_reuse_wait y log_reuse_wait_desc de la vista de catálogo sys.database.
Podemos mostrar la información del estado en que se encuentra el log de transacciones con la consulta :
Select name as base_datos, log_reuse_wait, log_reuse_wait_desc, recovery_model_desc as modo_recuperacion_log, page_verify_option_desc as page_verify_bbdd, user_access_desc as user_access, state_desc as estado_bbdd from sys.databases
Podemos cruzar el resultado de la anterior consulta, con la siguiente tabla, para entender el resultado de la consulta anterior:
|
valor log_reuse_wait |
valor log_reuse_wait_desc |
Descripción |
|
0 |
NOTHING |
Hay actualmente uno o más archivos de registro virtual reutilizables. |
|
1 |
CHECKPOINT |
No se ha producido ningún punto de comprobación desde el último truncamiento o el encabezado del registro no se ha movido más allá de un archivo de registro virtual (todos los modelos de recuperación). Éste es un motivo habitual para retrasar el truncamiento. |
|
2 |
LOG_BACKUP |
Se necesita una copia de seguridad del registro para hacer avanzar el encabezado del registro (modelos de recuperación completos o registrados de forma masiva sólo). Cuando se completa la copia de seguridad del registro, se avanza el encabezado del registro y algún espacio del registro podría convertirse en reutilizable. |
|
3 |
ACTIVE_BACKUP_OR_RESTORE |
Existe una recuperación o copia de seguridad de datos en curso (todos los modelos de recuperación).
La copia de seguridad de datos funciona como una transacción activa y, cuando se ejecuta, la copia de seguridad impide el truncamiento. |
|
4 |
ACTIVE_TRANSACTION |
Podría existir una transacción de larga duración en el inicio de la copia de seguridad del registro. En este caso, para liberar espacio se podría requerir otra copia de seguridad del registro.
Se difiere una transacción. Una transacción diferida es efectivamente una transacción activa cuya reversión se bloquea debido a algún recurso no disponible. |
|
5 |
DATABASE_MIRRORING |
Se realiza una pausa en la creación de reflejo de la base de datos o, en el modo de alto rendimiento, la base de datos reflejada está notablemente detrás de la base de datos de la entidad de seguridad (sólo para el modelo de recuperación completa). |
|
6 |
REPLICATION |
Durante las replicaciones transaccionales, las transacciones relevante para las publicaciones no se han entregado aún a la base de datos de distribución (sólo para el modelo de recuperación completa). |
|
7 |
DATABASE_SNAPSHOT_CREATION |
Se está creando una instantánea de base de datos (todos los modelos de recuperación). Éste es un motivo habitual, por lo general breve, para retrasar el truncamiento del registro. |
|
8 |
LOG_SCAN |
Se está produciendo un recorrido del registro (todos los modelos de recuperación). Éste es un motivo habitual, por lo general breve, para retrasar el truncamiento del registro. |
|
9 |
OTHER_TRANSIENT |
No se utiliza este valor actualmente. |
Una de las cosas que se pueden revisar, cuando nos vemos ante un registro de transacciones lleno son:
1. Realizar copias de seguridad del registro.
2. Liberar espacio en disco para que el registro pueda crecer automáticamente.
3. Mover el archivo de registro a una unidad de disco con suficiente espacio.
Mover el fichero .ldf de una base de usuario de ubicación:
Antes de nada, realiza un backup de la bbdd, por lo que pueda pasar.
Extraiga la ubicación física de sus archivos de la base de datos.
USE DataBaseName
GO
EXEC sp_helpfile
GO
Desatachamos la base de datos:
use master
GO
Exec sp_detach_db 'DataBaseName'
GO
Movemos los ficheros .mdf y .ldf a los nuevos volúmenes, en nuestro caso el .ldf:
use master
GO
Exec sp_attach_db 'DataBaseName','<Ruta y unidad>DataBaseName_Data.MDF','<Nueva ruta y unidad>DataBaseName_Log.LDF'
GO
Esta forma, tenderá a desaparecer en versiones futuras, por lo que os paso como se ha de realizar:
Borraremos la Basedatos, sin eliminar los ficheros de datos ni el fichero del log de transacciones. copiamos los .mdf y .ldf a sus discos difinitivos. y:
USE [master]
GO
CREATE DATABASE [database_name] ON
( FILENAME = N'<Ruta y unidad>DataBaseName_Data.mdf' ),
( FILENAME = N'<Nueva ruta y unidad>DataBaseName_Log.ldf' )
FOR ATTACH ;
GO
4. Aumentar el tamaño del log de transacciones.
En este ejemplo aumentamos el tamaño del fichero .ldf a 2GB:
USE master
GO
ALTER DATABASE database_name
MODIFY FILE ( NAME = N'<Nombre_logico_ldf>', SIZE = 2097152KB )
GO
5. Agregar un archivo adicional para el log de transacciones en un disco diferente. Para agregar un archivo de registro a la base de datos, utilice la cláusula ADD LOG FILE de la instrucción ALTER DATABASE
En este ejemplo se añaden 2 archivos adicionales para el log de transacciones:
USE master;
GO
ALTER DATABASE databasename
ADD LOG FILE
(
NAME = test1log2,
FILENAME = '<Unidad y ruta>databasename2log.ldf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
),
(
NAME = test1log3,
FILENAME = '<Unidad y ruta>databasename 3log.ldf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
);
GO
6. Terminar o eliminar una transacción de larga duración. Leres siguiente punto, “transacción activa”.
Transacción activa
Una causa probable de que el registro esté lleno es que se haya realizado una transacción de ejecución prolongada. Una transacción de ejecución prolongada se mantiene activa en el log de transacciones e impide el truncamiento del archivo.
Una transacción de ejecución muy prolongada hará que el registro de transacciones se llene. Para buscar las transacciones de ejecución prolongada, use la vista: sys.dm_tran_database_transactions
Select * from sys.dm_tran_database_transactions
Esta vista de administración dinámica devuelve información sobre las transacciones en la base de datos. En una transacción de ejecución prolongada, las columnas de especial interés incluyen la hora de la primera entrada del registro (database_transaction_begin_time), el estado actual de la transacción (database_transaction_state) y el número de secuencia (LSN) del registro inicial del log de transacciones (database_transaction_begin_lsn).
Valores que puede adoptar el campo database_transaction_type:
1 = Transacciones de lectura/escritura
2 = Transacción de sólo lectura
3 = Transacción de sistema
Valores que puede adoptar el campo database_transaction_state:
1 = La transacción no se ha inicializado.
3 = La transacción se ha inicializado, pero no se han generado registros.
4 = La transacción ha generado registros.
5 = La transacción se ha preparado.
10 = La transacción se ha confirmado.
11 = La transacción se ha revertido.
12 = La transacción se está confirmando. En este estado el registro se está generando, pero no se ha materializado o se ha hecho permanente.
Otra forma de detectar las transacciones activas es con: DBCC OPENTRAN:
Esta instrucción “DBCC OPENTRAN” permite identificar el Id. de usuario del propietario de la transacción, por lo que se puede realizar un seguimiento del origen de la misma para terminarla de forma más ordenada, otro dato que nos devuelve es el SPID de la transacción.
Para detener la transacción, puede que deba utilizar la instrucción KILL. Utilice esta instrucción con sumo cuidado, especialmente cuando se estén ejecutando procesos críticos
DBCC SQLPERF (LOGSPACE)
Puede supervisar el uso del espacio del log de transacciones mediante el comando DBCC SQLPERF (LOGSPACE). Este comando devuelve información sobre la cantidad de espacio del registro de transacciones actualmente en uso e indica cuándo es necesario el truncamiento del registro de transacciones. Para obtener información acerca del tamaño actual de un archivo de registro, su tamaño máximo y la opción de crecimiento automático de este archivo, se pueden usar las columnas size, max_size y growth de ese archivo de registro en sys.database_files.
Formas para liberar espacio o reducir el tamaño del log de transacciones.
Es conveniente que antes de cualquier reducción del log, realice un backup del mismo:
declare @fichero varchar(250)
select @fichero = '< Unida:_ruta TU_bbdd_tlog_' + convert(varchar(20), getdate(),112) + left(replace(convert(varchar(10), getdate(), 114), ':', ''), 4) + '.trn'
backup log [TU_bbdd] to disk = @fichero
Cuando hacemos backup del log y no se especifica lo contrario, marca la parte no activa del log (transacciones que ya se les hizo commit) para ser reusada con nuevas transacciones. Si la parte no activa del log de transacciones no se marca para ser reusada, con el "backup del log", se podrá llegar al final del archivo, y si las transacciones continúan, entonces el log debera crecer para seguir almacenando el resto de transacciones. Ojo, que el backup completo no trunca el log, asi que en dependencia de la frecuencia con que hagas el backup del log, tiene sentido hacer un backup del log despues del backup completo.
Casos prácticos 1.:
Backup log “<BaseDatos_Name>” with no_log -->> borra la parte no activa del log, pero no reduce el log.
Backup log <BaseDatos_Name> with truncate_only -->> Borra todo el log, pero no reduce el log.
Desde SQL Server 2008, ya no se puede truncar el log, realizando un backup, con las opciones no_log y truncate_only, no funcionan.
Luego, lo importante es saber qué impide que el log de transacciones se reduzca o se recicle, para lo cual, lo más fácil es mirarlo en sys.databases, en la columna log_reuse_wait_desc. Aquí tienes su explicación: http://msdn.microsoft.com/es-es/library/ms345414.aspx. En función de lo que marque esa columna, el modelo de recuperación que tengas y para la base de datos que quieres reducir, tendrás que tomar una acción u otra, a continuación, como realizar una reducción con un shrink:
DBCC SHRINKFILE(<NombreLogico_log>, 512) -->> Reduce el log a n Mb
Casos prácticos 2.:
Si no se deja comprimir o borrar el log por que no tiene sus trasacciones como distribuidas ha que ponerlas, de la forma:
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
--->Ver el tamaño de los log y el % de ocupacion de las bbdd de una instacia:
dbcc sqlperf (logspace)
--->"esto tarda mucho..." Mostrar información de espacio actualizada acerca de una base de datos
EXEC sp_spaceused @updateusage = N'TRUE';
GO
Casos prácticos 3.:
Recomendaron para SQL Server 2008, con problemas de reducción del log de transacciones:
Ejecuta dos o tres veces la instrucción CHECKPOINT. Esto asegurará que todas las páginas de memoria se han escrito en el fichero de datos:
CHECKPOINT
BACKUP LOG NombreBaseDeDatos TO DISK = '<Ruta y unidad>triton1.trn'
DBCC SHRINKFILE (N'Nombre_logico_Log' , 0, TRUNCATEONLY)
GO
Casos prácticos 4.:
Comprueba qué valor muestra el campo "log_reuse_wait" de sys.databases para esa base de datos en cuestión. Si ese campo indica efectivamente que el problema es que hay transacciones activas, puedes revisarlas con el comando DBCC OPENTRAN (http://msdn.microsoft.com/es-es/library/ms182792.aspx)
alter database BBDD_Name set recovery simple
go
checkpoint
go
checkpoint
go
checkpoint
go
checkpoint
go
alter database BBDD_Name set recovery full
go
--Recomendable, para comenzar secuencia, para posterior backup del log.:
backup database BBDD_Name to disk = '<Ruta y unidad>.bak' with init
go
DBCC SHRINKFILE(pubs_log, 2)
Go
Casos prácticos 5.:
La operación de reducción está bloqueada
Es posible bloquear las operaciones de reducción mediante una transacción que se ejecuta con un nivel de aislamiento basado en el control de versiones de filas. Por ejemplo, si se está ejecutando una operación de eliminación grande con un nivel de aislamiento basado en el control de versiones de filas cuando se ejecuta una operación DBCC SHRINK DATABASE, la operación de reducción esperará a que la operación de eliminación se haya completado antes de reducir los archivos. Cuando esto sucede, las operaciones DBCC SHRINKFILE y DBCC SHRINKDATABASE imprimen un mensaje informativo (5202 en el caso de SHRINKDATABASE y 5203 para SHRINKFILE) en el registro de errores de SQL Server cada cinco minutos durante la primera hora, y cada hora sucesivamente. Por ejemplo, si el registro de errores contiene el siguiente mensaje de error:
DBCC SHRINKFILE for file ID 1 is waiting for the snapshot
transaction with timestamp 15 and other snapshot transactions linked to
timestamp 15 or with timestamps older than 109 to finish.
Esto significa que la operación de reducción está bloqueada por transacciones de instantánea que tienen marcas de tiempo anteriores a 109, que es la última transacción que ha completado la operación de reducción. También indica que las columnas transaction_sequence_num o first_snapshot_sequence_num de la vista de administración dinámica sys.dm_tran_active_snapshot_database_transactions contienen el valor 15. Si la columna transaction_sequence_num o first_snapshot_sequence_num contiene un número inferior al de la última transacción completada mediante una operación de reducción (109), la operación de reducción esperará a que las transacciones finalicen.
Para resolver el problema, puede llevar a cabo una de las tareas siguientes:
· Finalizar la transacción que está bloqueando la operación de reducción.
· Finalizar la operación de reducción. Si finaliza la operación de reducción, se conservará todo el trabajo completado.
· No hacer nada y permitir que la operación de reducción espere a que finalice la transacción que la está bloqueando.
Basado en:
TechNet, MSDN.
Apuntes y recopilaciones por: Norman M. Pardell
Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.
