3

Need an advice from SQL Server Experts. I'm just a developer, not a DBA, so pardon my ignorance.

My environment consists of 2 equal instances (Node1 and Node2) of SQL Server 2008 R2 x64 (10.50.4339.0). I have 2 databases: DB1 and DB2 with Full recovery model. Both databases participate in replication between each other within single SQL instance and also both of them get mirrored from Node1 to Node2. These are not production databases and I did take full DB backups before doing any changes.

However, I have ended up in trouble: the log file is not growing for both DB1 and DB2 at Principal and the execution of:

select log_reuse_wait_desc, name, * from sys.databases where name='DB1' or name='DB2'

shows that both DB1 and DB2 have `log_reuse_wait_desc='LOG_BACKUP' even though I did run log backup manually.

Here is the sequence of events which caused the current state:

  1. I needed to update DB1 and DB2 with year-worth set of changes which involved running many structure and data change scripts. Some of the data scripts were quite heavy, truncating tables and containing 70K+ INSERT calls. So, I've started applying changes at Principal (Node1) one by one in little chunks.
  2. Node1 already had limited disk space available at partition with DB1 and DB2 data and log files.
  3. When applying changes several times I've received log file full... message. So I've stopped and shrunk the log file of both DB1 and DB2 to 0Mb to free up some disk space to be able to finish the data/structure update task.
  4. The log file of both DBs did get shrunk to minimal allowed however after I have continued with data changes I've got the following message:

Msg 9002, Level 17, State 2, Line 1 The transaction log for database 'DB2' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Sometime during running of data/structure change scripts the free disk space at relevant partition dropped to a few KBs and SQL Server sent out warning emails.

I did try to research how to get log_reuse_wait_desc column to show NOTHING. However, the suggested solutions prompted putting DB1 and DB2 into simple recovery mode or detach/re-attach them, something I cannot do because of the replication and mirroring.

I have also followed the advice from Paul S. Randal and have run log backup manually:

 BACKUP LOG DB2 TO DISK = 'z:\DB2.TRN'

which completed successfully, however the log_reuse_wait_desc still shows LOG_BACKUP and the log file is not growing.

DBCC LOGINFO Output:

DB1: 421 records like:

dbcc loginfo for DB1

DB2: 381 records like:

dbcc loginfo for DB2

Initial loginfo records for DB2: https://i.stack.imgur.com/TTsVu.png

Note, the Field column for DB1 contains values 2 and 4, while for DB2 Field column contains only value 2. The file size and offset fields looks scary: 54GB+? I may not have so much free space. Currently there is 26GB of free space on the drive and it's not decreasing. So seems SQL Server has hung.

Is there any way to fix the current DB1 and DB2 without disabling replication/mirroring followed by a restore from backup?

Any help is greatly appreciated.

  • What does DBCC LOGINFO return when ran within DB1 and DB2? Are you seeing a lot of records with a Status of 2? – John Eisbrener Sep 19 '17 at 15:38
  • @JohnEisbrener yes, I've run that command before. The result is just as you say: a lot of records with status=2. Hold on, I may be wrong. Field=2 but status=0. Let me add it to the question. – andrews Sep 19 '17 at 15:50
  • Is your mirroring and replication caught up on Node2? – SqlWorldWide Sep 19 '17 at 16:00
  • It was the initial records being returned by DBCC LOGINFO I was more concerned about. I've posted an answer to try and better explain what I suspect may be occurring. – John Eisbrener Sep 19 '17 at 16:05
  • @JohnEisbrener check the DBCC LOGINFO output. @SqlWorldWide How would I check that? In sys.dm_exec_requests there is one suspended DELETE request with the following text: WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue), TIMEOUT 5000 – andrews Sep 19 '17 at 16:05
2

I wonder if your operations generated some gaps in the transaction log which may be preventing the virtual log file from looping back around to the front of the physical log file. Conceptually this is what should be happening within your database when the end of the physical file is reached:

enter image description here

If DBCC LOGINFO returns records indicating the VLFs at the beginning of the physical log file have a status of 2, these VLFs need to be cleared, which in Full Recovery mode, means a TLog backup must be taken. I believe this situation can also cause the log_reuse_wait_desc column to show LOG BACKUP as well. To fix this, you must force the logical header back toward the beginning of the physical file to clear out these status 2 VLFs (and hopefully get your log_reuse_wait_desc column back to showing NOTHING). To do this, perform the following steps (which may need to done on both DB1 and DB2 databases):

  1. Perform a Log Backup (e.g. BACKUP LOG [DBNAME]...) against the database
  2. Perform a DBCC SHRINKFILE operation on the transaction log file specifying 1 MB as the target size
  3. Perform another Log Backup against the database
  4. Perform another DBCC SHRINKFILE operation on the transaction log file specifying 1 MB as the target size
  5. Manually increase the size of the log file, incrementally per Kimberly Tripp's article, to grow the TLog back to an appropriate size.

So what's happening here?

  1. The first Log Backup simply generates a backup of the completed transactions that have been flushed to disk and deletes inactive virtual log files from the logical transaction log, freeing space for reuse up to the logical log header.
  2. The first DBCC Shrinkfile operation shrinks the physical TLOG down to where the current logical log header is located, freeing as much space as possible.
  3. The second Log Backup operation is now able to loop back around within the logical log and backup/clear any residual inactive VLFs located before the logical log header. These were probably never flushed in the past for whatever reason, but should be cleared at this point. This step is critical to fixing your issue and sometimes this backup can take a bit of time depending on how much data needs to be written down to disk.
  4. The second DBCC SHRINKFILE operation moves the logical log header to the front of the physical log file, eliminates excessive VLFs that are likely there now, and reduces the TLOG file back down to a very small size, in this case 1MB. It is imperative to manually grow the file in step 5 so you don't run into VLF issues again down the road
  5. Manually increasing the size of the tlog will allow you to control the size and number of VLFs within your physical log file. This is an opportune time to configure this properly, so take advantage.

One final note on the process. If your tlog is highly active, you may need to perform this a few times or during a window of reduced activity. High activity may result in another errant auto-growth event occurring before you execute the second log backup. This basically turns what you thought was step 3 back into step 1. Generally Steps 1 and 2 go by quickly, and step 3 takes the longest to complete. Make sure to follow-through with steps 4 and 5.

  • Thank you very much for the answer, +1. Here is the view of initial loginfo records for DB2: i.stack.imgur.com/TTsVu.png . Give me some time to follow your suggested steps and I will get back with the result. – andrews Sep 19 '17 at 16:11
  • @andrews Those gaps of status 2 can happen anywhere within the file. It generally occurs toward the beginning, but look through and if you see a few islands of status 2 records, you may be running into this situation. Worst case, this solution won't negatively affect your databases, and may fix the problem just the same. – John Eisbrener Sep 19 '17 at 16:14
  • John, you are correct! There are many status=2 log records in DB1 and only 1 such record for DB2. Question: shall I run SHRINKFILE with TRUNCATEONLY or without? Shall I run it for all log files attached to DBs (I think some DBs have 2 log files)? – andrews Sep 19 '17 at 16:19
  • 1
    John, running these 2 commands: BACKUP LOG DB2 TO DISK = 'z:\DB21.TRN'; DBCC SHRINKFILE (DB2_Log, 1) first has put DB2 log_reuse_wait_desc to DATABASE MIRRORING (I didn't touch DB1 yet intentionally) and then after some time DB1 reuse has become NOTHING while DB2 has fallen back to LOG_BACKUP. I let it run some time more and now both DBs have log_reuse_wait_desc=NOTHING. Thank you very much! You have saved me a lot of time! – andrews Sep 19 '17 at 16:45
  • Glad to help. :) – John Eisbrener Sep 19 '17 at 18:18
1

Your situation is the normal one, and you should do nothing to "fix" it.

Here is Paul Randale article Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?

The key moment to understand id this one:

If you have a transaction log that is not growing, and you’re taking regular log backups, but the log_reuse_wait_desc remains LOG_BACKUP, this is because zero VLFs were cleared when the previous log backup was performed.

How can that happen?

Imagine a database where there’s very little insert/update/delete/DDL activity, so in between your regular log backups there are only a few log records generated, and they’re all in the same VLF. The next log backup runs, backing up those few log records, but it can’t clear the current VLF, so can’t clear log_reuse_wait_desc. As soon as there are enough changes in the database that the current VLF fills up and the next VLF is activated, the next log backup should be able to clear the previous VLF and then the log_reuse_wait_desc will revert to NOTHING. Until the next log backup occurs and isn’t able to clear the current VLF, in which case it will go back to LOG_BACKUP again.

So LOG_BACKUP really means “either you need to take a log backup, or the log records that were backed up were all in the current VLF and so it could not be cleared.”

Back to your situation and your pictures.

It's not shown for the first database, but for the second you showed the right rows: there are 381 VLFs in that log and only ONE of these contains active part of the log. That VLF is 57Mb.

So when you performed your log backup, there were less than 57Mb of log rows to backup. All that rows were from that last VLF (FSeqNo = 97737), 0 VLF were cleared.

But this is not a problem. Once you reach the next VLF and do log backup, log_reuse_wait_desc will change.

  • thanks for the info, +1 for extra explanation. But I would disagree with you that I had "to do nothing" to fix it. Both DBs were not used for a couple of hours and the issue didn't get resolved until I have followed the steps suggested by John. – andrews Sep 19 '17 at 19:28
  • @andrews Please explain, did you get the 9002 error and at the same time DB2 log was in the same state as on your picture? I cannot understand at what time that picture was made. – sepupic Sep 19 '17 at 20:04
  • the picture was made several hours after the 9002 error first appeared when I realized I would not be able to solve this issue by myself. Just before posting this question I ran the BACKUP LOG... for the first time following the Paul's advice however I didn't run SHRINKFILE immediately after log backup. I did it after John posted his answer and it has fixed the issue. – andrews Sep 19 '17 at 20:10
  • @andrews But what did you mean saying "I have run log backup manually"? Is there any job that makes regular log backups but it was not enough and you did it manually? Is it possible that another log backup was made immediately before your "manual" log backup? Can you show your log backup history for this db including backup_start_time and some other columns? Smth like this:select backup_start_date, type, cast(backup_size / 1024 / 1024 as decimal(20, 2)) as Mb from msdb..backupset where database_name = 'DB2' – sepupic Sep 19 '17 at 20:24
0

SQL Server log files make a record of all the steps taken to perform a transaction; if the transaction is rolled back, then these steps need to be reversed.

In the SIMPLE recovery model, once a transaction has been committed, the part of the log with the steps for it is released and can be reused.

In the FULL recovery model, the transaction information is kept in the log until a transaction log backup is run; by backing up the steps for every change in the database, you can use transaction log backups (in conjunction with a full backup) to restore a database to a specific moment in time.

Replication can also cause data to be kept in the log file, as those same commands will be sent to the other server to make the same changes there.

When a new transaction is happening, but the log file is full of other transactions being kept, if the log file is set to grow automatically (which it normally is by default), then the database server will try to make the log file bigger.

Log files can also be shrunk automatically; however, that is not the default setting. Because....

Under normal operating conditions, you do not want to shrink a log file. It grew to its present size in order to accommodate your normal workload. If you shrink it, it will almost certainly need to grow again.

Now, you're seeing 'LOG_BACKUP' as the reason why your log cannot be reused. There's nothing wrong with taking a transaction log manually; however, if that's the usual reason your log is saying it's full, then you want to be sure you're taking log backups regularly, with a scheduled job (ideally to a shared file server). If the database is active, then there will be new logged activity almost immediately after you run that manual log backup, if there's been a CHECKPOINT since the last log backup, you may see LOG_BACKUP (even though the log is not full).

If large INSERTs are causing the log to fill, then try breaking them up into smaller chunks, and make sure the log file backup job runs in between each INSERT command.

NOTE: You can check on how full your log files are with the command DBCC SQLPERF (LOGSPACE); this will show both the size of your log file, and the percentage of that space which is in use.

  • Thanks for the info but I need a fix to my immediate problem: I cannot continue DB updates because the log file is not growing and DB is stuck in LOG_BACKUP for several hours already. How would you suggest getting the log_reuse_wait_desc to show NOTHING? – andrews Sep 19 '17 at 15:25
  • Note that log_reuse_wait_desc may not show NOTHING even when there's space available in the log - LOG_BACKUP will show up as a reason the log cannot be reused as soon as something changes and a CHECKPOINT is taken after a log backup has been performed. – RDFozz Sep 19 '17 at 16:47
  • If your disk is not full, and your log file is, check to see if there's a limit on how large your log file is and consider if you want to let it get bigger. If things work under normal circumstances, I'd try smaller INSERTs and more frequent log backups, as I state in my answer. – RDFozz Sep 19 '17 at 16:48
  • thanks, appreciate your efforts. I do know about auto-growth. But I think my situation was not trivial: replication, mirroring and me too frequently shrinking log files before they have probably reached the mirror or before they have been applied to actual DB data. The solution from John Eisbrener has worked for me. – andrews Sep 19 '17 at 16:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.