New in the Community? Get started here

Schneider Electric Exchange Community

Discuss and solve problems in energy management and automation. Join conversations and share insights on products and solutions. Co-innovate and collaborate with a global network of peers.

Register Now
Knowledge Base
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Discover the Exchange Community Top members of August

Cannot Shrink SQL Transaction Log

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.

  1. Open SQL Server Management Studio. 
  2. Click on the New Query button.
  3. 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

Tags (1)
Labels (1)
No ratings