This week’s blog post comes from Rakesh Yadav, a good friend of mine. Rakesh is dealing in hardware basically, and since he holds good knowledge about servers some of his clients call him if they get an issue. The last week, one of his customers call him that they are seeing an Error 3043, Severity 16, State 1.
What exactly the issue was?
Rakesh’s customer has SQL Server 2012 installed, the server is configured to use the native maintenance plan of SQL Server. While the backup job was running it wrote the message in Error Log which reads like below. Since he knows that I can be approached in cases like this, he calls me.
Error: 3043, Severity: 16, State: 1. BACKUP 'SomeDB' detected an error on page (1:352044) in file 'D:\SQLDATA\SomeDB.mdf'
I dig up a little deeper and noticed that it’s a corruption issue. I search through my notes and bookmark and it landed me in the Paul Randal’s article.
The workaround I’ve used to fix Error 3043, Severity 16, State 1
As you are expecting, I have asked Rakesh if his client has the latest backup available. Luckily, his client has the latest FULL backup available before the corruption reported, followed by the differential backup. I have followed Paul Randal’s article and took action as below:
- Restored the previous night’s full backup followed by the differential backup (4 hour’s data loss is acceptable)
- Developer/Support Team has verified the data
- Exported the data where it is possible and imported into the newly created database
- Renamed the OLD database to SomeDB2
- Renamed the newly created database to SomeDB
This is how I have fixed the issue for my friend. However, I would like to advise you, a very important advise. Please use the “with checksum” parameter whenever you run or schedule the backup. This will result in an error and it would stop completing the backup. This would alarm you that there may be a damage in an underlying disk subsystem. Paul Randal, In his article I’ve mentioned above has said that in cases like this you can still backup and restore your database using “Continue_after_error” parameter.
You may also want to test your backup files from time to time so that you won’t get surprised when you need them the most.
-- Backup Command BACKUP DATABASE [SomeDB] TO DISK = N'E:\SQL_Backups\SomeDB\SomeDB.bck' WITH CHECKSUM, CONTINUE_AFTER_ERROR; GO -- Restore command RESTORE DATABASE [SomeDB] TO DISK = N'E:\SQL_Backups\SomeDB\SomeDB.bck' WITH REPLACE, CONTINUE_AFTER_ERROR; GO
There are few more tips I’ve pen down for you to take a look by browsing through Troubleshooting category of my blog space.
Let me know how do you like this tip in the comment section below.