- Solutions
-
- File Server: Ransomware Protection
- File Server: File Copy
- File Server: Audit File Access
- File Server: Storage growth reporting
- Licensing/Pricing
- Contact
Handling Large Database Transaction Log Files
Everytime a insert, update or delete happens on a database table, the activity is written to the database's Transaction Log File. And although we'll be talking specifically about Microsoft SQL Server, this is true for most database systems.
Why is this happening now?
Sometimes there will be a change to the product that will require a lot of background database activity. Some recent examples:
- The MonitorStatusHistory table has been found to be very large in many installations, yet the data is rarely if ever used. We changed the default data age from 90 days down to 30 days to free database space. This will cause large amounts of deletes to happen, which affects the Transaction Log size.
- Similar to the above, the default for the ErrorHistory2 table was reduced.
- For PA File Sight specifically, in version 9.4 there was a major table format change, so all the old data will slowly get rewritten to the new table in the background.
SIMPLE vs FULL
One setting that can have a very large impact on the Transaction Log file size is the Recovery Model. This page does a good job discussing the differences:
Briefly, the SIMPLE recovery model will reuse space in the Transaction Log file, while the FULL recovery model requires the Transaction Log to be backed up before space can be reused. However, the FULL model allows point-in time recovery of the database which the SIMPLE model does not. FULL is the default setting.
Truncating
When the Transaction Log becomes very large the inclination is to truncate the log file. This can't be done if the Recovery Model is FULL (unless the log is first backed up). So many people will run the following commands:
ALTER DATABASE {your database name here} SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE ({your database name here}_log, 1)
GO
ALTER DATABASE {your database name here} SET RECOVERY FULL
Further Growth
It is also recommended to take a look at how large SQL Server will let your Transaction Log grow before (in SIMPLE Recovery Mode) it starts to re-use the log space. This can be seen in the database settings as shown below:
