SQL Server backup log – hasn’t been run in a long time, what could go wrong?

The question:

SQL Agent was accidentally not enabled on a SQL Server after a restart, and no jobs were run for 3 weeks, including no backups (full or diff or log).
SQL Agent is enabled now. The full backup of the db has been run. Now the log backup is running and it’s already been running for more than an hour (normally it takes seconds right after a full backup has been run).

Any potential issues to consider? The log file had gotten really large, like 200 gb.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Likely the reason your Transaction Log backup is taking much longer to run than usual is because it has a lot more data to backup – all of the transactions that haven’t been backed up yet since the last time a Transaction Log backup was ran three weeks ago.

Please see my answer to a related question that explains a little bit of how the Transaction Log works and what causes the log file to grow.

But in short, independent of when your Full backup runs, your Transaction Log backups are dependent on the unbacked up transactions in the log file, which accumulate until a Transaction Log backup is taken. Once a Transaction Log backup is taken, the transactions in the log file are marked for re-use which allows them to be overwritten by new transactions. This can only happen as a result of a Transaction Log backup occuring, a Full backup doesn’t backup the log file (it only backs up the database’s data file).

This explains why your log file grew so much over the last few weeks. New transactions will continue to append to the log file until it’s backed up and the old transactions are marked for re-use such that they can be overwritten instead.

Q:Any potential issues to consider?

A: No immediate issues that I can think of. The only issue that can occur is if you have an event that requires point in time recovery, you wouldn’t have any Transaction Log backups to get you to a specific restore point in time (within the last three weeks). The best you’d be able to do is the timestamp of the Full backup you took already, and once your current Transaction Log backup finishes, that’ll be the most granular point in time you’ll be able to recover to until your next Transaction Log backup runs (which should complete a lot quicker since it won’t have three weeks of transactions to backup).

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment