February 18, 2014 at 3:48 am
Sorry if this is an obvious answer but I just cannot see why I am having these problems
I have a database which will not backup
It is set in Simple recovery mode but every day I am seeing lots of the following errors
The transaction log for database 'mydb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases (Error: 9002, Severity: 16, State: 1)
There seems to be nothing in the log_reuse_wait_desc column to help.
The Backup fails with the following error
BACKUP failed to complete the command BACKUP DATABASE . Check the backup application log for detailed messages. (Error: 3041, Severity: 16, State: 1)
The log file is set to auto growth and unlimited size.
When I check there are no transactions in progress.
Sorry if this is a simple one - although I am experienced with SQL it is unfortunately not in the Microsoft version
February 18, 2014 at 3:54 am
Looks like log file has filled up the entire disk on which it is hosted. Try adding a new log file on a different drive and then try taking a backup
February 18, 2014 at 3:57 am
That was the case a while ago but I have added a new disk and cleared up space by moving some files around.
The log file is only 99MB with 466GB free space on the disk.
Could the log file still think the disk is full?
February 18, 2014 at 4:05 am
In that case "transaction log full" error should not come. Please check file auto growth settings for log file again
February 18, 2014 at 4:09 am
It had changed itself to Restricted File Growth (MB) 2,097,152.
I changed this again to Unrestricted and it resets itself to the Restricted File Growth (MB) settings above.
February 18, 2014 at 4:13 am
Phil4860 (2/18/2014)
It had changed itself to Restricted File Growth (MB) 2,097,152.I changed this again to Unrestricted and it resets itself to the Restricted File Growth (MB) settings above.
That's normal, That's also 2 TB, I do hope you don't have a 2TB log file.
Can you try taking a native backup, not via some app just a plain BACKUP DATABASE ... and see what messages you get?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2014 at 4:16 am
Had thought of that but unfortunately the database is 2.5TB and I have not got enough disk space on the server or attached SAN. The area where the scheduled backups are stored is not available for native backups.
February 18, 2014 at 4:29 am
So then you're going to need to dig into the backup app's logs and find the detailed messages. The message in the SQL error log is just a 'something happened' message.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2014 at 4:31 am
Will do - but from what I have been told these are just generic messages.
Will investigate and post if I find anything
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply