Problema:

Uno de los aspectos más importantes de una base es el log de transacciones. El log de transacciones se utiliza para escribir todas las acciones sobre los datos antes de ser confirmadas en los archivos de la base. En algunas circunstancias el log de transacciones puede llegar a ocupar un tamaño considerablemente grande y no saber cuál transacción es la causa o cuanto espacio es utilizado puede llegar a ser un problema. Entonces, ¿cómo determinar cuánto del registro de transacciones se está utilizando y qué partes se están utilizando?

Solución:

En la mayoría de las bases de datos, el log transaccional es un solo archivo LDF, pero dentro de este se encuentran archivos virtuales de registro como se muestra a continuación:

En cada archivo virtual se escriben los registros de las transacciones y cuando estas se confirman, se produce un punto de control y el espacio del ocupado se libera para poder volver a ser utilizado. Aunque esto depende del modelo de recuperación de la base, o se utlilizan replicas, o algún proceso de back up. Cuando no quedan más archivos virtuales SQL Server hace crecer el log de transacciones basado en la configuración de la misma para acomodarlos en el espacio obtenido.

El uso del archivo y de los registros virtuales depende principalmente de cómo es utilizada la base de datos. Si estas publicando datos de una base o si la base esta en modo de recuperación completo o Bulk-Logged, es probable que se vea afectada por los “loops back” de volver al principio del archivo o por la amplicacion del archivo de transacciones.

DBCC SQLPERF (logspace)

Un comando extremadamente útil para entender como está siendo utilizado el registro de transacciones es DBCC SQLPERF(logspace). Este comando muestra detalles sobre el tamaño actual de todas las transacciones de base de los registros, así como el porcentaje actualmente en uso. Ejecutando este comando en forma periódica te dará una buena idea de cómo los registros de transacciones están siendo utilizados y también te dará una idea de lo grande que realmente debe ser.
Es bastante frecuente que la gente que utiliza SQL Server que se pregunte como es que funciona, pero no hay una respuesta exacta ya que depende de un montón de criterios como:

– EL modo de recuperación de la base
– El tamaño de las transacciones
– Cuan grande sean las tablas y de cuantos índices disponga
– Cuan frecuente sean los back ups de los logs.
– etc…

Para ejecutar este comando:

 DBCC SQLPERF(logspace)

Salida:

Desde aquí podemos ver el tamaño de los registros de transacciones, así como la cantidad de espacio que utiliza. El espacio del registro actual utilizado te dirá el tamaño del registro de transacciones utilizado. Si este porcentaje es alto y el tamaño del registro es muy grande es probable que sea debido a uno de los puntos enumerados anteriormente.

DBCC LOGINFO

El siguiente comando para tener en cuenta es DBCC LOGINFO. Esto le dará información sobre sus registros virtuales dentro de su registro de transacciones. Lo principal a mirar es la columna Status. Dado que estos archivos se escriben secuencialmente y luego en bucle desde el principio, ver el valor 2. Las partes del registro que están en uso y las que no están en estado de uso tendrán Status = 0. Otra cosa a tener en cuenta es la columna FSeqNo. Este es el número de secuencia de registro virtual y el último es el último registro (el mayor) Si se ejecuta repetidas veces, se podrá ver que estos números cambian constantemente.

Para ejecutar este comando:

DBCC LOGINFO

Salida:

Al ejecutar alguno de los siguientes comandos se podrá apreciar el cambio

 BACKUP LOG DBUtil WITH NO_LOG

or

BACKUP LOG DBUtil TO DISK = C:\Backup\DBUtil.trn

Se ven los registros que tenían Status = 2 han cambiado a cero.

Una cosa a tener en cuenta es que si ejecutas un BACKUP LOG…WITH NO_LOG necesitaras ejecutar otro back up full, sino SQL Server reutilizara el espacio en el log de transacciones porque no hay forma de restaurar la copia de seguridad del registro de transacciones a no ser que sea completo.

DBCC OPENTRAN

Otro comando para mirar es DBCC OPENTRAN. Que muestra las transacciones abiertas que no han sido confirmadas, ya sean activas o que por alguna razón quedaron huérfanas, proporcionando información adicional sobre porque el registro de transacciones es tan grande o porque no es posible que reduzca de tamaño. También figuran las transacciones que aún no se han publicado en la réplica.

Para ejecutar este comando:

 DBCC OPENTRAN

Salida:

Bueno, ahora tienes una idea de cuánto de tu log de transacciones está siendo utilizado y por donde se puede empezar a mirar para tomar decisiones y cuán grande deben de ser estos.

Fuente: MSSqlTips.com