Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2010
    Posts
    1

    Unanswered: Transaction log backup fails....

    I have several databases in my SQL Server 2005

    They all have 2 maintenance task
    1.- Shrinks db, creates full backup, cleans the history and reindex the db.
    2.- Creates transaction log backups every hour and cleans old trn files.

    I have trouble with a couple of db that, after the full backup, they generate 3 or 4 transaction log backups and then start failing.

    This is the example of what the log says about it:



    NEW COMPONENT OUTPUT
    Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
    Report was generated on "s-bdcentral1".
    Maintenance Plan: Petrohora
    Duration: 00:00:02
    Status: Warning: One or more tasks failed..
    Details:
    Back Up Database Task (s-bdcentral1)
    Backup Database on Central1
    Databases: petro
    Type: Transaction Log
    Append existing
    Task start: 2010-08-11T04:24:31.
    Task end: 2010-08-11T04:24:31.
    Failed-1073548784) Executing the query "BACKUP LOG [petro] TO DISK = N'H:\\Backup\\petro\\petro_backup_201008110424.trn ' WITH NOFORMAT, NOINIT, NAME = N'petro_backup_20100811042430', SKIP, REWIND, NOUNLOAD, STATS = 10
    " failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.
    BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Command:EXECUTE master.dbo.xp_create_subdir N''H:\Backup\petro''
    GO
    BACKUP LOG [petro] TO DISK = N''H:\Backup\petro\petro_backup_201008110424.trn'' WITH NOFORMAT, NOINIT, NAME = N''petro_backup_20100811042430'', SKIP, REWIND, NOUNLOAD, STATS = 10
    GO
    declare @backupSetId as int
    select @backupSetId = position from msdb..backupset where database_name=N''petro'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''petro'' )
    if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''petro'''' not found.'', 16, 1) end
    RESTORE VERIFYONLY FROM DISK = N''H:\Backup\petro\petro_backup_201008110424.trn'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND


    Back Up Database Task (s-bdcentral1)
    Backup Database on Central1
    Databases: petro
    Type: Transaction Log
    Append existing
    Task start: 2010-08-11T04:24:31.
    Task end: 2010-08-11T04:24:31.
    Failed-1073548784) Executing the query "declare @backupSetId as int
    select @backupSetId = position from msdb..backupset where database_name=N'petro' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'petro' )
    if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''petro'' not found.', 16, 1) end
    RESTORE VERIFYONLY FROM DISK = N'H:\\Backup\\petro\\petro_backup_201008110424.trn ' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
    " failed with the following error: "Cannot open backup device 'H:\\Backup\\petro\\petro_backup_201008110424.trn' . Operating system error 2(error not found).
    VERIFY DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Command:EXECUTE master.dbo.xp_create_subdir N''H:\Backup\petro''
    GO
    BACKUP LOG [petro] TO DISK = N''H:\Backup\petro\petro_backup_201008110424.trn'' WITH NOFORMAT, NOINIT, NAME = N''petro_backup_20100811042430'', SKIP, REWIND, NOUNLOAD, STATS = 10
    GO
    declare @backupSetId as int
    select @backupSetId = position from msdb..backupset where database_name=N''petro'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''petro'' )
    if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''petro'''' not found.'', 16, 1) end
    RESTORE VERIFYONLY FROM DISK = N''H:\Backup\petro\petro_backup_201008110424.trn'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

    My question is, I have enough space to create the backup, no tasks other than this maintenance plans run on the night. I have no more evidence to know why this transaction logs are not being generated.

    This keeps happening even if I delete the task and create a new one.

    Can anyone help me find what is going on?

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    There are several issues with your maintenance plans, and the ones that leap out are shrinking the database, and appending backups to existing backup device. The failure you're getting indicates that most likely the recovery model has changed from FULL/BULK-LOGGED to SIMPLE, and then back. After that no transaction log backups are possible without taking a full backup first.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •