July 22, 2016 at 7:29 am
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?
July 22, 2016 at 7:35 am
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
July 22, 2016 at 7:46 am
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.
July 22, 2016 at 8:00 am
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.
July 22, 2016 at 9:07 am
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
July 22, 2016 at 12:03 pm
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.
July 26, 2016 at 12:05 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply