Warning
Potential for Data Loss: The steps detailed in the resolution of this article may result in a loss of critical data if not performed properly. Before beginning these steps, make sure all important data is backed up in the event of data loss. If you are unsure or unfamiliar with any complex steps detailed in this article, please contact Product Support for assistance.
Issue
After switching the recovery model from Full to simple, the shrink task fails to shrink the transaction log file.
Product Line
Continuum
Environment
- Continuum
- Microsoft SQL Server
Cause
A transaction log cannot be shrunken while there is a process actively accessing the log file.
Resolution
Use the log_reuse_wait_desc function in a SQL query to help you identify the process that is preventing the shrinking of the transaction log.
- Open SQL Server Management Studio.
- Click on the New Query button.
- In the query window type the following SQL statement:
SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'ContinuumDB'
Note: The name in single quotes should be the name of the Continuum Database. In some cases, it may have been changed to something other than "ContinuumDb."
This query will return one or more of the following keywords. Each of the following keywords indicates a transaction log process that is taking place.
Here is a list of keywords that could be returned.
- NOTHING
- CHECKPOINT
- LOG_BACKUP
- ACTIVE_BACKUP_OR_RESTORE
- ACTIVE_TRANSACTION
- DATABASE_MIRRORING
- REPLICATION
- DATABASE_SNAPSHOT_CREATION
- LOG_SCAN
- OTHER_TRANSIENT
For example, if this query were to return REPLICATION, this would indicate that the replication process has control of the transaction log. In that case you might investigate the replication configuration or perhaps even turn replication off temporarily to successfully shrink the log.
You can find a detailed description for what each keyword indicates by searching the following website Eight Reasons Transaction Log Files Keep Growing
