"Make Room Make Room" . . . Microsoft Dynamics GP Runs Out of Space

Harry Harrison’s classic science fiction novel “Make Room Make Room” deals with unchecked population growth and decreasing resources, not unlike a situation with a client’s SQL server recently.  The server had separate drives for the data (mdf), log (ldf) and backups (bak) files, however, the log files kept growing until the drive ran out of space.  At that point, errors started appearing in the event logs and within Microsoft Dynamics GP.

In this case, the Dynamics GP and company databases had the Recovery Model set to Simple, so large log files should not have been a problem.  However, at some point, the Audit Trail module was enabled and when the Audit databases were created for each company, they had a default setting for the Recovery Model as Full.  Because the company databases were set at Simple, the original maintenance plans only created nightly full backups and transaction log backups were not included in the plans.

The data (mdf) files store the data.  The log (ldf) files store active transactions until the transactions are written to the data file after which the transactions are labeled as inactive.  In the Simple Recovery Model, the log file is then truncated and the inactive transactions can be overwritten. However, in the Full or Bulk-logged Recovery Model, the log file is not truncated until a transaction log backup is made. Without transaction log backups, the log files do not overwrite the inactive transactions and the files continue to grow, limited only by the size of the drive.

Once the problem was discovered, the audit databases were backed up, the Recovery Model set to Simple and Shrink was run on the database.  The log files reset to a minimum size and future space issues for the log drive were eliminated.

You can check the Recovery Model for each database by right clicking the database, select Properties, and select the Options page.  Change the Recovery Model by selecting the appropriate choice from the dropdown (Full, Bulk-logged or Simple)

To check all databases at once, run the script
SELECT name, recovery_model_desc from sys.databases
and see the results:

In any case, it is a good idea to check the size of the data, log and backup files on a regular basis and the remaining space on the drives.  If the log file is ever larger than the data file, further review with your Dynamics GP consultant is necessary.

If you have any specific Microsoft Dynamics GP needs regarding this function or others, RSM consultants are certified in a broad range of Dynamics GP areas.  Our services for Microsoft Dynamics ERP are offered from 16 different offices throughout the country.  Contact our professional for more information at 800.274.3978.

By: Claude Frymark - New York Metro Microsoft Dynamics partner