Extent is marked allocated in the GAM (Server: Msg 8905)

After running dbcc check db I am getting:
----
--
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:681848) in database ID 7 is marked allocated in the GAM, but no SG
AM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:681856) in database ID 7 is marked allocated in the GAM, but no SG
AM or IAM has allocated it.
.
.
.
.
CHECKDB found 2 allocation errors and 0 consistency errors in database.
repair_allow_data_loss is the minimum repair level for the errors found by D
BCC CHECKDB.
----
--
I also ran dbcc check with repair_allow_data_loss but it did not help me.
Thank you,
ktfoh dear.
Finding the object for that extent is at best a very difficult task.
I would first back up the corrupt database, then reorg it (rebuild the clust
ered indexes). If it is still not fixed it is very likely that the error is
on a heap table. I have had success adding clustered indexes to the heap t
ables and then dropping the
clustered index.
You final option may be to bcp all of the data out, empty all of the tables
and bcp it back in.|||With all due respect, none of what's below is relevant to this problem
(8905s) at all. The extent is marked allocated in the GAM but nothing is
actually using it. This also means its not available for anything to
allocate it because the allocation code already thinks its allocated.
There are no operations you can do to any of the tables which will affect
this extent - because none of the tables have it allocated and it's not
available for being allocated. The only way to fix this error is to restore
from your backups or to run repair. Ktf, which version of SQL Server are you
running on?
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:86D05800-3256-4674-8610-3A473386D7AE@.microsoft.com...
> oh dear.
> Finding the object for that extent is at best a very difficult task.
> I would first back up the corrupt database, then reorg it (rebuild the
clustered indexes). If it is still not fixed it is very likely that the
error is on a heap table. I have had success adding clustered indexes to
the heap tables and then dropping the clustered index.
> You final option may be to bcp all of the data out, empty all of the
tables and bcp it back in.|||It is a clustered MS SQL 2K (active/passive) with its twin passive site to u
se for disasters using double-take as the real time replication software. I
did restore from the back up several times for different reason but even I r
ecreated a blank database w
ith lookup data only but I every time this error stayed there even doing dbc
c with repair option on. This is a mid size but complex database and I had a
significant improvement in performance. Again I have everything working per
fect for long time except t
his allocation error.
For my blank db I suppose to have 400 MB but I could not have less than 5 GB
“I think this causes it” but again it does not cause any other problems
just probably allocating 4 GB empty space. Since this is a production envir
onment and I had so many si
tes migrated I can not restore from any of my backups because the I do not k
now when that occurred. I did everything you can think of to clear this erro
r but I could not.
Thank you
ktf|||Paul:
Any questions, comments are welcome.
ktf
---
It is a clustered MS SQL 2K (active/passive) with its twin passive site to u
se for disasters using double-take as the real time replication software. I
did restore from the back up several times for different reason but even I r
ecreated a blank database w
ith lookup data only but I every time this error stayed there even doing dbc
c with repair option on. This is a mid size but complex database and I had a
significant improvement in performance. Again I have everything working per
fect for long time except t
his allocation error.
For my blank db I suppose to have 400 MB but I could not have less than 5 GB
“I think this causes it” but again it does not cause any other problems
just probably allocating 4 GB empty space. Since this is a production envir
onment and I had so many si
tes migrated I can not restore from any of my backups because the I do not k
now when that occurred. I did everything you can think of to clear this erro
r but I could not.
Thank you
ktf|||ok - you've unfortunately run up against a corruption state that I know
cannot be repaired (there are many flavors of 8905 but one in particular
cannot be repaired). It also seems that the corruption happened a while ago
and is in your backups. I suggest you call PSS, when you get to the SQL
support engineers tell them that you've been told by Paul Randal in SQL
Development in Redmond (they know who I am) that this seems to be an
unfixable 8905 corruption and they will help you (ask them to confirm with
me over email).
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"ktf" <anonymous@.discussions.microsoft.com> wrote in message
news:05E009E5-201E-4D8D-9F2D-F402083A4D37@.microsoft.com...
> Paul:
> Any questions, comments are welcome.
> ktf
> ---
> It is a clustered MS SQL 2K (active/passive) with its twin passive site to
use for disasters using double-take as the real time replication software. I
did restore from the back up several times for different reason but even I
recreated a blank database with lookup data only but I every time this error
stayed there even doing dbcc with repair option on. This is a mid size but
complex database and I had a significant improvement in performance. Again I
have everything working perfect for long time except this allocation error.
> For my blank db I suppose to have 400 MB but I could not have less than 5
GB "I think this causes it" but again it does not cause any other problems
just probably allocating 4 GB empty space. Since this is a production
environment and I had so many sites migrated I can not restore from any of
my backups because the I do not know when that occurred. I did everything
you can think of to clear this error but I could not.
> Thank you
> ktf
>|||Paul,
We do have a Gold support service with Dell.
Should I go through them or Do you have the pss phone number?
Thank you,
ktf