Sizing the Microsoft SQL Primary Data (mdf) and Transaction Log (ldf) files

A Microsoft SQL database consists a primary data file (mdf) a secondary data file (ndf) and a transaction log file (ldf). For this article, we will concern ourselves with the mdf and ldf files.

MDF stands for Main Database File and contains all the information in a database. LDF records all the transactions and changes to the database. The ldf is critical for disaster recovery.

Setting the Initial size and Auto growth for these files, can have a significant  impact on the performance or even the ability to use the database. Which in turn impacts the use pf the PDM vault, relying on this database.

WARNING: Any changes should be done by someone knowledgeable of Microsoft SQL.

The mdf and ldf  can be set for each Microsoft SQL database, by right-clicking on the database, in Microsoft SQL Server Management Studio and selecting Properties.

Accessing Database Properties

In the Database Properties Select Files.

Database Properties Files

In this window, the current settings for the mdf and ldf are displayed.

MDF LDF Settings

The default initial size for both files are 8 MB. The initial size is not as important as Autogrowth. And even for Autogrowth, there are no suggested values. SOLIDWORKS in the past has recommended 15% for the mdf and 10% for ldf as initial settings.

Change Autogrowth

Setting Autogrowth and Maximum File Size

These settings may need tweaking if you experience poor performance. Microsoft has published an article on “Considerations for the “autogrow” and “autoshrink” settings in SQL Server“, that can be helpful in determining the Autogrowth size. Autoshrink should not be used, as this can cause Index fragmentation, which can then lead to poor performance.

The Maximum File Size for the mdf, is set to Unlimited, bby default. For a Microsoft SQL Standard implementation, the maximum mdf size is 10 GB. Few PDM databases will ever reach that size. Since the mdf contains everything you add to your database, setting a Maximum Size, will also add a ceiling to what can added in PDM. I would suggest NOT capping the Maximum File Size for the mdf. If you are running out of space on your SQL server, consider adding a larger drive or having a server that is dedicated to running SQL. A dedicated Database (SQL) and Archive sever, can provide superior performance.

By Default Maximum File Size for the ldf is capped. If the Simple Recovery Model is used, this file should not grow to quickly. The Maximum File Size can be adjusted if this cap is reached. There will be indicators in the PDM user log that indicate that the Transaction log is full and this could in turn mean that the cap has been reached. As a note these messages can also be indicative of a full drive.