1

This question already has an answer here:

I'll start by saying our databases use full recovery mode and we run SQL Server 2014.

Over the last few days our backups appeared to spiral out of control to the point that our full/differential backups were failing and only extremely large transactional backups were getting created. This of course caused a disk space issue and required a fair bit of manual intervention to restore order.

I manually ran a full backup on each database and then tried to follow up by running a transaction backup. During this period, one database's log file was increasing in size to the point that it ran out of disk space. Usually the log file would be around 1 GB, it is now sitting at about 30 GB.

Running a transactional backup did not reduce the size of the log file and I was stuck in a position where the log file was full and there was no room left on the disk. As a last ditch effort, I ran the SHRINKFILE( dbname, 4096) which instantly reduced the size and all appeared to be normal.

After doing so, I noticed my backups were no longer working and I was receiving an error:

Data error (cyclic redundancy check) failed. I tried running CheckDB and was presented with the following message:

A severe error occurred on the current command. The results, if any, should be discarded.

Given I was unable to back the database up, I decided to try and restore from earlier in the day off a full backup and 1 single transactional backup I had created. Both these files were large. The reason I only had 1 transactional backup was because I had disabled the job from running to avoid things spiralling further out of control.

New DB is up and running and backups work fine, but the issue of the large log file still prevails. My transactional backups are very small now but I can't seem to shrink the DB.

Anyone have any ideas? Alternatively, if there's a way to resolve the redundancy issue on the original database that would be a preferable solution as I'd have no data loss then.

No open transactions. The log_reuse_wait_desc is "LOG_BACKUP" which my understanding means that it is waiting for a log backup to run. This is madness because I have done several ;) dbcc sqlperf(logspace) is claiming I'm using 92% space.

Log backups run every 15 minutes in normal circumstances, but like I mentioned, these had been failing over the weekend. I've turned them off for the time being to stop the drive filling up and have just been manually running transactional backups. The database is not live at the moment so no additional data is going in there, I've run maybe 4 transactional backups as a test in the last hour or so.

None of replication, mirroring, or AOAG is configured here.

marked as duplicate by Paul White sql-server Sep 20 at 19:52

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2

What is the frequency of the log backups ?

Use dbcc sqlperf(logspace) to check how much % is free space in the logs.

There is something that is holding the logs up. Check the log_reuse_wait_desc for that database and take action accordingly.

Follow the below article :

https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/

  • Let us continue this discussion in chat. – Ramakant Dadhichi Jul 10 '17 at 10:36
  • 1
    The answer is a little bit hit and miss. I attempted to defrag my indexes which further ballooned the log file out. I tried to run another transaction backup and on this occasion it was able to release it and 99% of the log was unused. I was then able to shrink the database successfully :) – Sami.C Jul 10 '17 at 13:35
0

log_reuse_wait_desc in sys.databases for your database can show LOG_BACKUP under 2 circumstances:

  1. waiting for a log backup to run
  2. or the log records that were backed up were all in the current VLF and so it could not be cleared. If the log backup ran but still log_reuse_wait_desc shows as "LOG_BACKUP", it's probably because of the 2nd reason. What this means is that there are very few transactions on the database and these are all on the same VLF. When the log backup runs it cant clear the current VLF; so log_reuse_wait_desc can't be reset.

Paul Randal has a great post about this here.

-2

Put the database in single recovery mode.
Shrink the transaction log.
Put the database back into full recovery mode.
Make a full database backup.
Start again your transactional backups regular jobs.

PS : Also make sure your transactional backups are not "copy-only" tagged.

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