How to address transaction log space issue.

Products

• CA Service Management - Service Desk Manager

Issue/Introduction

After configuring CA Service management, User are not able to access application and encounter "Delay Server Response". 

Environment

CA Service Desk Manager 17.4.X

Cause

7/16 15:54:30.43 SERVER_NAME bpvirtdb_srvr            784 ERROR        vdbmisc.c              688 Miscellaneous Database error occured :[Microsoft SQL Server Native Client 11.0] [ SQL Code=9002 SQL State=42000] The transaction log for database 'mdb' is full due to 'LOG_BACKUP'.

Resolution

Database administrator need to be engaged to address the transaction log space constraint issue. 

Command to be executed : 

USE MDB

ALTER DATABASE MDB SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(mdb_log, 1)
ALTER DATABASE MDB SET RECOVERY FULL WITH NO_WAIT

Truncate the transaction log
Use the following steps to truncate the transaction log file in SQL Server Management Studio (SQL Server 2008 and later). Keep in mind that following these steps might result in data loss. You should not need to manually truncate the log because regular log backups should automatically perform this task.

Note: You need the sysadmin fixed server role or the db_owner fixed database role to truncate the log.
1.Right-click the database and select Properties -> Options.
2.Set the recovery model to Simple and exit the menu.
3.Right-click the database again and select Tasks -> Shrink -> Files.
4.Change the type to Log.
5.Under Shrink action, select Reorganize pages before releasing unused space and click OK.
When the process completes, switch the recovery model back to Full or Bulk-Logged and take a full database backup.

Understand shrinking the log
If you need to recover disk space from the transaction log file, consider shrinking the log file. Shrinking recovers space by moving data from the end of the file to unoccupied space at the front of the file. After the process creates enough space at the end of the file, it can be deallocated and returned to the file system. Shrinking logs helps after you perform an action that creates a large number of logs. You can only shrink the log if there is free space on the log file.

Shrink the transaction log
1.Use the following steps to truncate the transaction log file:
Note: You need the sysadmin fixed server role or the db_owner fixed database role to shrink the log.
2.Right-click the database and select Tasks -> Shrink -> Files.
3.Change the type to Log.
4.Under Shrink action, select Release unused space and click OK.