log backup fails a few times a day

st8floorsupst8floorsup Posts: 27
edited May 14, 2008 10:19AM in SQL Backup Previous Versions
I have a server where the log backup that runs every hour fails a few times a day. Unusally when I get the email notification I fire the job off again and it works fine so I haven't spent any time investigating. Today I tried running the job serveral times and it kept failing. I looked in management studio activity monitor and noticed the Checkpoint was blocking. I ran checkpoint by itself and it took 6:48 minutes to complete. After this completed I was able to run the backup job successfully. I'm running sql backup 5.0. The backup runs checkpoint before it starts so, what is the wait time for log backup and how can I increase it? I'm not sure the significance of long wait times for checkpoint. I know you can pass a variable to increase the resources it will use to speed up the process. This is a SQL2005 SP2 standard edition 32 bit server.

Thanks,
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Coul you please post the contents of the SQL Backup log, for a couple of the backups that failed? The default folder for the log files is C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • st8floorsupst8floorsup Posts: 27
    SQL Backup log file
    5/13/2008 9:55:08 AM: Backing up myDB (transaction log) to:
    5/13/2008 9:55:08 AM: \\myServer\g$\Backups\myDB-20080513_095501_1log.sqb

    5/13/2008 9:55:08 AM: BACKUP LOG [myDB] TO DISK = '\\myServer\g$\Backups\myDB-20080513_095501_1log.sqb' WITH NAME = 'myDB Transaction Log backup', DESCRIPTION = 'SQLBackup Transaction Log Backup', ERASEFILES_ATSTART = 2, MAILTO_ONERROR = 'sqladmin@mycompany.com', COMPRESSION = 1

    5/13/2008 9:55:39 AM: 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.)
    5/13/2008 9:56:44 AM: SQL error 3013: BACKUP LOG is terminating abnormally.
    5/13/2008 9:56:44 AM: SQL error 3201: Cannot open backup device 'SQLBACKUP_D2B18610-841A-4447-A053-AFF0F0FAC1EE'. Operating system error 0x80070002(The system cannot find the file specified.).
    5/13/2008 9:56:44 AM:
    5/13/2008 9:56:44 AM: Memory profile
    5/13/2008 9:56:44 AM: Type Maximum Minimum Average Blk count Total
    5/13/2008 9:56:44 AM:
    5/13/2008 9:56:44 AM: Commit 67313664 4096 957474 1983 1898672128
    5/13/2008 9:56:44 AM: Reserve 12251136 4096 291477 427 124461056
    5/13/2008 9:56:44 AM: Free 23859200 4096 478018 260 124284928
    5/13/2008 9:56:44 AM: Private 67313664 4096 1136519 1666 1893441536
    5/13/2008 9:56:44 AM: Mapped 4444160 4096 157341 75 11800576
    5/13/2008 9:56:44 AM: Image 24862720 4096 176219 669 117891072
    5/13/2008 9:56:44 AM:
    5/13/2008 9:56:44 AM: Warning 300: Backup failed. Retry attempt: 1
    5/13/2008 9:56:46 AM: BACKUP LOG [myDB] TO DISK = '\\myServer\g$\Backups\myDB-20080513_095501_1log.sqb' WITH NAME = 'myDB Transaction Log backup', DESCRIPTION = 'SQLBackup Transaction Log Backup', INIT, ERASEFILES_ATSTART = 2, MAILTO_ONERROR = 'sqladmin@mycompany.com', COMPRESSION = 1

    5/13/2008 9:56:46 AM:
    5/13/2008 9:56:46 AM: Warning 300: Backup failed. Retry attempt: 2
    5/13/2008 9:56:48 AM: BACKUP LOG [myDB] TO DISK = '\\myServer\g$\Backups\myDB-20080513_095501_1log.sqb' WITH NAME = 'myDB Transaction Log backup', DESCRIPTION = 'SQLBackup Transaction Log Backup', INIT, ERASEFILES_ATSTART = 2, MAILTO_ONERROR = 'sqladmin@mycompany.com', COMPRESSION = 1

    5/13/2008 9:56:48 AM:
    5/13/2008 9:56:48 AM: Warning 300: Backup failed. Retry attempt: 3
    5/13/2008 9:56:50 AM: BACKUP LOG [myDB] TO DISK = '\\myServer\g$\Backups\myDB-20080513_095501_1log.sqb' WITH NAME = 'myDB Transaction Log backup', DESCRIPTION = 'SQLBackup Transaction Log Backup', INIT, ERASEFILES_ATSTART = 2, MAILTO_ONERROR = 'sqladmin@mycompany.com', COMPRESSION = 1

    5/13/2008 9:56:50 AM:
    5/13/2008 9:56:50 AM: Warning 300: Backup failed. Retry attempt: 4
    5/13/2008 9:56:52 AM: BACKUP LOG [myDB] TO DISK = '\\myServer\g$\Backups\myDB-20080513_095501_1log.sqb' WITH NAME = 'myDB Transaction Log backup', DESCRIPTION = 'SQLBackup Transaction Log Backup', INIT, ERASEFILES_ATSTART = 2, MAILTO_ONERROR = 'sqladmin@mycompany.com', COMPRESSION = 1

    5/13/2008 9:56:52 AM: Warning 150: MAILTO error: SMTP host name not defined.


    SQL Backup log file
    5/13/2008 10:55:08 AM: Backing up myDB (transaction log) to:
    5/13/2008 10:55:08 AM: \\myServer\g$\Backups\myDB-20080513_105501_1log.sqb

    5/13/2008 10:55:08 AM: BACKUP LOG [myDB] TO DISK = '\\myServer\g$\Backups\myDB-20080513_105501_1log.sqb' WITH NAME = 'myDB Transaction Log backup', DESCRIPTION = 'SQLBackup Transaction Log Backup', ERASEFILES_ATSTART = 2, MAILTO_ONERROR = 'sqladmin@mycompany.com', COMPRESSION = 1

    5/13/2008 10:55:39 AM: 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.)
    5/13/2008 10:55:56 AM: SQL error 3013: BACKUP LOG is terminating abnormally.
    5/13/2008 10:55:56 AM: SQL error 3201: Cannot open backup device 'SQLBACKUP_F1615261-0E8D-4B2B-BF3E-9870D0FB88CF'. Operating system error 0x80070002(The system cannot find the file specified.).
    5/13/2008 10:55:56 AM:
    5/13/2008 10:55:56 AM: Memory profile
    5/13/2008 10:55:56 AM: Type Maximum Minimum Average Blk count Total
    5/13/2008 10:55:56 AM:
    5/13/2008 10:55:56 AM: Commit 67313664 4096 963828 1969 1897779200
    5/13/2008 10:55:56 AM: Reserve 12251136 4096 301899 415 125288448
    5/13/2008 10:55:56 AM: Free 23859200 4096 493454 252 124350464
    5/13/2008 10:55:56 AM: Private 67313664 4096 1154497 1640 1893376000
    5/13/2008 10:55:56 AM: Mapped 4444160 4096 157341 75 11800576
    5/13/2008 10:55:56 AM: Image 24862720 4096 176219 669 117891072
    5/13/2008 10:55:56 AM:
    5/13/2008 10:55:56 AM: Warning 300: Backup failed. Retry attempt: 1
    5/13/2008 10:55:58 AM: BACKUP LOG [myDB] TO DISK = '\\myServer\g$\Backups\myDB-20080513_105501_1log.sqb' WITH NAME = 'myDB Transaction Log backup', DESCRIPTION = 'SQLBackup Transaction Log Backup', INIT, ERASEFILES_ATSTART = 2, MAILTO_ONERROR = 'sqladmin@mycompany.com', COMPRESSION = 1

    5/13/2008 10:55:58 AM:
    5/13/2008 10:55:58 AM: Warning 300: Backup failed. Retry attempt: 2
    5/13/2008 10:56:00 AM: BACKUP LOG [myDB] TO DISK = '\\myServer\g$\Backups\myDB-20080513_105501_1log.sqb' WITH NAME = 'myDB Transaction Log backup', DESCRIPTION = 'SQLBackup Transaction Log Backup', INIT, ERASEFILES_ATSTART = 2, MAILTO_ONERROR = 'sqladmin@mycompany.com', COMPRESSION = 1

    5/13/2008 10:56:00 AM:
    5/13/2008 10:56:00 AM: Warning 300: Backup failed. Retry attempt: 3
    5/13/2008 10:56:02 AM: BACKUP LOG [myDB] TO DISK = '\\myServer\g$\Backups\myDB-20080513_105501_1log.sqb' WITH NAME = 'myDB Transaction Log backup', DESCRIPTION = 'SQLBackup Transaction Log Backup', INIT, ERASEFILES_ATSTART = 2, MAILTO_ONERROR = 'sqladmin@mycompany.com', COMPRESSION = 1

    5/13/2008 10:56:02 AM:
    5/13/2008 10:56:02 AM: Warning 300: Backup failed. Retry attempt: 4
    5/13/2008 10:56:04 AM: BACKUP LOG [myDB] TO DISK = '\\myServer\g$\Backups\myDB-20080513_105501_1log.sqb' WITH NAME = 'myDB Transaction Log backup', DESCRIPTION = 'SQLBackup Transaction Log Backup', INIT, ERASEFILES_ATSTART = 2, MAILTO_ONERROR = 'sqladmin@mycompany.com', COMPRESSION = 1

    5/13/2008 10:56:04 AM: Warning 150: MAILTO error: SMTP host name not defined.


    SQL Backup log file
    5/13/2008 10:58:25 AM: Backing up myDB (transaction log) to:
    5/13/2008 10:58:25 AM: \\myServer\g$\Backups\myDB-20080513_105818_1log.sqb

    5/13/2008 10:58:25 AM: BACKUP LOG [myDB] TO DISK = '\\myServer\g$\Backups\myDB-20080513_105818_1log.sqb' WITH NAME = 'myDB Transaction Log backup', DESCRIPTION = 'SQLBackup Transaction Log Backup', ERASEFILES_ATSTART = 2, MAILTO_ONERROR = 'sqladmin@mycompany.com', COMPRESSION = 1

    5/13/2008 10:58:55 AM: 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.)
    5/13/2008 10:59:58 AM: SQL error 3013: BACKUP LOG is terminating abnormally.
    5/13/2008 10:59:58 AM: SQL error 3201: Cannot open backup device 'SQLBACKUP_7369DD06-0402-4A22-BB37-5C45B71B4E39'. Operating system error 0x80070002(The system cannot find the file specified.).
    5/13/2008 10:59:58 AM:
    5/13/2008 10:59:59 AM: Memory profile
    5/13/2008 10:59:59 AM: Type Maximum Minimum Average Blk count Total
    5/13/2008 10:59:59 AM:
    5/13/2008 10:59:59 AM: Commit 67313664 4096 878683 2156 1894440960
    5/13/2008 10:59:59 AM: Reserve 12251136 4096 215258 600 129155072
    5/13/2008 10:59:59 AM: Free 23859200 4096 495288 250 123822080
    5/13/2008 10:59:59 AM: Private 67313664 4096 941304 2012 1893904384
    5/13/2008 10:59:59 AM: Mapped 4444160 4096 157341 75 11800576
    5/13/2008 10:59:59 AM: Image 24862720 4096 176219 669 117891072
    5/13/2008 10:59:59 AM:
    5/13/2008 10:59:59 AM: Warning 300: Backup failed. Retry attempt: 1
    5/13/2008 11:00:01 AM: BACKUP LOG [myDB] TO DISK = '\\myServer\g$\Backups\myDB-20080513_105818_1log.sqb' WITH NAME = 'myDB Transaction Log backup', DESCRIPTION = 'SQLBackup Transaction Log Backup', INIT, ERASEFILES_ATSTART = 2, MAILTO_ONERROR = 'sqladmin@mycompany.com', COMPRESSION = 1

    5/13/2008 11:00:01 AM:
    5/13/2008 11:00:01 AM: Warning 300: Backup failed. Retry attempt: 2
    5/13/2008 11:00:03 AM: BACKUP LOG [myDB] TO DISK = '\\myServer\g$\Backups\myDB-20080513_105818_1log.sqb' WITH NAME = 'myDB Transaction Log backup', DESCRIPTION = 'SQLBackup Transaction Log Backup', INIT, ERASEFILES_ATSTART = 2, MAILTO_ONERROR = 'sqladmin@mycompany.com', COMPRESSION = 1

    5/13/2008 11:00:03 AM:
    5/13/2008 11:00:03 AM: Warning 300: Backup failed. Retry attempt: 3
    5/13/2008 11:00:05 AM: BACKUP LOG [myDB] TO DISK = '\\myServer\g$\Backups\myDB-20080513_105818_1log.sqb' WITH NAME = 'myDB Transaction Log backup', DESCRIPTION = 'SQLBackup Transaction Log Backup', INIT, ERASEFILES_ATSTART = 2, MAILTO_ONERROR = 'sqladmin@mycompany.com', COMPRESSION = 1

    5/13/2008 11:00:05 AM:
    5/13/2008 11:00:05 AM: Warning 300: Backup failed. Retry attempt: 4
    5/13/2008 11:00:07 AM: BACKUP LOG [myDB] TO DISK = '\\myServer\g$\Backups\myDB-20080513_105818_1log.sqb' WITH NAME = 'myDB Transaction Log backup', DESCRIPTION = 'SQLBackup Transaction Log Backup', INIT, ERASEFILES_ATSTART = 2, MAILTO_ONERROR = 'sqladmin@mycompany.com', COMPRESSION = 1

    5/13/2008 11:00:07 AM: Warning 150: MAILTO error: SMTP host name not defined.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    I would suggest updating to SQL Backup 5.3. There is a bug in SQL Backup 5.0, where the backup reattemps never actually run.

    If there is a blocking process, SQL Backup 5.3 will fail on the first backup attempt. When the blocking process ends, SQL Backup will reattempt the backup, which should then succeed.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.