How to Prevent: MS SQL Server bogs down over time


MSSQL Server databases used with FactorySQL groups which overwrite the same record repeatedly (for example, bi-directional items) should be placed into the partial backup mode. If this is not done then the transaction log grows huge and slows down database response time. The partial backup mode can be set either from the MSSQL console command prompt or from menu items in Enterprise Manager.

All queries are written to the transaction log. The the database server executes the query from the transaction log. In the full backup mode, records in the transaction log are never overwritten. In the partial backup mode once the query is read by the database engine and executed that record in the transaction log is freed and overwritten later. Therefore the transaction log size stays relatively constant and queries remain fast.

This is very important to know about MSSQL Server.



The “partial backup mode” is actually referred to as “simple recovery model”.

Here is how you set it.

  1. From Enterprise manager drill down to the database of concern (which will have a lot of realtime values) and right click it.

  2. Go the the “Options Tab” and the “Recovery Model” drop down box. Change this from the default “Full” to “Simple”.

  3. Go to the “Transaction File” tab and limit the transaction file size to some reasonable size such as the default 2mb.

This would be applicable to a database which has realtime values being updated since there is no value in recovering the history of a field which is repeatedly being updated 1x per second.

But if you are logging tracking information (such as part history) by inserting records, this would best be done in a separate database which is left with the default “Full” recovery model.



Is there a way to do this with MySQL?



As far as I know this isn’t really an issue with mysql. Why do you ask, are you encountering some sort of large log file growth?



MSSQL pulls a lot of memory. There is a significant difference between its usage and MySqL.