How to purge Log file of CRM database

Over a period of time, you will notice that the size of the transaction log file has grown exponentially and it has now used up most of the free disk space available on the drive.

Transaction log keep a track of all updates being made to the database tables and is required for recovery or restore of the database. So it is important that we do not lose the transaction logs. However we can safely purge the logs for the transactions have been completed successfully.

To free up the disk space used by the transaction log you need to perform the below steps.

1. Take a backup of the transaction log.
2. Shrink the log file.

Take a backup of the transaction log.

You can either execute the following statements through the browser

You need to first create a backup device
EXEC sp_addumpdevice ‘disk’, ‘AdvWorksData’,
‘C:\SQLServerBackups\AdvWorksData.bak’;

Next backup the entire database first before you backup the log file
— Back up the full AdventureWorks database.
BACKUP DATABASE AdventureWorks TO AdvWorksData;
GO

Now that a backup has been created of the entire database, you can back up the transaction log file.

— Back up the AdventureWorks log.
BACKUP LOG AdventureWorks
TO AdvWorksLog;

Note: It is important that you take a backup of the database before you take a backup of the log file as otherwise you will receive the following error.

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

Also you need to make sure that the Recovery model of the database is set to “Full”. By default the recovery model of CRM database is Simple.

Or through the UI, backup the log file using the following steps

Right Click dataset –> Backup…

Select backup type as Full and take a complete backup of the database.

Once this backup completes. You now change the backup type to Transaction Log and in the options tab check for the following

Shrink the log file.

You can run the following statements

DBCC SHRINKFILE(mscrm_log, 2)

Mscrm_log is the logical file name of the Log file created for CRM database.
2 – is the target size specified. However if the transaction log has data more than the 2 MB it will release all the unused space to the minmum space that is required to store the log information.

Through the UI, you can follow these steps.

Right Click dataset –> Shrink –> Files…

Select the log file and choose the Release Unused space.