February 3, 2006 at 8:42 am
My production database was set to Bulk-Log recovery model. We were running full backups weekly, diffs daily and transaction backups hourly.
On Jan 28, our full backup job failed (disk space error) but trans backup succeeded. The next days diff backup and transaction logs completed successfully.
On 30 Jan - 3 Feb, transaction log backups failed and reported that there was not enough space to perform backup. I continued to delete, older diffs to clear space and eventually free 114 GB space on the backup drive. I also performed a manual full backup.
However, when I now try to perform a transaction log backup, it fails with the same error. The odd thing is that the transaction log files are growing. _Log1 is now 107 GB and _LOG2 is 62 GB. It appears that with each unsuccessful attempt to backup the log files, they grew during the process and then the job would fail.
Two weeks ago there were configuration changes that were made to the configuration of the database server by the vendor of an application that my company purchased. I am not sure of all changes that were made but need to understand what changes could possibly cause this to happen?
Any help is deeply appreciated.
Greg
February 3, 2006 at 10:58 am
The log continues to grow if there are any lasting open transactions.
Perhaps the SET implicit transaction option was modified?
Have you contacted the vendor on what settings were modified?
February 3, 2006 at 1:43 pm
You said "My production database was set to Bulk-Log recovery model" (emphasis mine) Does that mean it's not bulk-logged anymore ? Can you shrink the log. Have the backup jobs changed ?
February 6, 2006 at 1:37 pm
All,
thanks for your responses. I fixed the problem by performing the following steps.
1. Restored a full backup and placing the log files on separate drives.
2. Changed recovery model to simple.
3. Ran Backup Log (WITH TRUNCATE_ONLY)
4. Ran DBCC ShrinkFile() against each log file.
This shrank the files from 100 down to 1 GB and 60 down to 10 GB.
5. Moved logs back to original drives.
6. Recovery model FULL mode.
7. Reinstituted log shipping.
Of course full backups were taken at times during the entire process.
I need to check to see if Implicit transactions were modified or not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply