Lately my server is overloaded because of daily running SQL jobs, my transaction log grows very large throughout the night and I run out of disc space. The short term solution was to shrink the log every hour, because even at every 4 hours it seemed to still fill up the whole server (as the shrink job might get blocked from running by other scripts). Is there any long term solution to this so I can go back to backing up/shrinking the transaction log once per day?
3
-
You must be doing a lot of data modifications. Depending on your needs, you can adjust the logging. For instance, if you have a a separate mechanism for checkpointing and recovery, you could use simple logging.– Gordon LinoffDec 17, 2020 at 20:40
-
Off-topic - belongs on dba. Most likely your database is using the wrong recovery model for the resources you apply to manage it. You shouldn't be shrinking the log in the first place - establish the amount of space it requires based on your recovery model and objectives and just leave it. Don't shrink– SMorDec 17, 2020 at 22:09
-
1Are you running Transaction Log backup jobs at regular intervals?– Martin CairneyDec 17, 2020 at 23:56
Add a comment
|
1 Answer
I think your problem could actually be that you only want to backup and shrink your transaction log once per day.
If you don't need Transaction Log Backups to restore to any point in time, then switch your recovery model to "Simple" and stop doing log backups altogether.
If you do need Transaction Log Backups, then you usually have a need to take more than 1 in a day. SQL Server will track every change in the Transaction Log until the Transaction Log is backed up, so maybe try scheduling these to run more often.
Before you backup and shrink the file, or whatever you're doing each day, you can run the following command to tell you what it's waiting for:
SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases;
Given that backing up the transaction log fixes your problem, that's where I'd put my money. But other things that can cause the Transaction Log to continue to grow are Mirroring/AlwaysOn replicas falling behind, long running transactions (which will affect Simple recovery model as well). The above query will help you find the problem.
Finally, having a large transaction log is not actually a problem. You shouldn't shrink the file every night, it's just going to grow again tomorrow. If log_reuse_wait = 0 then just let it be. If log_reuse_wait is not 0 then check before you clear the log what it's waiting on. Microsoft Docs has the full list of codes.
