May 19, 2022 at 10:18 am
Our Reporting process works like this
00:00 - SQL Takes it's daily Full backup of the OLTP Database. Backups use CheckSum and Verify
02-00 - A different SQL Instance used for Reporting restores this database onto this instance
03-00 - A SQL Job on the Reporting Instance runs a process to create flattened heavily indexed reporting tables
Last night this SQL Job failed and it appears to fail due to corrupted data. I ran a CHECKDB and isolated the corruption to 4 tables. 2 were transient reporting tables so could be ignored but 2 were critical tables, one with 27m rows, and the corruption was in the data pages not indexes.
First I restored the same backup to the reporting server and experienced the same corruption in the same tables.
I couldn't run CheckDB on the OLTP DB due to the performance hit, but did run CheckTable on the two tables which were corrupted in my reporting DB. Both came back ok. I also checked the log of our weekly CheckDB of the OLTP DB which runs each Saturday. That was fine last Sat.
I then took a new backup of the OLTP DB, and restored this to the reporting instance. No corruption.
I'm at a loss as to what caused the original corruption. The original backup file seemed to have been corrupt as restoring it again showed the same corruption in the same tables, but how could it be corrupt if it was verified and check summed, and SQL didn't have any issue restoring it?
I've checked with our infrastructure team and they can't see any IO issues on the VMs or physical hosts.
Anything else I can check or read up on?
May 19, 2022 at 11:53 am
When you restored the prod db a second time to reporting, did you first drop de reporting db or did you restore with replace ?
In such case it will reuse the under laying files as allocated.
So it may hit the same DISK issues.
Check the disk/volume for errors.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 19, 2022 at 12:01 pm
Interesting, and sadly, I actually cannot remember. I did drop the DB but can't remember if that was the first or second restore.
If we assume I didn't drop it and restored into the old one, that would suggest the reporting DB MDF file and not the backup being restored had corruption?
May 19, 2022 at 12:28 pm
For reference, I've been referred to this article that infers that yes, a backup that is verified, check summed, and can be restored without error, might still be corrupt which was news to me
May 19, 2022 at 12:46 pm
Indeed, that's why we always perform DBCC CHECKDB after a database restore.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 19, 2022 at 1:09 pm
I have now restored the backup to completely different hardware and done a CheckDB and it was fine so the issue is very much with the Reporting DB MDF file and not the bak file by the looks of it, as you suggested in post 2.
Still no idea what caused that though.
May 19, 2022 at 1:19 pm
...
Still no idea what caused that though.
hardware issue
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply