Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backups failing - SQL Server 2008 R2 Expand / Collapse
Author
Message
Posted Tuesday, February 18, 2014 3:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 8:37 AM
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


Post #1542449
Posted Tuesday, February 18, 2014 3:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:20 AM
Points: 308, Visits: 307
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
Post #1542450
Posted Tuesday, February 18, 2014 3:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 8:37 AM
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?
Post #1542452
Posted Tuesday, February 18, 2014 4:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:20 AM
Points: 308, Visits: 307
In that case "transaction log full" error should not come. Please check file auto growth settings for log file again
Post #1542454
Posted Tuesday, February 18, 2014 4:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 8:37 AM
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.

Post #1542456
Posted Tuesday, February 18, 2014 4:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 42,488, Visits: 35,556
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 2008, MVP
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

Post #1542459
Posted Tuesday, February 18, 2014 4:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 8:37 AM
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.
Post #1542464
Posted Tuesday, February 18, 2014 4:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 42,488, Visits: 35,556
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 2008, MVP
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

Post #1542471
Posted Tuesday, February 18, 2014 4:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 8:37 AM
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
Post #1542474
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse