Dear experts,
I have an SQL database which has a log file.
This log file will be saved in a different hard drive than the actual data.
Now today we found out the LOG hard drive was full. After Ignition had trouble getting to the SQL database.
I have found out in the properties if you set the database recovery mode to “Simple” you can shrink the database log file manually. Which I did and this worked.
Now in the properties I also found an option called “Autogrowth Max file size” which was set to 2.000.000MB (2.000 GB) where my harddrive was only 49.9 GB.
I changed this to 40.000 MB (40 GB). My question is what will happen if we reach this 40.000 MB.
Will Igntion have trouble again or will it auto delete the logging at the beginning and keep logging?
Or is there another way to make sure logging files don’t reach their max size?
Ignition won’t do anything to that log. That’s entirely a feature of Microsoft SQL Server.
After the log reaches the maximum size, I would expect it to start to rotate or truncate, but that’s an MSSQL question, not an Ignition question.
2 Likes
Hello Griffith,
Thank you for your answer. I have another question though.
This setup was made by another company so I was wondering if the log file should be there?
If not maybe I can stop the log completely I don’t think any1 looks at it anyway…
If the log file for te SQL database needs to be there is it normal for it to reach 50 GB?
Keep in mind for me this looks like a log file only for SQL transactions and not to save data.
Seems odd to me it would reach this amount of space.
The SQL Server transaction log is much more complicated than just logging transactions. If you are using the Standard (or better) edition of SQL Server, a maintenance plan can be created that will automatically make backups and shrink the log file. If you are using the Express version, the maintenance plan can be created in a stored procedure then called from either an Ignition Transaction or gateway timer script. The following link has much more information: https://www.sqlshack.com/beginners-guide-sql-server-transaction-logs/
1 Like
Hello,
Thanks for the information it seems like this is exactly what describes my problem.
It seems to me that there are three different methodes to adjust the autogrowing.
-> Simple recovery mode automatic log trunctuation
-> Bulk logged Manual log trunctuation
-> Full recovery mode manual log trunctuation.
What do you experts suggest we use? It says we have risk of data loss in case of recovery?
Doesn’t that also mean we should backup the database on regular time?
Also when we manually trunctuate the data won’t we also have risk of data loss?
That part wasn’t as clear to me.
1 Like
A lot depends on what version of SQL Server you are using and your expectations for recovery in the event of a database related failure. Here is a bunch more free resources that might help you out.
https://www.brentozar.com/archive/category/backup-and-recovery/
1 Like
