Is there a way to simulate maintenance plan option for
integrity checking called: attempt to repair minor
problems using some dbcc option preferably in mutli user
mode for MSSQL2000.Milan
The command you want is DBCC CHECKDB. It allows three
levels of repair. See Books on line for format and options.
Regards
John|||However, please be aware of two things:
1) you cannot run repair in multi-user mode - the database must be in
single-user mode
2) you should not automatically repair errors. You should always work out
why an error happened (so you can prevent it in future) and whether the
repair may lose some of your data. A preferable strategem is always to
restore from your most recent backups.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:079201c3502d$575fc370$a601280a@.phx.gbl...
> Milan
> The command you want is DBCC CHECKDB. It allows three
> levels of repair. See Books on line for format and options.
> Regards
> John
Showing posts with label checking. Show all posts
Showing posts with label checking. Show all posts
Wednesday, March 21, 2012
Friday, February 17, 2012
DBCC CHECKDB Error
I am getting the following error after checking the database with DBCC
CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
option. But it is not repairing. I am not a professional DBA please advise
what to do.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:5604) in database ID 7 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:5604) with latch type SH. VerifyPageId
failed.
Hi,
Best solution will be to restore the database from a healthy backup and to
analyze further open a case with Microsoft product support.
These errors occurs when there is a Hardware issue, so contact your hardware
vendor and ask them to do a hardware diagonostic.
Replace the hardware if any fault is rectified
Thanks
Hari
SQL Server MVP
"Sarma" <Sarma@.discussions.microsoft.com> wrote in message
news:C9AD329A-1066-482E-AAC8-1248BD70FAE8@.microsoft.com...
>I am getting the following error after checking the database with DBCC
> CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
> option. But it is not repairing. I am not a professional DBA please advise
> what to do.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:5604) in database ID 7 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'.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:5604) with latch type SH. VerifyPageId
> failed.
|||Here are my general recommendations for these types of situations.
Also, below is from Books Online, specific for this error number:
Severity Level 16
Message Text
Page P_ID in database ID DB_ID is allocated in the SGAM SGAM_P_ID and PFS PFS_P_ID, but was not
allocated in any IAM. PFS flags 'PFS_FLAGS'.
Explanation
A PFS page has one byte per page for all pages in the file interval it maps (approximately 64 Mb).
Each byte contains, among other things, a bit that, when set, indicates that the page is from a
mixed extent (that is, it is a mixed page).
An IAM page has, among other things, an array of eight page IDs (called the mixed page array) to
hold the mixed pages allocated to the index. Note that IAM pages themselves are mixed pages, but IAM
pages are not self-referential (that is, the IAM page ID itself is not stored in its own mixed page
array).
This message means that a page has the 'mixed page' bit set in its PFS byte, but the page was not
seen as an IAM page and was not seen in an IAM page's mixed page array.
Action
HARDWARE FAILURE
Run hardware diagnostics and correct any problems. Also examine the Microsoft Windows NT system
and application logs and the SQL ServerT error log to see if the error occurred as the result of
hardware failure. Fix any hardware related problems.
If you have persistent data corruption problems, try to swap out different hardware components to
isolate the problem. Check to ensure that your system does not have write caching enabled on the
disk controller. If you suspect this to be the problem, contact your hardware vendor.
Finally, you might find it beneficial to switch to a completely new hardware system, including
reformatting the disk drives and reinstalling the operating system.
RESTORE FROM BACKUP
If the problem is not hardware related and a known clean backup is available, restore the database
from the backup.
DBCC CHECKDB
If no clean backup is available, execute DBCC CHECKDB without a repair clause to determine the
extent of the corruption. DBCC CHECKDB will recommend a repair clause to use. Then, execute DBCC
CHECKDB with the appropriate repair clause to repair the corruption.
Caution If you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact
your primary support provider before executing this statement.
If the page is an IAM page, performing a repair will de-allocate the page, and the IAM chain will be
patched by the repairs for accompanying errors.
Otherwise, a general purpose routine to repair doubly allocated pages is used. It takes two sets of
IDs, one for each of the two indexes (IAM chains) that have the page allocated. In this case, we use
the IDs derived from the page itself, and an invalid set of IDs.
The algorithm of the general purpose routine is as follows:
1.. Check page validity.
a.. If the page is not a valid page ID in the database, de-allocate it from both indexes and
return.
b.. If the page is corrupt, de-allocate it from both indexes, then return.
2.. Work out which index the page actually belongs to.
3.. Check for the allocation page.
a.. If the page is an IAM page or allocation page, de-allocate it from both indexes passed in.
b.. If the page is an IAM page and the index it thinks it belongs to cannot be found,
de-allocate the page.
c.. Return.
4.. If the page belongs to the first index passed in:
a.. De-allocate it from the second index passed in.
b.. Try to allocate it to the first index. If this fails, de-allocate the page.
c.. Return.
Note We should not get below here for this particular error.
5.. De-allocate the page from the first index.
6.. If the page does not belong to second index:
a.. If the second index is not invalid, de-allocate the page from the second index.
b.. Try to allocate the page to the real owning index. If it fails, de-allocate the page.
c.. Return.
7.. Page must belong to second index passed in.
a.. Try to allocate the page to the second index. If it fails, de-allocate the page.
b.. Return.
Caution This repair may cause data loss.
If running DBCC CHECKDB with one of the repair clauses does not correct the problem, contact your
primary support provider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sarma" <Sarma@.discussions.microsoft.com> wrote in message
news:C9AD329A-1066-482E-AAC8-1248BD70FAE8@.microsoft.com...
>I am getting the following error after checking the database with DBCC
> CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
> option. But it is not repairing. I am not a professional DBA please advise
> what to do.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:5604) in database ID 7 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'.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:5604) with latch type SH. VerifyPageId
> failed.
begin 666 caution.gif
M1TE&.#EA# `+`/<``````#,``&8``)D``,P``/\````S`#,S`&8S`)DS`,PS
M`/\S``!F`#-F`&9F`)EF`,QF`/]F``"9`#.9`&:9`)F9`,R9`/^9``#,`#/,
M`&;,`)G,`,S,`/_,``#_`#/_`&;_`)G_`,S_`/__````,S,`,V8`,YD`,\P`
M,_\`,P`S,S,S,V8S,YDS,\PS,_\S,P!F,S-F,V9F,YEF,\QF,_]F,P"9,S.9
M,V:9,YF9,\R9,_^9,P#,,S/,,V;,,YG,,\S,,__,,P#_,S/_,V;_,YG_,\S_
M,___,P``9C,`9F8`9ID`9LP`9O\`9@.`S9C,S9F8S9IDS9LPS9 O\S9@.!F9C-F
M9F9F9IEF9LQF9O]F9@."99C.99F:99IF99LR99O^99@.#,9C/,9F;,9IG,9LS,
M9O_,9@.#_9C/_9F;_9IG_9LS_9O__9@.``F3,`F68`F9D`F<P`F?\`F0`SF3,S
MF68SF9DSF<PSF?\SF0!FF3-FF69FF9EFF<QFF?]FF0"9F3.9F6:9F9F9F<R9
MF?^9F0#,F3/,F6;,F9G,F<S,F?_,F0#_F3/_F6;_F9G_F<S_F?__F0``S#,`
MS&8`S)D`S,P`S/\`S `SS#,SS&8SS)DSS,PSS/\SS !FS#-FS&9FS)EFS,QF
MS/]FS "9S#.9S&:9S)F9S,R9S/^9S #,S#/,S&;,S)G,S,S,S/_,S #_S#/_
MS&;_S)G_S,S_S/__S ``_S,`_V8`_YD`_\P`__\`_P`S_S,S_V8S_YDS_\PS
M__\S_P!F_S-F_V9F_YEF_\QF__]F_P"9_S.9_V:9_YF9_\R9__^9_P#,_S/,
M_V;,_YG,_\S,___,_P#__S/__V;__YG__\S______P``````````````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M`````````````````````"'Y! $``*P`+ `````,``L`0 @.B`%D)'$BPH,&#
;K HH1)AP(4*%!1A"9$A1X,2'#BMBC#@.P( `[
`
end
CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
option. But it is not repairing. I am not a professional DBA please advise
what to do.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:5604) in database ID 7 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:5604) with latch type SH. VerifyPageId
failed.
Hi,
Best solution will be to restore the database from a healthy backup and to
analyze further open a case with Microsoft product support.
These errors occurs when there is a Hardware issue, so contact your hardware
vendor and ask them to do a hardware diagonostic.
Replace the hardware if any fault is rectified
Thanks
Hari
SQL Server MVP
"Sarma" <Sarma@.discussions.microsoft.com> wrote in message
news:C9AD329A-1066-482E-AAC8-1248BD70FAE8@.microsoft.com...
>I am getting the following error after checking the database with DBCC
> CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
> option. But it is not repairing. I am not a professional DBA please advise
> what to do.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:5604) in database ID 7 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'.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:5604) with latch type SH. VerifyPageId
> failed.
|||Here are my general recommendations for these types of situations.
Also, below is from Books Online, specific for this error number:
Severity Level 16
Message Text
Page P_ID in database ID DB_ID is allocated in the SGAM SGAM_P_ID and PFS PFS_P_ID, but was not
allocated in any IAM. PFS flags 'PFS_FLAGS'.
Explanation
A PFS page has one byte per page for all pages in the file interval it maps (approximately 64 Mb).
Each byte contains, among other things, a bit that, when set, indicates that the page is from a
mixed extent (that is, it is a mixed page).
An IAM page has, among other things, an array of eight page IDs (called the mixed page array) to
hold the mixed pages allocated to the index. Note that IAM pages themselves are mixed pages, but IAM
pages are not self-referential (that is, the IAM page ID itself is not stored in its own mixed page
array).
This message means that a page has the 'mixed page' bit set in its PFS byte, but the page was not
seen as an IAM page and was not seen in an IAM page's mixed page array.
Action
HARDWARE FAILURE
Run hardware diagnostics and correct any problems. Also examine the Microsoft Windows NT system
and application logs and the SQL ServerT error log to see if the error occurred as the result of
hardware failure. Fix any hardware related problems.
If you have persistent data corruption problems, try to swap out different hardware components to
isolate the problem. Check to ensure that your system does not have write caching enabled on the
disk controller. If you suspect this to be the problem, contact your hardware vendor.
Finally, you might find it beneficial to switch to a completely new hardware system, including
reformatting the disk drives and reinstalling the operating system.
RESTORE FROM BACKUP
If the problem is not hardware related and a known clean backup is available, restore the database
from the backup.
DBCC CHECKDB
If no clean backup is available, execute DBCC CHECKDB without a repair clause to determine the
extent of the corruption. DBCC CHECKDB will recommend a repair clause to use. Then, execute DBCC
CHECKDB with the appropriate repair clause to repair the corruption.
Caution If you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact
your primary support provider before executing this statement.
If the page is an IAM page, performing a repair will de-allocate the page, and the IAM chain will be
patched by the repairs for accompanying errors.
Otherwise, a general purpose routine to repair doubly allocated pages is used. It takes two sets of
IDs, one for each of the two indexes (IAM chains) that have the page allocated. In this case, we use
the IDs derived from the page itself, and an invalid set of IDs.
The algorithm of the general purpose routine is as follows:
1.. Check page validity.
a.. If the page is not a valid page ID in the database, de-allocate it from both indexes and
return.
b.. If the page is corrupt, de-allocate it from both indexes, then return.
2.. Work out which index the page actually belongs to.
3.. Check for the allocation page.
a.. If the page is an IAM page or allocation page, de-allocate it from both indexes passed in.
b.. If the page is an IAM page and the index it thinks it belongs to cannot be found,
de-allocate the page.
c.. Return.
4.. If the page belongs to the first index passed in:
a.. De-allocate it from the second index passed in.
b.. Try to allocate it to the first index. If this fails, de-allocate the page.
c.. Return.
Note We should not get below here for this particular error.
5.. De-allocate the page from the first index.
6.. If the page does not belong to second index:
a.. If the second index is not invalid, de-allocate the page from the second index.
b.. Try to allocate the page to the real owning index. If it fails, de-allocate the page.
c.. Return.
7.. Page must belong to second index passed in.
a.. Try to allocate the page to the second index. If it fails, de-allocate the page.
b.. Return.
Caution This repair may cause data loss.
If running DBCC CHECKDB with one of the repair clauses does not correct the problem, contact your
primary support provider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sarma" <Sarma@.discussions.microsoft.com> wrote in message
news:C9AD329A-1066-482E-AAC8-1248BD70FAE8@.microsoft.com...
>I am getting the following error after checking the database with DBCC
> CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
> option. But it is not repairing. I am not a professional DBA please advise
> what to do.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:5604) in database ID 7 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'.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:5604) with latch type SH. VerifyPageId
> failed.
begin 666 caution.gif
M1TE&.#EA# `+`/<``````#,``&8``)D``,P``/\````S`#,S`&8S`)DS`,PS
M`/\S``!F`#-F`&9F`)EF`,QF`/]F``"9`#.9`&:9`)F9`,R9`/^9``#,`#/,
M`&;,`)G,`,S,`/_,``#_`#/_`&;_`)G_`,S_`/__````,S,`,V8`,YD`,\P`
M,_\`,P`S,S,S,V8S,YDS,\PS,_\S,P!F,S-F,V9F,YEF,\QF,_]F,P"9,S.9
M,V:9,YF9,\R9,_^9,P#,,S/,,V;,,YG,,\S,,__,,P#_,S/_,V;_,YG_,\S_
M,___,P``9C,`9F8`9ID`9LP`9O\`9@.`S9C,S9F8S9IDS9LPS9 O\S9@.!F9C-F
M9F9F9IEF9LQF9O]F9@."99C.99F:99IF99LR99O^99@.#,9C/,9F;,9IG,9LS,
M9O_,9@.#_9C/_9F;_9IG_9LS_9O__9@.``F3,`F68`F9D`F<P`F?\`F0`SF3,S
MF68SF9DSF<PSF?\SF0!FF3-FF69FF9EFF<QFF?]FF0"9F3.9F6:9F9F9F<R9
MF?^9F0#,F3/,F6;,F9G,F<S,F?_,F0#_F3/_F6;_F9G_F<S_F?__F0``S#,`
MS&8`S)D`S,P`S/\`S `SS#,SS&8SS)DSS,PSS/\SS !FS#-FS&9FS)EFS,QF
MS/]FS "9S#.9S&:9S)F9S,R9S/^9S #,S#/,S&;,S)G,S,S,S/_,S #_S#/_
MS&;_S)G_S,S_S/__S ``_S,`_V8`_YD`_\P`__\`_P`S_S,S_V8S_YDS_\PS
M__\S_P!F_S-F_V9F_YEF_\QF__]F_P"9_S.9_V:9_YF9_\R9__^9_P#,_S/,
M_V;,_YG,_\S,___,_P#__S/__V;__YG__\S______P``````````````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M`````````````````````"'Y! $``*P`+ `````,``L`0 @.B`%D)'$BPH,&#
;K HH1)AP(4*%!1A"9$A1X,2'#BMBC#@.P( `[
`
end
Tuesday, February 14, 2012
DBCC CHECKDB Error
I am getting the following error after checking the database with DBCC
CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
option. But it is not repairing. I am not a professional DBA please advise
what to do.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:5604) in database ID 7 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:5604) with latch type SH. VerifyPageId
failed.Hi,
Best solution will be to restore the database from a healthy backup and to
analyze further open a case with Microsoft product support.
These errors occurs when there is a Hardware issue, so contact your hardware
vendor and ask them to do a hardware diagonostic.
Replace the hardware if any fault is rectified
Thanks
Hari
SQL Server MVP
"Sarma" <Sarma@.discussions.microsoft.com> wrote in message
news:C9AD329A-1066-482E-AAC8-1248BD70FAE8@.microsoft.com...
>I am getting the following error after checking the database with DBCC
> CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
> option. But it is not repairing. I am not a professional DBA please advise
> what to do.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:5604) in database ID 7 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'.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:5604) with latch type SH. VerifyPageId
> failed.|||Here are my general recommendations for these types of situations.
Also, below is from Books Online, specific for this error number:
Severity Level 16
Message Text
Page P_ID in database ID DB_ID is allocated in the SGAM SGAM_P_ID and PFS PFS_P_ID, but was not
allocated in any IAM. PFS flags 'PFS_FLAGS'.
Explanation
A PFS page has one byte per page for all pages in the file interval it maps (approximately 64 Mb).
Each byte contains, among other things, a bit that, when set, indicates that the page is from a
mixed extent (that is, it is a mixed page).
An IAM page has, among other things, an array of eight page IDs (called the mixed page array) to
hold the mixed pages allocated to the index. Note that IAM pages themselves are mixed pages, but IAM
pages are not self-referential (that is, the IAM page ID itself is not stored in its own mixed page
array).
This message means that a page has the 'mixed page' bit set in its PFS byte, but the page was not
seen as an IAM page and was not seen in an IAM page's mixed page array.
Action
HARDWARE FAILURE
Run hardware diagnostics and correct any problems. Also examine the Microsoft® Windows NT® system
and application logs and the SQL ServerT error log to see if the error occurred as the result of
hardware failure. Fix any hardware related problems.
If you have persistent data corruption problems, try to swap out different hardware components to
isolate the problem. Check to ensure that your system does not have write caching enabled on the
disk controller. If you suspect this to be the problem, contact your hardware vendor.
Finally, you might find it beneficial to switch to a completely new hardware system, including
reformatting the disk drives and reinstalling the operating system.
RESTORE FROM BACKUP
If the problem is not hardware related and a known clean backup is available, restore the database
from the backup.
DBCC CHECKDB
If no clean backup is available, execute DBCC CHECKDB without a repair clause to determine the
extent of the corruption. DBCC CHECKDB will recommend a repair clause to use. Then, execute DBCC
CHECKDB with the appropriate repair clause to repair the corruption.
Caution If you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact
your primary support provider before executing this statement.
If the page is an IAM page, performing a repair will de-allocate the page, and the IAM chain will be
patched by the repairs for accompanying errors.
Otherwise, a general purpose routine to repair doubly allocated pages is used. It takes two sets of
IDs, one for each of the two indexes (IAM chains) that have the page allocated. In this case, we use
the IDs derived from the page itself, and an invalid set of IDs.
The algorithm of the general purpose routine is as follows:
1.. Check page validity.
a.. If the page is not a valid page ID in the database, de-allocate it from both indexes and
return.
b.. If the page is corrupt, de-allocate it from both indexes, then return.
2.. Work out which index the page actually belongs to.
3.. Check for the allocation page.
a.. If the page is an IAM page or allocation page, de-allocate it from both indexes passed in.
b.. If the page is an IAM page and the index it thinks it belongs to cannot be found,
de-allocate the page.
c.. Return.
4.. If the page belongs to the first index passed in:
a.. De-allocate it from the second index passed in.
b.. Try to allocate it to the first index. If this fails, de-allocate the page.
c.. Return.
Note We should not get below here for this particular error.
5.. De-allocate the page from the first index.
6.. If the page does not belong to second index:
a.. If the second index is not invalid, de-allocate the page from the second index.
b.. Try to allocate the page to the real owning index. If it fails, de-allocate the page.
c.. Return.
7.. Page must belong to second index passed in.
a.. Try to allocate the page to the second index. If it fails, de-allocate the page.
b.. Return.
Caution This repair may cause data loss.
If running DBCC CHECKDB with one of the repair clauses does not correct the problem, contact your
primary support provider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sarma" <Sarma@.discussions.microsoft.com> wrote in message
news:C9AD329A-1066-482E-AAC8-1248BD70FAE8@.microsoft.com...
>I am getting the following error after checking the database with DBCC
> CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
> option. But it is not repairing. I am not a professional DBA please advise
> what to do.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:5604) in database ID 7 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'.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:5604) with latch type SH. VerifyPageId
> failed.
begin 666 caution.gif
M1TE&.#EA# `+`/<``````#,``&8``)D``,P``/\````S`#,S`&8S`)DS`,PS
M`/\S``!F`#-F`&9F`)EF`,QF`/]F``"9`#.9`&:9`)F9`,R9`/^9``#,`#/,
M`&;,`)G,`,S,`/_,``#_`#/_`&;_`)G_`,S_`/__````,S,`,V8`,YD`,\P`
M,_\`,P`S,S,S,V8S,YDS,\PS,_\S,P!F,S-F,V9F,YEF,\QF,_]F,P"9,S.9
M,V:9,YF9,\R9,_^9,P#,,S/,,V;,,YG,,\S,,__,,P#_,S/_,V;_,YG_,\S_
M,___,P``9C,`9F8`9ID`9LP`9O\`9@.`S9C,S9F8S9IDS9LPS9O\S9@.!F9C-F
M9F9F9IEF9LQF9O]F9@."99C.99F:99IF99LR99O^99@.#,9C/,9F;,9IG,9LS,
M9O_,9@.#_9C/_9F;_9IG_9LS_9O__9@.``F3,`F68`F9D`F<P`F?\`F0`SF3,S
MF68SF9DSF<PSF?\SF0!FF3-FF69FF9EFF<QFF?]FF0"9F3.9F6:9F9F9F<R9
MF?^9F0#,F3/,F6;,F9G,F<S,F?_,F0#_F3/_F6;_F9G_F<S_F?__F0``S#,`
MS&8`S)D`S,P`S/\`S `SS#,SS&8SS)DSS,PSS/\SS !FS#-FS&9FS)EFS,QF
MS/]FS "9S#.9S&:9S)F9S,R9S/^9S #,S#/,S&;,S)G,S,S,S/_,S #_S#/_
MS&;_S)G_S,S_S/__S ``_S,`_V8`_YD`_\P`__\`_P`S_S,S_V8S_YDS_\PS
M__\S_P!F_S-F_V9F_YEF_\QF__]F_P"9_S.9_V:9_YF9_\R9__^9_P#,_S/,
M_V;,_YG,_\S,___,_P#__S/__V;__YG__\S______P``````````````````
M````````````````````````````````````````````````````````````
M````````````````````````````````````````````````````````````
M`````````````````````"'Y! $``*P`+ `````,``L`0 @.B`%D)'$BPH,&#
;K HH1)AP(4*%!1A"9$A1X,2'#BMBC#@.P( `[
`
end
CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
option. But it is not repairing. I am not a professional DBA please advise
what to do.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:5604) in database ID 7 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:5604) with latch type SH. VerifyPageId
failed.Hi,
Best solution will be to restore the database from a healthy backup and to
analyze further open a case with Microsoft product support.
These errors occurs when there is a Hardware issue, so contact your hardware
vendor and ask them to do a hardware diagonostic.
Replace the hardware if any fault is rectified
Thanks
Hari
SQL Server MVP
"Sarma" <Sarma@.discussions.microsoft.com> wrote in message
news:C9AD329A-1066-482E-AAC8-1248BD70FAE8@.microsoft.com...
>I am getting the following error after checking the database with DBCC
> CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
> option. But it is not repairing. I am not a professional DBA please advise
> what to do.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:5604) in database ID 7 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'.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:5604) with latch type SH. VerifyPageId
> failed.|||Here are my general recommendations for these types of situations.
Also, below is from Books Online, specific for this error number:
Severity Level 16
Message Text
Page P_ID in database ID DB_ID is allocated in the SGAM SGAM_P_ID and PFS PFS_P_ID, but was not
allocated in any IAM. PFS flags 'PFS_FLAGS'.
Explanation
A PFS page has one byte per page for all pages in the file interval it maps (approximately 64 Mb).
Each byte contains, among other things, a bit that, when set, indicates that the page is from a
mixed extent (that is, it is a mixed page).
An IAM page has, among other things, an array of eight page IDs (called the mixed page array) to
hold the mixed pages allocated to the index. Note that IAM pages themselves are mixed pages, but IAM
pages are not self-referential (that is, the IAM page ID itself is not stored in its own mixed page
array).
This message means that a page has the 'mixed page' bit set in its PFS byte, but the page was not
seen as an IAM page and was not seen in an IAM page's mixed page array.
Action
HARDWARE FAILURE
Run hardware diagnostics and correct any problems. Also examine the Microsoft® Windows NT® system
and application logs and the SQL ServerT error log to see if the error occurred as the result of
hardware failure. Fix any hardware related problems.
If you have persistent data corruption problems, try to swap out different hardware components to
isolate the problem. Check to ensure that your system does not have write caching enabled on the
disk controller. If you suspect this to be the problem, contact your hardware vendor.
Finally, you might find it beneficial to switch to a completely new hardware system, including
reformatting the disk drives and reinstalling the operating system.
RESTORE FROM BACKUP
If the problem is not hardware related and a known clean backup is available, restore the database
from the backup.
DBCC CHECKDB
If no clean backup is available, execute DBCC CHECKDB without a repair clause to determine the
extent of the corruption. DBCC CHECKDB will recommend a repair clause to use. Then, execute DBCC
CHECKDB with the appropriate repair clause to repair the corruption.
Caution If you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact
your primary support provider before executing this statement.
If the page is an IAM page, performing a repair will de-allocate the page, and the IAM chain will be
patched by the repairs for accompanying errors.
Otherwise, a general purpose routine to repair doubly allocated pages is used. It takes two sets of
IDs, one for each of the two indexes (IAM chains) that have the page allocated. In this case, we use
the IDs derived from the page itself, and an invalid set of IDs.
The algorithm of the general purpose routine is as follows:
1.. Check page validity.
a.. If the page is not a valid page ID in the database, de-allocate it from both indexes and
return.
b.. If the page is corrupt, de-allocate it from both indexes, then return.
2.. Work out which index the page actually belongs to.
3.. Check for the allocation page.
a.. If the page is an IAM page or allocation page, de-allocate it from both indexes passed in.
b.. If the page is an IAM page and the index it thinks it belongs to cannot be found,
de-allocate the page.
c.. Return.
4.. If the page belongs to the first index passed in:
a.. De-allocate it from the second index passed in.
b.. Try to allocate it to the first index. If this fails, de-allocate the page.
c.. Return.
Note We should not get below here for this particular error.
5.. De-allocate the page from the first index.
6.. If the page does not belong to second index:
a.. If the second index is not invalid, de-allocate the page from the second index.
b.. Try to allocate the page to the real owning index. If it fails, de-allocate the page.
c.. Return.
7.. Page must belong to second index passed in.
a.. Try to allocate the page to the second index. If it fails, de-allocate the page.
b.. Return.
Caution This repair may cause data loss.
If running DBCC CHECKDB with one of the repair clauses does not correct the problem, contact your
primary support provider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sarma" <Sarma@.discussions.microsoft.com> wrote in message
news:C9AD329A-1066-482E-AAC8-1248BD70FAE8@.microsoft.com...
>I am getting the following error after checking the database with DBCC
> CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
> option. But it is not repairing. I am not a professional DBA please advise
> what to do.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:5604) in database ID 7 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'.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:5604) with latch type SH. VerifyPageId
> failed.
begin 666 caution.gif
M1TE&.#EA# `+`/<``````#,``&8``)D``,P``/\````S`#,S`&8S`)DS`,PS
M`/\S``!F`#-F`&9F`)EF`,QF`/]F``"9`#.9`&:9`)F9`,R9`/^9``#,`#/,
M`&;,`)G,`,S,`/_,``#_`#/_`&;_`)G_`,S_`/__````,S,`,V8`,YD`,\P`
M,_\`,P`S,S,S,V8S,YDS,\PS,_\S,P!F,S-F,V9F,YEF,\QF,_]F,P"9,S.9
M,V:9,YF9,\R9,_^9,P#,,S/,,V;,,YG,,\S,,__,,P#_,S/_,V;_,YG_,\S_
M,___,P``9C,`9F8`9ID`9LP`9O\`9@.`S9C,S9F8S9IDS9LPS9O\S9@.!F9C-F
M9F9F9IEF9LQF9O]F9@."99C.99F:99IF99LR99O^99@.#,9C/,9F;,9IG,9LS,
M9O_,9@.#_9C/_9F;_9IG_9LS_9O__9@.``F3,`F68`F9D`F<P`F?\`F0`SF3,S
MF68SF9DSF<PSF?\SF0!FF3-FF69FF9EFF<QFF?]FF0"9F3.9F6:9F9F9F<R9
MF?^9F0#,F3/,F6;,F9G,F<S,F?_,F0#_F3/_F6;_F9G_F<S_F?__F0``S#,`
MS&8`S)D`S,P`S/\`S `SS#,SS&8SS)DSS,PSS/\SS !FS#-FS&9FS)EFS,QF
MS/]FS "9S#.9S&:9S)F9S,R9S/^9S #,S#/,S&;,S)G,S,S,S/_,S #_S#/_
MS&;_S)G_S,S_S/__S ``_S,`_V8`_YD`_\P`__\`_P`S_S,S_V8S_YDS_\PS
M__\S_P!F_S-F_V9F_YEF_\QF__]F_P"9_S.9_V:9_YF9_\R9__^9_P#,_S/,
M_V;,_YG,_\S,___,_P#__S/__V;__YG__\S______P``````````````````
M````````````````````````````````````````````````````````````
M````````````````````````````````````````````````````````````
M`````````````````````"'Y! $``*P`+ `````,``L`0 @.B`%D)'$BPH,&#
;K HH1)AP(4*%!1A"9$A1X,2'#BMBC#@.P( `[
`
end
DBCC CHECKDB Error
I am getting the following error after checking the database with DBCC
CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
option. But it is not repairing. I am not a professional DBA please advise
what to do.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:5604) in database ID 7 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:5604) with latch type SH. VerifyPageId
failed.Hi,
Best solution will be to restore the database from a healthy backup and to
analyze further open a case with Microsoft product support.
These errors occurs when there is a hardware issue, so contact your hardware
vendor and ask them to do a hardware diagonostic.
Replace the hardware if any fault is rectified
Thanks
Hari
SQL Server MVP
"Sarma" <Sarma@.discussions.microsoft.com> wrote in message
news:C9AD329A-1066-482E-AAC8-1248BD70FAE8@.microsoft.com...
>I am getting the following error after checking the database with DBCC
> CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
> option. But it is not repairing. I am not a professional DBA please advise
> what to do.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:5604) in database ID 7 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'.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:5604) with latch type SH. VerifyPageId
> failed.
CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
option. But it is not repairing. I am not a professional DBA please advise
what to do.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:5604) in database ID 7 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:5604) with latch type SH. VerifyPageId
failed.Hi,
Best solution will be to restore the database from a healthy backup and to
analyze further open a case with Microsoft product support.
These errors occurs when there is a hardware issue, so contact your hardware
vendor and ask them to do a hardware diagonostic.
Replace the hardware if any fault is rectified
Thanks
Hari
SQL Server MVP
"Sarma" <Sarma@.discussions.microsoft.com> wrote in message
news:C9AD329A-1066-482E-AAC8-1248BD70FAE8@.microsoft.com...
>I am getting the following error after checking the database with DBCC
> CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
> option. But it is not repairing. I am not a professional DBA please advise
> what to do.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:5604) in database ID 7 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'.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:5604) with latch type SH. VerifyPageId
> failed.
DBCC CheckDB
Hi:
As part of database maintenance plan, I am using the option (checkbox) for checking the integrity of the database before backing up database or transaction log.
I am not sure when this database integrity check happens (DBCC CheckDB). They seem to be happening at different times not necessarily right before database backup. Does anyone have more details on this?
Also I am backing up my user and system databases every night with database integrity checkup. This is a live eCommerce site. Usually database integrity check should not take long but if it does, will it block users. Is it good idea to do it every night? I was thinking of backing up system database only on weekends.
Thanks
Hi Mike. From the sounds of it, you're referring to a SQL 2000 system I assume.
Can't say really if it's a good idea or not to perform the checkdb nightly, I can surely say it's not a bad thing to do it, other than if it causes problems like you stated (i.e. blocking)...another possible problem could be that it 'thrashes' the buffer pool, and possibly leaves unneeded data in your cache rather than data that is normally there, so in the morning you may see some slowdown on queries until the wanted data resides in cache again.
One thing many people will do on larger, OLTP type systems is perform a checkdb on the production system weekly (or something like that) and perform a nightly checkdb on a restore of the live database on a test server.
As for the schedule of when they occur, if you go to the 'integrity' tab of the existing maintanence plan, at the bottom of the screen there will be a schedule listed there for the integrity check portion of the plan only. Each of the tabs (Optimizations, Integrity, Backup, etc.) have a seperate schedule assigned that defines when that particular operation is begun. Additionaly, you could check the schedule of the SQL Agent Jobs that are created to actually kick off the maintanence tasks.
HTH
Subscribe to:
Posts (Atom)