Sql Server Backup fails sometimes RRS feed

  • Question

  • We are running SQL Server 2008 R2 SP1. There is a scheduled transaction log backup using SQL Server maintenance job that runs at a certain time of the day. The job runs fine on few days and fails few days and there is no specific pattern.

    I changed the schedule to run every 1 hour and the log backups were fine for the first 3 attempts and started failing randomly.

    -- operating system error 112(There is not enough space on the disk.)--

    I am not sure why it fails few times and runs fine the rest of the times. The backups are going directly to same machine - e.g. D:\\vnvx\SQL_backups\

    SQL Server errorlog has the following messages:

    Backup Error: 3041, Severity: 16, State: 1.

    Executing the query "BACKUP DATABASE [AHNOSQL] TO  DISK = N'D:\\MSSQL\\BA..." failed with the following error: "A nonrecoverable I/O error occurred on file "D:\\MSSQL\\BACKUP\\AHNOSQL_backup_2016_08_17_210003_8022693.bak:" 112(There is not enough space on the disk.).
    BACKUP 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.

    If it fails on a regular basis I could have checked permissions, SQL Server agent start up ID etc. However it fails few times and runs successfully few times.

    I had checked for memory and found memory is much more than required.

    The job is set to run with 1 retry after 2 minutes and sometimes the 2nd attempt goes through.

    Please help. Thanks in advance.

    Friday, August 19, 2016 12:32 PM