Safely Shrink the Size of the Infor CRM SQL Log File

In recent weeks, I have talked about SQL recovery models [here] and how to manage SQL logs [here], both in reference to using a SQL database in Infor CRM. Today I am going to talk about what to do if you realize your SQL logs have gotten out of control and are way too big (likely because you were using the Full recovery model, and backups weren’t running regularly).

You can just choose the shrink the log files using the SQL Management Console.  Right click on the database, and go Tasks > Shrink > Files.  Choose to shrink the log file, then either leave as is, or change the bottom radio button to Reorganize to choose how much you want to shrink the log.

This is not a good way to proceed, however. It will shrink the log file, but it does it by deleting entries. So if you do this command in isolation, you will render that log file useless for recoveries, and will need to recover to the last good backup if the database fails.

In order to SAFELY shrink the database, you will need to first make sure everyone is out of the data, or just make Infor CRM temporarily inaccessible in some way.  (Temporarily stopping the services, or just turning off the web client site work well.)  Then run a full backup, this will give you a current backup, and clear all the the transactions out of the log file.

Now, clearing the transactions of the log file this way gives your log file a lot of space to add new transactions, but does not actually shrink the file. It is just sitting there as big as ever, with plenty of blank space. Now is the time to shrink the actual file.  You can use the SQL console as above, or just use the following SQL command

ALTER DATABASE saleslogix_eval
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (saleslogix_eval_log, 1)
GO
ALTER DATABASE databasename
SET RECOVERY FULL

where saleslogix_eval is the name of the database, and 1 is the file size in MB you wish to shrink it to.  (You will need to substitute your own values for the ones I have used here.) Once you have the log file down to the size you want, you can enable Infor CRM again. Oh, and maybe fix whatever was wrong with the backups and/or recovery model in the first place so you don’t have to do this again.