I received a batch of text messages because a backup job failed on
one database consistently and ran all the others just fine. So I looked
in the first place you should always check (in the Logs directory of
your SQL instance) and found this error in the errorlog file:
2007-08-08 21:25:07.39 Backup Error: 3041, Severity: 16, State: 1.
2007-08-08 21:25:07.39 Backup BACKUP failed to complete
the command BACKUP LOG AAAAOA07. Check the backup application log for
detailed messages.
So I turned to my backup software and looked there to see if any
additional errors were reported. It provided the following information:
SQL Backup log file
8/8/2007 8:25:06 PM: Backing up AAAAOA07 (transaction log) on SVYDATA2 instance to:
8/8/2007 8:25:06 PM: C:\sql_log_1\Backup\AAAAOA07_SVYDATA2_LOG_20070808_202506.sqb
8/8/2007 8:25:06 PM: BACKUP LOG [AAAAOA07] TO DISK =
'C:\sql_log_1\Backup\<AUTO>' WITH NAME = 'Database (AAAAOA07),
Aug 8 2007 8:25PM', DESCRIPTION = 'TLog Backup on Aug 8 2007 8:25PM
Database: AAAAOA07 Instance: (local) Server: svyData1', NO_TRUNCATE,
PASSWORD = 'XXXXXXXXXX', KEYSIZE = 256, MAILTO_ONERROR =
'shawn.johnson@xxxxxx.com', COMPRESSION = 0
8/8/2007 8:25:36 PM: VDI error 1010: Failed to get
configuration from server. Check that the SQL Server instance is
running, and that you have the SQL Server Systems Administrator server
role. Error code: (-2139684861: The api was waiting and the timeout
interval had elapsed.)
8/8/2007 8:25:36 PM: SQL error 3013: BACKUP LOG is terminating abnormally.
8/8/2007 8:25:36 PM: SQL error 4214: BACKUP LOG cannot be performed because there is no current database backup.
8/8/2007 8:25:36 PM:
8/8/2007 8:25:37 PM: Memory profile
....
My first thought was to make sure the Recovery Method was set correctly. Yes, it was Full Recovery. Yes,
the instance was running. After all, the other jobs on this instance
ran just fine and they all had the proper permissions. The Error code:
-2139684861 did provide much when I Googled it.
Then I saw the SQL Error 4214: BACKUP LOG cannot be performed
because there is no current database backup. That's strange as our
process is to do a full backup every night and when a new database is
created, we immediately create a backup to start this process and so
that we generate a copy for our disaster recovery site. So after asking
around, I found that this database was recently moved
(detached/attached) to this server from another server and domain. The
DBA had switched the Recovery Method from Simple to Full Recovery and
it fell into the log backup rotation during the middle of the
day. However, since it was moved from another server, it was never
backed up on the new server and hence the logs were failing because
there was not a previous full backup.
In SQL 2000, you would not have been told this and would have
assumed this was functional but it would not have been recoverable
until the first full backup. However, In 2005, this has been fixed so
that you are warned that the tlogs are not usable until your first full
backup.
We ran a full backup on the job and BAM, the errors stopped.