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.
