CHECK DB

Hi
When i execute dbcc checkdb in the database i am getting the error message "CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object."
Detail of the error messages are
Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
How to repair the Database .Plz help me
Thanks
Aravind
Thanks
Aravind
Aravind,
Refer DBCC CHECKDB in BooksOnLine.It lists options where you can repair the
data.It says:
" DBCC CHECKDB is the safest repair statement because it identifies and
repairs the widest possible errors. If only allocation errors are reported
for a database, execute DBCC CHECKALLOC with a repair option to repair these
errors. However, to ensure that all errors, including allocation errors, are
properly repaired, execute DBCC CHECKDB with a repair option rather than
DBCC CHECKALLOC with a repair option. "
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind
|||Aravind
Have you tried to use REPAIR option which is given by Microsoft?
DBCC CHECKDB
( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind
|||Make sure you have the updated books online, as it contains specific instructions on how to deal with
different DBCC errors. Your error number seems to be 8906. Here's an online link to the specific suggestions
for that error:
http://msdn.microsoft.com/library/de...err_2_6eye.asp
And here's a generic link on handling database corruption:
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error message "CHECKDB found 2 allocation
errors and 0 consistency errors not associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any
IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any
IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind

CHECK DB

H
When i execute dbcc checkdb in the database i am getting the error message "CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object."
Detail of the error messages ar
Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'
Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'
How to repair the Database .Plz help m
Thank
Aravin
Thank
AravindAravind,
Refer DBCC CHECKDB in BooksOnLine.It lists options where you can repair the
data.It says:
" DBCC CHECKDB is the safest repair statement because it identifies and
repairs the widest possible errors. If only allocation errors are reported
for a database, execute DBCC CHECKALLOC with a repair option to repair these
errors. However, to ensure that all errors, including allocation errors, are
properly repaired, execute DBCC CHECKDB with a repair option rather than
DBCC CHECKALLOC with a repair option. "
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind|||Aravind
Have you tried to use REPAIR option which is given by Microsoft?
DBCC CHECKDB
( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind|||Make sure you have the updated books online, as it contains specific instructions on how to deal with
different DBCC errors. Your error number seems to be 8906. Here's an online link to the specific suggestions
for that error:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_6eye.asp
And here's a generic link on handling database corruption:
http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error message "CHECKDB found 2 allocation
errors and 0 consistency errors not associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any
IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any
IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind

CHECK DB

Hi
When i execute dbcc checkdb in the database i am getting the error messag
e "CHECKDB found 2 allocation errors and 0 consistency errors not associated
with any single object."
Detail of the error messages are
Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1)
, but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_F
ULL'.
Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1)
, but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
How to repair the Database .Plz help me
Thanks
Aravind
Thanks
AravindAravind,
Refer DBCC CHECKDB in BooksOnLine.It lists options where you can repair the
data.It says:
" DBCC CHECKDB is the safest repair statement because it identifies and
repairs the widest possible errors. If only allocation errors are reported
for a database, execute DBCC CHECKALLOC with a repair option to repair these
errors. However, to ensure that all errors, including allocation errors, are
properly repaired, execute DBCC CHECKDB with a repair option rather than
DBCC CHECKALLOC with a repair option. "
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind|||Aravind
Have you tried to use REPAIR option which is given by Microsoft?
DBCC CHECKDB
( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error
message "CHECKDB found 2 allocation errors and 0 consistency errors not
associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS
(1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT
ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind|||Make sure you have the updated books online, as it contains specific instruc
tions on how to deal with
different DBCC errors. Your error number seems to be 8906. Here's an online
link to the specific suggestions
for that error:
serr_2_6eye.asp" target="_blank">http://msdn.microsoft.com/library/d...serr_2_6eye.asp
And here's a generic link on handling database corruption:
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aravind" <anonymous@.discussions.microsoft.com> wrote in message
news:B58E6708-2566-4D59-B567-669D3E1E829E@.microsoft.com...
> Hi
> When i execute dbcc checkdb in the database i am getting the error message "CHE
CKDB found 2 allocation
errors and 0 consistency errors not associated with any single object."
> Detail of the error messages are
> Page (1:3978) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but
was not allocated in any
IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
> Page (1:3979) in database ID 11 is allocated in the SGAM (1:3) and PFS (1:1), but
was not allocated in any
IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
> How to repair the Database .Plz help me
> Thanks
> Aravind
>
> Thanks
> Aravind

Check database healthiness on a daily basis?

How do people usually do to make sure all the databases are in good status?
We have about 50 databases. I was intended to write 'dbcc checkdb' for each one in a T-SQL script and have it run every day. Is there a better way to do that?
Thanks in advance for any advices.
Bing
Hi,
Yes, that will be a better option to check and confirm that your database is
good. DBCC CHECKDB will run for a long time if your database is
big. In that case probably you can do this activity weekly once during non
peak hours (weekends).
Along with this you can also run UPDATE STATISTICS daily on those tables
which have high DML access (Insert/ Update/ Delete).
Monthly once check the fragmentation of table using DBCC
SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
THis will remove the fragmentation and increase the performance.
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> How do people usually do to make sure all the databases are in good
status?
> We have about 50 databases. I was intended to write 'dbcc checkdb' for
each one in a T-SQL script and have it run every day. Is there a better way
to do that?
> Thanks in advance for any advices.
> Bing
|||Thanks so much for your instance response, Hari. Not just for this one. You have answered a lot of my questions I posted previously. They are all very helpful. I really appreciate your knowledgement and your kindness of willing to help others.
I'll try what you suggested.
Bing
"Hari Prasad" wrote:

> Hi,
> Yes, that will be a better option to check and confirm that your database is
> good. DBCC CHECKDB will run for a long time if your database is
> big. In that case probably you can do this activity weekly once during non
> peak hours (weekends).
> Along with this you can also run UPDATE STATISTICS daily on those tables
> which have high DML access (Insert/ Update/ Delete).
> Monthly once check the fragmentation of table using DBCC
> SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
> THis will remove the fragmentation and increase the performance.
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> status?
> each one in a T-SQL script and have it run every day. Is there a better way
> to do that?
>
>
|||Bing,
Something else to keep in mind if you have some large db's or ones that are
24 x 7. You can restore a full backup on another machine and run the DBCC's
there. If it is corrupted on the primary it will be corrupted there as well
and you don't have to disrupt anyone while doing the check.
Andrew J. Kelly SQL MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> Thanks so much for your instance response, Hari. Not just for this one.
You have answered a lot of my questions I posted previously. They are all
very helpful. I really appreciate your knowledgement and your kindness of
willing to help others.[vbcol=seagreen]
> I'll try what you suggested.
> Bing
> "Hari Prasad" wrote:
database is[vbcol=seagreen]
non[vbcol=seagreen]
for[vbcol=seagreen]
way[vbcol=seagreen]
|||Good point. Thanks for the advice, Andrew.
Bing
"Andrew J. Kelly" wrote:

> Bing,
> Something else to keep in mind if you have some large db's or ones that are
> 24 x 7. You can restore a full backup on another machine and run the DBCC's
> there. If it is corrupted on the primary it will be corrupted there as well
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
> for
> way
>
>
|||Hi Andrew - just curious... what would the reason be for moving this data to
another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
it just takes page locks?
Thanks for your help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> Bing,
> Something else to keep in mind if you have some large db's or ones that
are
> 24 x 7. You can restore a full backup on another machine and run the
DBCC's
> there. If it is corrupted on the primary it will be corrupted there as
well[vbcol=seagreen]
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
tables[vbcol=seagreen]
table.[vbcol=seagreen]
> for
better
> way
>
|||Besides the little bit of blocking CHECKDB is very resource intensive. It
can use a lot of CPU and potentially a lot of I/O. So if you have a large DB
to check you can hinder performance of other users if the load is great
enough. If you had a 500GB db and wanted to run CHECKDB it could take a
long time and potentially affect lots of users since it would most likely
run outside of a typical maintenance window. By running it on a nother
machine you might not care how long it takes and how much CPU, I/O etc it
takes. And you could specify the TABLOCK option to make it faster and more
comprehensive than on the production system. This is not something that
everyone needs to do but just wanted to mention it is an option for those
that need it.
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23yqn58idEHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi Andrew - just curious... what would the reason be for moving this data
to[vbcol=seagreen]
> another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
> it just takes page locks?
> Thanks for your help.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> are
> DBCC's
> well
one.[vbcol=seagreen]
all[vbcol=seagreen]
of[vbcol=seagreen]
during[vbcol=seagreen]
> tables
> table.
good[vbcol=seagreen]
checkdb'
> better
>