April 15, 2016 at 3:24 am
Hi,
I have detached the database and cut paste .mdf and .ldf files to diffrent drive to create space.then i have also increased the space of that drive on server.
Now i am trying to attach that database but its not getting attached.
So i tried a method from internet (by creating empty DB and taking it offline and deleting and replacing dummy .mdf and .ldf files with my DB),now its attached but I cant make it online.
Somehow I managed to take the DB in Emergency mode,so i can view table but not columns.
No query is executing on that database.
Thanks,
Nallya.
April 15, 2016 at 3:30 am
Do you have a backup?
What are the error messages in the error log relating to this DB?
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 3:42 am
No,
For Select query its giving the error:
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:515235; actual 0:0). It occurred during a read of page (1:515235) in database ID 43 at offset 0x000000fb946000 in file 'G:\Data\XYZ.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
April 15, 2016 at 3:52 am
You've got corruption in your DB. Either the DB was corrupt before you tried moving it and you didn't notice, or it was damaged in the move.
What are the errors related to this database in the errorlog?
Do you have a 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
April 15, 2016 at 4:01 am
Hi,
I don't have backup.
This is the first error i got while attaching.
But i can expand and view anything in database object explorer.only tables columns and indexes r not visible.
Thanks.
April 15, 2016 at 4:05 am
GilaMonster (4/15/2016)
What are the errors related to this database in the errorlog?
In addition to getting those errors (which I really need to see), please run the following (exactly that command with no alterations) on that DB (in Emergency mode) and post the full, complete and unedited results.
DBCC CheckDB('<database name here>') WITH NO_INFOMSGS, ALL_ERRORMSGS
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 4:09 am
Hi,
the most appered error is the one i posted some time ago.
I am getting diffrent errors as i am querying diffrent things on it.
Thanks,
Swapnil.
April 15, 2016 at 4:14 am
I am not asking for the errors you get when querying. I need to see messages relating to this database from the error log. They are not the same thing. Please open the error log, find all messages relating to this database, and post them.
Once you've done that, please run the command I just gave you, and post the complete output.
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 4:39 am
For above Query The error looks like.
I am trying since last 3 days so my error log has 2124 errors ,i cant post that much here.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:388224) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:388224) to (1:396311). 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 0 (type Unknown), page ID (1:396312) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:396312) to (1:404399). 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 0 (type Unknown), page ID (1:404400) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:404400) to (1:412487). 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 0 (type Unknown), page ID (1:412488) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:412488) to (1:420575). 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 0 (type Unknown), page ID (1:420576) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:420576) to (1:428663). 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 0 (type Unknown), page ID (1:428664) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:428664) to (1:436751). 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 0 (type Unknown), page ID (1:436752) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:436752) to (1:444839). 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 0 (type Unknown), page ID (1:444840) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:444840) to (1:452927). 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 0 (type Unknown), page ID (1:452928) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:452928) to (1:461015). 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 0 (type Unknown), page ID (1:461016) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:461016) to (1:469103). 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 0 (type Unknown), page ID (1:469104) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:469104) to (1:477191). 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 0 (type Unknown), page ID (1:477192) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:477192) to (1:485279). 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 0 (type Unknown), page ID (1:485280) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:485280) to (1:493367). 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 0 (type Unknown), page ID (1:493368) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:493368) to (1:501455). 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 0 (type Unknown), page ID (1:501456) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:501456) to (1:509543). 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 0 (type Unknown), page ID (1:509544) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:509544) to (1:517631). 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 0 (type Unknown), page ID (1:511232) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:509544) to (1:1027175). 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 0 (type Unknown), page ID (1:511233) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 43 pages from (1:509544) to (1:1027175). 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 0 (type Unknown), page ID (1:511238) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:511239) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:509544) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
CHECKDB found 38 allocation errors and 1 consistency errors not associated with any single object.
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (1:515239) is pointed to by the next pointer of IAM page (1:45) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data), but it was not detected in the scan.
CHECKDB found 1 allocation errors and 0 consistency errors in table 'sys.sysobjvalues' (object ID 60).
CHECKDB found 39 allocation errors and 1 consistency errors in database 'XYX'.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:515993; actual 0:0). It occurred during a read of page (1:515993) in database ID 43 at offset 0x000000fbf32000 in file 'G:\Data\XYX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:515995; actual 0:0). It occurred during a read of page (1:515995) in database ID 43 at offset 0x000000fbf36000 in file 'G:\Data\XYX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:515995; actual 0:0). It occurred during a read of page (1:515995) in database ID 43 at offset 0x000000fbf36000 in file 'G:\Data\XYX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:515994; actual 0:0). It occurred during a read of page (1:515994) in database ID 43 at offset 0x000000fbf34000 in file 'G:\Data\XYX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:515994; actual 0:0). It occurred during a read of page (1:515994) in database ID 43 at offset 0x000000fbf34000 in file 'G:\Data\XYX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:515992; actual 0:0). It occurred during a read of page (1:515992) in database ID 43 at offset 0x000000fbf30000 in file 'G:\Data\XYX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:515992; actual 0:0). It occurred during a read of page (1:515992) in database ID 43 at offset 0x000000fbf30000 in file 'G:\Data\XYX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:515992; actual 0:0). It occurred during a read of page (1:515992) in database ID 43 at offset 0x000000fbf30000 in file 'G:\Data\XYX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:515992; actual 0:0). It occurred during a read of page (1:515992) in database ID 43 at offset 0x000000fbf30000 in file 'G:\Data\XYX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:515992; actual 0:0). It occurred during a read of page (1:515992) in database ID 43 at offset 0x000000fbf30000 in file 'G:\Data\XYX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:515992; actual 0:0). It occurred during a read of page (1:515992) in database ID 43 at offset 0x000000fbf30000 in file 'G:\Data\XYX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:515992; actual 0:0). It occurred during a read of page (1:515992) in database ID 43 at offset 0x000000fbf30000 in file 'G:\Data\XYX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:515992; actual 0:0). It occurred during a read of page (1:515992) in database ID 43 at offset 0x000000fbf30000 in file 'G:\Data\XYX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:515235; actual 0:0). It occurred during a read of page (1:515235) in database ID 43 at offset 0x000000fb946000 in file 'G:\Data\XYX.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
April 15, 2016 at 4:47 am
What's the latest backup you have?
Because something (bad IO subsystem usually) has done a lot of damage to that file, it's not repairable.
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 4:52 am
Hi,
I this is the last detached mdf file i have.
Is it repairable by any tool or any other way.
Thanks.
April 15, 2016 at 6:31 am
Probably not. From the errors a portion of the data file has been overwritten by zeros. Whatever was originally there is lost for good.
You have several problems (excluding the fact that this DB has probably been lost without recovery possible). A lack of backups and a misbehaving IO subsystem. I strongly suggest that, when you explain this mess to your boss, you emphasise the need to hire a DBA.
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:33 am
Unfortunately you are learning the hard way the value of taking regular backups.
When you moved the mdf and ldf files did you cut and paste or copy and paste? Reason I'm asking is I want to see if the original pre-move mdf and ldf files still exist and if you can attach those and create a backup.
April 15, 2016 at 6:39 am
Backups. What are they good for?[/url]
and, more recently:
How to recover from deleting everything, including the backups
I sincerely hope that this data is not vital to your organization, because it's gone.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 15, 2016 at 7:39 am
Grant Fritchey (4/15/2016)
Backups. What are they good for?[/url]and, more recently:
How to recover from deleting everything, including the backups
I sincerely hope that this data is not vital to your organization, because it's gone.
Grant, your second link is gone.
This question was removed from Server Fault for reasons of moderation. Please refer to the help center for possible explanations why a question might be removed.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply