Problem: Transaction Log Growing Continuously

System: ERP (XYZ)

Solution: First take all types of Backups (Full Db, Differential, master, model and Transaction log),

Put the DB recovery mode to SIMPLE/FULL


Query: ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE/FULL

To Know the Reuse_wait_reason of each DB

Query:  select <name>, recovery_model_desc, log_reuse_wait_desc from sys.databases

In our System the Log file XYZLOG1 uconsumes more memory because of no taken transaction backup and Recovery mode.

It leads to consume more mempry than the actual data required, like XYZ datafiles consumes 160Gb to store data where as XYZlog1 consumes 200GB to store Transaction log.

For this we need to take backup of Transaction Log and set recovery mode to simple/full and Shrink the XYZLOG1 file which consumes more memory.

⦁    Query: DBCC SHRINKFILE(XYZLOG1)


–>DB02-Space Overview

/wp-content/uploads/2014/09/1_546344.png

Here Allocated Log Size showing 175GB.

Shrink the Log File

Query: DBCC SHRINKFILE(XYZLOG1)


/wp-content/uploads/2014/09/2_546342.png

Set Recovery Mode:

Back up your database!

Launch SQL Server Management Studio.

Open up a query window associated with the database with the large transaction log. (Right-click on the DB and choose new query.)

Get the logical name of the transaction log file. (Right-click on the DB, select Properties, then in the Files screen, grab the Logical Name… probably ends in something like _Log.)

Query: ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE/FULL

/wp-content/uploads/2014/09/1_546344.png


Check Recovery Model and Log Reuse Wait:


Query: select name, recovery_model_desc, log_reuse_wait_desc from sys.databases

/wp-content/uploads/2014/09/3_546112.png

Finally the XYZ space at SAP Screen DB02-Space Overview after Shrinking XYZLOG1


/wp-content/uploads/2014/09/3_546112.png

Regards,

Chandrababu Katta

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Matt Fraser

    Hi Chandrababu,

    You might mention in your document that the desired end-state is an appropriately sized transaction log that is regularly (multiple times per day for production systems, and at least daily for dev/test systems) backed up, and with the database in full recovery mode. The measures you describe here shouldn’t be necessary unless your transaction log backups have been failing. Also, we do want the transaction log to be large enough that it isn’t forced to autogrow during business operations, but yes, it shouldn’t be as large as or larger than the actual database. Probably a size about 10% of the database size is generally appropriate, although for a very busy database it might need to be larger (and/or the backups more frequent), and for something very static it could be smaller.

    Regards,

    Matt

    (0) 
    1. Chandrababu Katta Post author

      Hi Matt,

         Yes you’re right and i focused on the failed transaction log backup and continuous growth of transaction log which is going to be larger than actual DB Size.

      Regards,

      Chandrababu

      (0) 

Leave a Reply