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’,
Next backup the entire database first before you backup the log file
– Back up the full AdventureWorks database.
BACKUP DATABASE AdventureWorks TO AdvWorksData;
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
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…
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.