June 8, 2017 at 2:15 am
Hi Gurus,
I have a sql job that restores my backups every day and runs a DBCC CHECKDB on them. Yesterday one of my jobs started returning errors when running DBCC CHECKDB:DBCC results for 'MyDB'.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594232045568 (type Unknown), page ID (1:55257216) contains an incorrect page ID in its page header. The PageId in the page header = (1:15324352).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 11 pages from (1:55257216) to (1:55265303). See other errors for cause.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594232045568 (type Unknown), page ID (1:55257216) contains an incorrect page ID in its page header. The PageId in the page header = (1:15324352).
CHECKDB found 2 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 2 allocation errors and 1 consistency errors in database 'MyDB'.
Immediately i ran DBCC CHECKDB on production database but this didn't return any errors and everything seems to be fine.DBCC CHECKDB ([MyDB]) WITH ALL_ERRORMSGS, NO_INFOMSGS, MAXDOP = 1
My backup strategy is Full backup on Sunday midnight and Diff backups the rest of the days in a weekly cycle, also trn backups every 5 minutes.
So i thought maybe my diff backup from Wednesday is somehow corrupted because Tuesday restore was fine so just to make sure i restored a full backup from Sunday and diff backup from Wednesday on a different server, i ran DBCC but it returned the same error. Today i have the same problem after restoring Sunday Full + Thursday Diff backups.
Any thoughts?
June 8, 2017 at 2:41 am
Emil B - Thursday, June 8, 2017 2:15 AMHi Gurus,I have a sql job that restores my backups every day and runs a DBCC CHECKDB on them. Yesterday one of my jobs started returning errors when running DBCC CHECKDB:
DBCC results for 'MyDB'.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594232045568 (type Unknown), page ID (1:55257216) contains an incorrect page ID in its page header. The PageId in the page header = (1:15324352).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 11 pages from (1:55257216) to (1:55265303). See other errors for cause.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594232045568 (type Unknown), page ID (1:55257216) contains an incorrect page ID in its page header. The PageId in the page header = (1:15324352).
CHECKDB found 2 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 2 allocation errors and 1 consistency errors in database 'MyDB'.Immediately i ran DBCC CHECKDB on production database but this didn't return any errors and everything seems to be fine.
DBCC CHECKDB ([MyDB]) WITH ALL_ERRORMSGS, NO_INFOMSGS, MAXDOP = 1
My backup strategy is Full backup on Sunday midnight and Diff backups the rest of the days in a weekly cycle, also trn backups every 5 minutes.
So i thought maybe my diff backup from Wednesday is somehow corrupted because Tuesday restore was fine so just to make sure i restored a full backup from Sunday and diff backup from Wednesday on a different server, i ran DBCC but it returned the same error. Today i have the same problem after restoring Sunday Full + Thursday Diff backups.
Any thoughts?
What I understood from your post is that your production is free from any error on CHECKDB but when you restore it to another environment you are getting error.Can you try restoring the backup to a different drive and check ?
June 8, 2017 at 2:43 am
I've tried on 2 different servers
June 8, 2017 at 2:52 am
Could be that the full backup file was damaged in some way. Do you take your backups WITH CHECKSUM? Can you try a fresh backup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 8, 2017 at 2:59 am
Yes, I take my backups with checksum and RESTORE VERIFYONLY straight after that, I'm using Ola's scripts. I was thinking of taking another Full backup today after working hours.
I'm also trying one more thing - copy backups again from servers local drive to azure storage where i restore from.
June 8, 2017 at 10:24 am
As a weird thought, are all of your differential restores succeeding?
We do not do differential backups, but we do nightly fulls plus hourly t-logs. I have seen strange things happen if I restore the tlogs out of order OR if a tlog restore fails.
I would try restoring your full backup (not just with verifyonly) first to verify that the full backup is valid. It could be something goofy with your differentials.
I would be using "RESTORE VERIFYONLY WITH CHECKSUM" instead (if you aren't already) as this will also verify that the checksum is valid.
And you say you are storing your backups on Azure storage. Is it on local storage and then pushed to Azure after the backup completes or is it going directly from your local SQL instance to the Azure storage? I would recommend having it local and then moved to off-site (ie Azure) if that is not how you are currently doing it and it probably wouldn't hurt to generate an MD5 on the backups before and after copying them and then verify that the MD5 matches.
And I would also take a full backup during working hours. A slow server for a bit with a valid working full backup is a lot better than having your database crash and you are stuck with broken backups.
The minute I find out my backups are bad, I do a new full backup. Let things be slow... the company will be happy when they need that backup.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 8, 2017 at 10:52 pm
This was removed by the editor as SPAM
June 9, 2017 at 2:26 am
prettsons - Thursday, June 8, 2017 10:52 PMRestore the database from updated and clean backup. Check the database snapshot and if it exists then extract data from the snapshot.
Maybe read the thread before offering suggestions that don't match what's being asked.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2017 at 1:23 am
Hi,
Just to let you know, taking fresh FULL backup helped.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply