Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backups failing - SQL Server 2008 R2


Backups failing - SQL Server 2008 R2

Author
Message
Phil4860
Phil4860
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
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
GAURAV UPADHYAY
GAURAV UPADHYAY
Old Hand
Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)

Group: General Forum Members
Points: 375 Visits: 370
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
Phil4860
Phil4860
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
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?
GAURAV UPADHYAY
GAURAV UPADHYAY
Old Hand
Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)

Group: General Forum Members
Points: 375 Visits: 370
In that case "transaction log full" error should not come. Please check file auto growth settings for log file again
Phil4860
Phil4860
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47263 Visits: 44392
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Phil4860
Phil4860
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47263 Visits: 44392
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Phil4860
Phil4860
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
Will do - but from what I have been told these are just generic messages.

Will investigate and post if I find anything
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search