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)
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