How to reduce the transactional log size (MSSQL database)?
OpManager provides extensive workflow operations:
You will find a print to the following effect in the logs: Caused by: java.sql.SQLException: The transaction log for database 'OpManagerDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases..
To check the used % : DBCC SQLPERF(LOGSPACE)
Steps to clear the logs:
- Shutdown OpManager server.
- Change the Recovery Model to Simple by following the steps below:
- Go to SQL server-->OpManagerDB properties-->Options-->Recovery Model-->Simple.
- Connect to MSSQL Server-->OpManagerDB. Execute the following query
BACKUP LOG OpManagerDB WITH TRUNCATE_ONLY
- DBCC SHRINKDATABASE (database name , target percent) for eg DBCC SHRINKDATABASE (opmanagerdb,20)
- alter database MODIFY FILE (Name = "_log",MAXSIZE=1 GB) - for eg alter database opmanagerdb MODIFY FILE (Name = "opmanagerdb_log",MAXSIZE=1 GB) (If the transaction log is too huge without any backup, it can not be shrunk. So run this query and then follow the above steps.
- Restart OpManager Server.
Ensure you upgrade to the latest build.
If you still get transaction logs filled up issue, check if you have free disk space where the database and transaction log files are saved.
If the drives are filled up, move them to different drive.