April 15, 2016 at 6:42 am
Grumpy DBA (4/15/2016)
GilaMonster (4/15/2016)
Brandie Tarvin (4/15/2016)
GilaMonster (4/15/2016)
*sigh* http://www.sqlservercentral.com/Forums/Topic1778081-433-1.aspxI don't have backup.
How does one get incorrect page IDs in the headers?
Seriously. I'm trying to figure out how that one is possible.
Pages zeroed out by the IO subsystem. Literally portion of the file overwritten by zeros by misbehaving IO subsystem
If the OP copied the files from the original location to the new location instead of cutting and pasting the files he/she can re-attach the original files and take a backup. But he/she said the files were cut and pasted...
Depends when in the process the files were damaged.
What's the bet that he was doing that because of a drive failure or similar?
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
April 15, 2016 at 6:46 am
That is a pretty severe failure, it sounds like. I can't say I have ever seen a SAN LUN fail to that degree. What do we want to bet too that the hardware is substandard? Maybe even local storage?
April 15, 2016 at 6:49 am
jeff.mason (4/15/2016)
I can't say I have ever seen a SAN LUN fail to that degree.
It happens. Not common, but it happens. I've seen a case where a SAN controller failed and scrambled the LUNs it controlled. Rare, but far from impossible.
Could also have been filter drives, HBA drivers/firmware, switch, etc, etc, etc, etc.
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
April 15, 2016 at 6:51 am
In any case, that is why ensuring backup success is just about the most important task on a DBA's list of to-do's. Still cannot fathom a system without any backups at all.
April 15, 2016 at 6:55 am
GilaMonster (4/15/2016)
Grumpy DBA (4/15/2016)
GilaMonster (4/15/2016)
Brandie Tarvin (4/15/2016)
GilaMonster (4/15/2016)
*sigh* http://www.sqlservercentral.com/Forums/Topic1778081-433-1.aspxI don't have backup.
How does one get incorrect page IDs in the headers?
Seriously. I'm trying to figure out how that one is possible.
Pages zeroed out by the IO subsystem. Literally portion of the file overwritten by zeros by misbehaving IO subsystem
If the OP copied the files from the original location to the new location instead of cutting and pasting the files he/she can re-attach the original files and take a backup. But he/she said the files were cut and pasted...
Depends when in the process the files were damaged.
What's the bet that he was doing that because of a drive failure or similar?
That sounds like a pretty safe bet. I feel bad for them, but I don't see any options without any backups.
For errors in the PFS, GAM, SGAM or IAM pages, are there any viable options for recovery without going to a backup?
April 15, 2016 at 6:56 am
Ed Wagner (4/15/2016)
Brandie Tarvin (4/15/2016)
GilaMonster (4/15/2016)
*sigh* http://www.sqlservercentral.com/Forums/Topic1778081-433-1.aspxI don't have backup.
How does one get incorrect page IDs in the headers?
Seriously. I'm trying to figure out how that one is possible.
I think you're looking for DBCC WRITEPAGE. Please first connect to dev and be careful.
I choose instead to look it up in BOL before doing anything of the sort. So NAH.
But thank you for pointing this out. I'll go grab documentation and see what is out there.
April 15, 2016 at 6:57 am
GilaMonster (4/15/2016)
Brandie Tarvin (4/15/2016)
GilaMonster (4/15/2016)
*sigh* http://www.sqlservercentral.com/Forums/Topic1778081-433-1.aspxI don't have backup.
How does one get incorrect page IDs in the headers?
Seriously. I'm trying to figure out how that one is possible.
Pages zeroed out by the IO subsystem. Literally portion of the file overwritten by zeros by misbehaving IO subsystem
YIKES. So that would include the page IDs I assume. YIKES again.
April 15, 2016 at 6:58 am
GilaMonster (4/15/2016)
Brandie Tarvin (4/15/2016)
BLOB_EATER (4/15/2016)
GilaMonster (4/15/2016)
*sigh* http://www.sqlservercentral.com/Forums/Topic1778081-433-1.aspxI don't have backup.
I read "by creating empty DB and taking it offline and deleting and replacing dummy .mdf and .ldf files with my DB".... and just started to shake my head.
I'm trying to figure out where he got that bit of advice so I can avoid that site.
Paul Randal's blog, pluralsight courses, articles and PASS presentations.
It's the last ditch solution for attaching a damaged DB or one where the log file was lost.
Ah, well Paul's a pretty knowledgeable guy. Won't avoid him. I thought the advice came from someone who said things something along the lines of "Hey, here's a quick fix. Use NO_LOCK!"
April 15, 2016 at 7:02 am
GilaMonster (4/15/2016)
Grumpy DBA (4/15/2016)
If the OP copied the files from the original location to the new location instead of cutting and pasting the files he/she can re-attach the original files and take a backup. But he/she said the files were cut and pasted...
Depends when in the process the files were damaged.
What's the bet that he was doing that because of a drive failure or similar?
True, although the OP referred to detaching the files (why are mdf and ldf files on the same drive?), copying and pasting to another drive to "create space" and then he/she increased the space on that drive - with the goal of moving the mdf and ldf files back to the original drive? Not enough detail (and not enough coffee yet for me).
The OP is no longer online so he/she:
1) resolved the issue (doubtful based on what I have read)
2) given up and resigned
3) told management "I'm an accidental DBA and not prepared for this type of disaster"
4) said "I'll deal with this tomorrow", in which case the database cannot be too important
April 15, 2016 at 7:08 am
Grumpy DBA (4/15/2016)
True, although the OP referred to detaching the files (why are mdf and ldf files on the same drive?), copying and pasting to another drive to "create space" and then he/she increased the space on that drive - with the goal of moving the mdf and ldf files back to the original drive? Not enough detail (and not enough coffee yet for me).
The OP is no longer online so he/she:
1) resolved the issue (doubtful based on what I have read)
2) given up and resigned
3) told management "I'm an accidental DBA and not prepared for this type of disaster"
4) said "I'll deal with this tomorrow", in which case the database cannot be too important
mdf and ldf on the same drive happens all the time. Of course when your SAN is configured as one big lun having the mdf's and ldf's on different logical drives doesn't make that much difference (my current location). Fortunately I'm a dev, not a DBA at my current job.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2016 at 7:36 am
Brandie Tarvin (4/15/2016)
GilaMonster (4/15/2016)
Brandie Tarvin (4/15/2016)
GilaMonster (4/15/2016)
*sigh* http://www.sqlservercentral.com/Forums/Topic1778081-433-1.aspxI don't have backup.
How does one get incorrect page IDs in the headers?
Seriously. I'm trying to figure out how that one is possible.
Pages zeroed out by the IO subsystem. Literally portion of the file overwritten by zeros by misbehaving IO subsystem
YIKES. So that would include the page IDs I assume. YIKES again.
Sure. The page ID is stamped into the header (The DB engine can still work out the page number, because it's related to the offset into the file, but if the page that claims to be page 200 is not at 200*8k offset into the file, then the page header is damaged). Also in the page header are the number of rows, what allocation unit the page belongs to, what the next page is in the page order, what the parent page is, how much free space is on the page etc. Then comes the actual data on the page.
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
April 15, 2016 at 7:36 am
Jack Corbett (4/15/2016)
Grumpy DBA (4/15/2016)
(why are mdf and ldf files on the same drive?)mdf and ldf on the same drive happens all the time. Of course when your SAN is configured as one big lun having the mdf's and ldf's on different logical drives doesn't make that much difference (my current location). Fortunately I'm a dev, not a DBA at my current job.
This is true of our 2k8 servers, which were on corporate controlled PODS. With our 2k12 upgrade, we're going back to servers "owned" and managed by our local division which have the drives separated out. But given how many of our servers are virtual, who's to know if these really are separate drives (physical) or just imaginary drives that are on the same drive (logical).
If I eat enough mushrooms, may I can hallucinate corporate infrastructure granting us a different drive for each database. That would be nice. :w00t:
(munching on a slice spinach and mushroom omelet casserole this morning).
April 15, 2016 at 7:41 am
Ed Wagner (4/15/2016)
For errors in the PFS, GAM, SGAM or IAM pages, are there any viable options for recovery without going to a backup?
Depends on how widespread. If just a single GAM/SGAM/PFS, it's probably possible to still select from the tables in that interval and script that data out.
With this one, where multiple GAM/SGAM pages in a row are marked as unreadable, it's a good bet that the 2GB between them is also damaged, and in that case probably not.
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
April 15, 2016 at 7:56 am
Brandie Tarvin (4/15/2016)
Jack Corbett (4/15/2016)
Grumpy DBA (4/15/2016)
(why are mdf and ldf files on the same drive?)mdf and ldf on the same drive happens all the time. Of course when your SAN is configured as one big lun having the mdf's and ldf's on different logical drives doesn't make that much difference (my current location). Fortunately I'm a dev, not a DBA at my current job.
This is true of our 2k8 servers, which were on corporate controlled PODS. With our 2k12 upgrade, we're going back to servers "owned" and managed by our local division which have the drives separated out. But given how many of our servers are virtual, who's to know if these really are separate drives (physical) or just imaginary drives that are on the same drive (logical).
If I eat enough mushrooms, may I can hallucinate corporate infrastructure granting us a different drive for each database. That would be nice. :w00t:
(munching on a slice spinach and mushroom omelet casserole this morning).
Even though all my servers are VMs, when I request them I still go for separate drives (I've gone one step further than in the past, too, with my new 2012 servers: OS drive, App drive, Filestream drive (if needed,) MDF drive, LDF drive, and Audit file drive) mostly for logical separation. Plus, if one drive starts filling up, I don't have to worry as much about the VMware / Storage admins whining about having to cram a humongous (by *their* definition) virtual HD into a LUN...
April 15, 2016 at 8:05 am
While I still typically request split drives (old habits die hard), if you are given SSD storage that all uses the same controller no matter what, is there any actual practical performance reason to do so? I would think that SSD through one channel would look no different for one drive vs two.
Viewing 15 posts - 53,551 through 53,565 (of 66,749 total)
You must be logged in to reply to this topic. Login to reply