There could be several reasons which leads to excessive growth of the transaction log,
Some of them may be as follows:
We can investigate the cause of same by looking at the value of column log_reuse_wait_desc from sys.databases.
We can simply run the below code:
SELECT name , recovery_model_desc , log_reuse_wait_desc FROM sys.databases WHERE name = 'Database_name'
Database_name= Please enter the name of your database.
The value of the log_reuse_wait_desc column will show the current reason why log space cannot be reused.
It may be possible that more than one thing is preventing log reuse. The sys.databases view will only show one of the reasons. It is therefore possible to resolve one problem, query sys.databases again and see the value of log_reuse_wait_desc for different reason.
The reasons could be as follows:
NOTHING :It means that currently there is no problem with transaction Log.
CHECKPOINT :his value means that a checkpoint hasn't occurred since the last time log truncation occurred.
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
AVAILABILITY_REPLICA
Please refer MSDN site for detailed information for above reasons:
http://msdn.microsoft.com/en-in/library/ms178534.aspx
Some of them may be as follows:
- operating a database in FULL recovery model, without taking log backups
- performing index maintenance like index rebuild and index reorganize
- long-running or uncommitted transactions that prevent space in the transaction log from being reused.
We can investigate the cause of same by looking at the value of column log_reuse_wait_desc from sys.databases.
We can simply run the below code:
SELECT name , recovery_model_desc , log_reuse_wait_desc FROM sys.databases WHERE name = 'Database_name'
Database_name= Please enter the name of your database.
The value of the log_reuse_wait_desc column will show the current reason why log space cannot be reused.
It may be possible that more than one thing is preventing log reuse. The sys.databases view will only show one of the reasons. It is therefore possible to resolve one problem, query sys.databases again and see the value of log_reuse_wait_desc for different reason.
The reasons could be as follows:
NOTHING :It means that currently there is no problem with transaction Log.
CHECKPOINT :his value means that a checkpoint hasn't occurred since the last time log truncation occurred.
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
AVAILABILITY_REPLICA
Please refer MSDN site for detailed information for above reasons:
http://msdn.microsoft.com/en-in/library/ms178534.aspx
No comments:
Post a Comment