Backup Database is terminating abormally
-
The SQL 2014 is on version 12.0.2000.8 (64X).
I have a stored procedure for daily full backup of every database (except for temp) to a backup hard drive, one bak file for each db. The backup parameters are: NOFORMAT, INIT, KIP, NOREWIND, NOUNLOAD, CHECKSUM, COMPRESSION.
The stored procedure has been working fine until a few days again. It failed with the error message:
SQLSTATE 42000 (Error 3013) BACKUP DATABASE is terminating abnormally.
Because I am doing a bak file per db with overwriting, I do not think the setting of FORMAT/NOFORMAT matters. Anyway, I tried both and both failed.
I had to restart the SQL server and it solved the error. But two days later it happened again.
Generally, the syptom is that, once the error appears, no backup can be done. Even when I backup a very small user db (500M) through the SSMS UI, I got the same error.
Before this happened, there was no change to the server/instance. Four days before this occured, there was an abormal growth of tempdb files. I reset the tempdb file size and make it bigger than any user db, and disabled the tempdb file growth. This might be irrelevant, because there were three successful daily backups after this and before the error occurred the first time.
Any suggestions?
-
Sounds like you are running out of space.
Even though you using Init to overwrite the file I think the fact that that compression is being used will cause the bak file to grow initially before reducing in size.
See a quick test below I carried out.
-
The backup drive has over 1T free space, while the total size of compressed bak files during a daily backup is less than 40G. So it is not a space issue.
-
One minor correction: I had to restart the SQL Server Service to resolve the issue temporarily. I did not restart OS.
It looks like a cycle of:
1. restarting the service;
2. backup works fine for a couple of days;
3. The error occurs the first time and then no backup command works.
-
I think this is telling you that a file mark in the backup file can't be read. Take the NOFORMAT in your backup command and change it to FORMAT. I know you said in your OP that this doesn't matter, but give it a try and let us know if it works. I've heard of this being an issue in SQL 2005, but not SQL 2014.
And what's a KIP in your options? Do you mean SKIP?
-
Yes. I meant SKIP.
I tried the FORMAT option with a new file path but got the same error.
-
Sometimes this error message occurs due to network traffic on the server. In such situation you need to change the Time
-
johnwalker10 (7/9/2016)
Sometimes this error message occurs due to network traffic on the server. In such situation you need to change the TimeThe time? Changing the date & time on the server or something else?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
seaport (7/8/2016)
The SQL 2014 is on version 12.0.2000.8 (64X).I have a stored procedure for daily full backup of every database (except for temp) to a backup hard drive, one bak file for each db. The backup parameters are: NOFORMAT, INIT, KIP, NOREWIND, NOUNLOAD, CHECKSUM, COMPRESSION.
The stored procedure has been working fine until a few days again. It failed with the error message:
SQLSTATE 42000 (Error 3013) BACKUP DATABASE is terminating abnormally.
Because I am doing a bak file per db with overwriting, I do not think the setting of FORMAT/NOFORMAT matters. Anyway, I tried both and both failed.
I had to restart the SQL server and it solved the error. But two days later it happened again.
Generally, the syptom is that, once the error appears, no backup can be done. Even when I backup a very small user db (500M) through the SSMS UI, I got the same error.
Before this happened, there was no change to the server/instance. Four days before this occured, there was an abormal growth of tempdb files. I reset the tempdb file size and make it bigger than any user db, and disabled the tempdb file growth. This might be irrelevant, because there were three successful daily backups after this and before the error occurred the first time.
Any suggestions?
Can you post the error you receiving here? Hope you have checked SQL Logs.
-
VastSQL,
Thanks for the great tip. I checked the sql server log and it shows a fatal exception with a SQL Dump.
"SqlDumpExceptionHandler: Process 2396 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I googled this exception and found out it was a known issue to version prior to 2014:
https://support.microsoft.com/en-us/kb/972936
Also, it occured to SQL 2014 too, which was dicussed via another post.
http://qa.sqlservercentral.com/Forums/Topic1588898-3411-1.aspx
My plan is to install SQL2014 SP1 as soon as possible.
Any other suggestions?
-
seaport (7/15/2016)
VastSQL,Thanks for the great tip. I checked the sql server log and it shows a fatal exception with a SQL Dump.
"SqlDumpExceptionHandler: Process 2396 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I googled this exception and found out it was a known issue to version prior to 2014:
https://support.microsoft.com/en-us/kb/972936
Also, it occured to SQL 2014 too, which was dicussed via another post.
http://qa.sqlservercentral.com/Forums/Topic1588898-3411-1.aspx
My plan is to install SQL2014 SP1 as soon as possible.
Any other suggestions?
Have you gone through the dump file created?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply