LDF not dumping after backup job concludes
-
It is my understanding that after the SQL backup process runs on a database, the LDF should dump (since this is a transactional db). However, this does not seem to be the case. The LDF never dumps and continues to grow despite the scheduled backups that occur weekly. In this situation, I need the LDF to dump because it will bog down the performance of the application using it. In this case, the app is a Microsoft financial product called Navision. Anyone have any ideas what is not being triggered or why the backup process is not dumping the LDF? One of the running theories I have going is that this particular db uses a different collation precedence than the rest of the db's on the server. Would this be part of the problem? Any guidance would be appreciated.
-
What is your current backup strategy? If your database is in Full Recovery Mode, but you are only taking Full Backups, the transaction log will not be truncated. This only occurs as part of a transaction log. Also, you may have long running transactions, and this can also cause your transaction logs not to shrink.
-
The db is indeed running in Full recovery mode. Thanks for pointing me in that direction. To have this ldf dumped automatically which mode is preferrable? Although, changing the db's recovery mode midstream might cause other issues. Or can using the truncate log file clauses be used to accomplish the same feat in the 'backup database' statement...? (which is what we use to create our .BAK file)
-
When you say "long running transactions" do you mean transactions that may still be running when the backup job is kicked off?
-
You could consider those long running, but there also could be updates or loads that take several minutes to hours to complete as well depending on what type of database we are talking about (OLTP v Data Warehouse).
If recovery to a point in time is not required, you could put your database in simple recovery mode. This will cause the transaction log to truncate when transactions are checkpointed (written to the database). If your database is in simple recovery mode, only full backups and differential backups can be completed.
If you need recovery to a point in time, then you need to schedule periodic transaction log backups during the time between full backups.
Hope this helps.
-
Excellent -- thank you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply