MSSQL Transaction Logs Full

MSSQL Transaction Logs Full

Steps to clear the Transaction logs.

1. Shutdown opmanager service.

2. Change the Recovery Model to Simple by following the steps below:

Go to SQL server-->OpManagerDB properties-->Options-->Recovery Model-->Simple. (You can replace it with full mode later)

3. Connect to MSSQL Server-->OpManagerDB. Execute the following query

1) DBCC SHRINKDATABASE (database name , target percent)

for eg

DBCC SHRINKDATABASE (opmanagerdb,20)

2) alter database <DATABASENAME > MODIFY FILE (Name = "<DATABASENAME>_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 shrink. So run this query and then follow the above steps

BACKUP LOG OpManagerDB WITH TRUNCATE_ONLY

4. Restart OpManager Service.