Weird backup error. Should I be worried?

  • I have a T-SQL job backing up databases to a snapvault-mirrored NAS share. What snapvault means is we can write to the NAS share, but we cannot delete or change anything on the NAS share after about 60 seconds. Mirrored means the share data is then mirrored every X hours over to our secondary data center.

    The job failed this morning, but when I looked at all the backups, everything seemed to be backed up. Even the job log file indicated stuff had been backed up. It took me a few minutes before I finally saw this buried in the middle of the log:

    10 percent processed. [SQLSTATE 01000]

    20 percent processed. [SQLSTATE 01000]

    30 percent processed. [SQLSTATE 01000]

    40 percent processed. [SQLSTATE 01000]

    50 percent processed. [SQLSTATE 01000]

    60 percent processed. [SQLSTATE 01000]

    70 percent processed. [SQLSTATE 01000]

    80 percent processed. [SQLSTATE 01000]

    90 percent processed. [SQLSTATE 01000]

    Processed 104746488 pages for database 'MyDB', file 'MyDB_Data' on file 1. [SQLSTATE 01000]

    100 percent processed. [SQLSTATE 01000]

    Processed 1 pages for database 'MyDB', file 'MyDB_Log' on file 1. [SQLSTATE 01000]

    Msg 3634, Sev 16, State 2, Line 1 : The operating system returned the error '59(An unexpected network error occurred.)' while attempting 'SetEndOfFile' on '\\BackupShare\MyDB\20160721_FULL\MyDB_FULL_20160721_1930.BAK'. [SQLSTATE 42000]

    Msg 3013, Sev 16, State 1, Line 1 : BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]

    I tested the backup by restoring to a non-prod database and it restored fine. I could navigate the db and read the tables. But now I'm confused. I have this horrible looking error, but the backup is fine???

    Googling the error itself came up with a lot of SAN articles and forum threads that didn't seem relevant to my specific situation. Can anyone shed any light on what is going on here?

    Should I be worried? Does anyone know what could possible be happening to cause this error?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • I see this (and minor variations thereof) regularly; unfortunately, our infrastructure guys aren't able to provide me with any information about what's causing the problem...

    ...and are dangling the prospect of a new all-flash SAN to help these problems go away.

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • ThomasRushton (7/22/2016)


    I see this (and minor variations thereof) regularly; unfortunately, our infrastructure guys aren't able to provide me with any information about what's causing the problem...

    ...and are dangling the prospect of a new all-flash SAN to help these problems go away.

    "Oh, hey. We don't know what the problem is, but the way to fix it is to erase everything and start over!" :w00t:

    I really really don't want that to be the solution to my problem.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin (7/22/2016)


    I have a T-SQL job backing up databases to a snapvault-mirrored NAS share. What snapvault means is we can write to the NAS share, but we cannot delete or change anything on the NAS share after about 60 seconds. Mirrored means the share data is then mirrored every X hours over to our secondary data center.

    The job failed this morning, but when I looked at all the backups, everything seemed to be backed up. Even the job log file indicated stuff had been backed up. It took me a few minutes before I finally saw this buried in the middle of the log:

    10 percent processed. [SQLSTATE 01000]

    20 percent processed. [SQLSTATE 01000]

    30 percent processed. [SQLSTATE 01000]

    40 percent processed. [SQLSTATE 01000]

    50 percent processed. [SQLSTATE 01000]

    60 percent processed. [SQLSTATE 01000]

    70 percent processed. [SQLSTATE 01000]

    80 percent processed. [SQLSTATE 01000]

    90 percent processed. [SQLSTATE 01000]

    Processed 104746488 pages for database 'MyDB', file 'MyDB_Data' on file 1. [SQLSTATE 01000]

    100 percent processed. [SQLSTATE 01000]

    Processed 1 pages for database 'MyDB', file 'MyDB_Log' on file 1. [SQLSTATE 01000]

    Msg 3634, Sev 16, State 2, Line 1 : The operating system returned the error '59(An unexpected network error occurred.)' while attempting 'SetEndOfFile' on '\\BackupShare\MyDB\20160721_FULL\MyDB_FULL_20160721_1930.BAK'. [SQLSTATE 42000]

    Msg 3013, Sev 16, State 1, Line 1 : BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]

    I tested the backup by restoring to a non-prod database and it restored fine. I could navigate the db and read the tables. But now I'm confused. I have this horrible looking error, but the backup is fine???

    Googling the error itself came up with a lot of SAN articles and forum threads that didn't seem relevant to my specific situation. Can anyone shed any light on what is going on here?

    Should I be worried? Does anyone know what could possible be happening to cause this error?

    If the backups are there, you tested their integrity and they are fine, then this is clearly a time out issue, a network problem. The connection between the SQL servers and the NAS got temporary lost but not long enough to damage the backup.

    While I do not recommend this, you may attempt and play with the registry and change the timeout interval, but I think you should engage your Network team and explain this to them.

  • Brandie Tarvin (7/22/2016)


    ThomasRushton (7/22/2016)


    I see this (and minor variations thereof) regularly; unfortunately, our infrastructure guys aren't able to provide me with any information about what's causing the problem...

    ...and are dangling the prospect of a new all-flash SAN to help these problems go away.

    "Oh, hey. We don't know what the problem is, but the way to fix it is to erase everything and start over!" :w00t:

    I really really don't want that to be the solution to my problem.

    Yeah, it's a somewhat expensive way to fix things... :hehe:

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • sql-lover (7/22/2016)


    Brandie Tarvin (7/22/2016)


    I have a T-SQL job backing up databases to a snapvault-mirrored NAS share. What snapvault means is we can write to the NAS share, but we cannot delete or change anything on the NAS share after about 60 seconds. Mirrored means the share data is then mirrored every X hours over to our secondary data center.

    The job failed this morning, but when I looked at all the backups, everything seemed to be backed up. Even the job log file indicated stuff had been backed up. It took me a few minutes before I finally saw this buried in the middle of the log:

    10 percent processed. [SQLSTATE 01000]

    20 percent processed. [SQLSTATE 01000]

    30 percent processed. [SQLSTATE 01000]

    40 percent processed. [SQLSTATE 01000]

    50 percent processed. [SQLSTATE 01000]

    60 percent processed. [SQLSTATE 01000]

    70 percent processed. [SQLSTATE 01000]

    80 percent processed. [SQLSTATE 01000]

    90 percent processed. [SQLSTATE 01000]

    Processed 104746488 pages for database 'MyDB', file 'MyDB_Data' on file 1. [SQLSTATE 01000]

    100 percent processed. [SQLSTATE 01000]

    Processed 1 pages for database 'MyDB', file 'MyDB_Log' on file 1. [SQLSTATE 01000]

    Msg 3634, Sev 16, State 2, Line 1 : The operating system returned the error '59(An unexpected network error occurred.)' while attempting 'SetEndOfFile' on '\\BackupShare\MyDB\20160721_FULL\MyDB_FULL_20160721_1930.BAK'. [SQLSTATE 42000]

    Msg 3013, Sev 16, State 1, Line 1 : BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]

    I tested the backup by restoring to a non-prod database and it restored fine. I could navigate the db and read the tables. But now I'm confused. I have this horrible looking error, but the backup is fine???

    Googling the error itself came up with a lot of SAN articles and forum threads that didn't seem relevant to my specific situation. Can anyone shed any light on what is going on here?

    Should I be worried? Does anyone know what could possible be happening to cause this error?

    If the backups are there, you tested their integrity and they are fine, then this is clearly a time out issue, a network problem. The connection between the SQL servers and the NAS got temporary lost but not long enough to damage the backup.

    While I do not recommend this, you may attempt and play with the registry and change the timeout interval, but I think you should engage your Network team and explain this to them.

    Thank you for this information. I appreciate it, especially as you just validated a suspicion my team has been holding about other issues we've seen in the past with this and another similar NAS.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • take a look at this link: http://www.sqlservercentral.com/blogs/zoras-sql-tips/2016/05/03/sql-server-error-3013-restore-database-is-terminating-abnormally/ [/url]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply