Transaction Log Growing Continuously..?

[chandrababu_katta]

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

Here Allocated Log Size showing 175GB.

Shrink the Log File

Query: DBCC SHRINKFILE(XYZLOG1)

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

Check Recovery Model and Log Reuse Wait:

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

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

Regards,

Chandrababu Katta