sql server 2000 扩展盘区分配错误 error:8905 的问题解决
在用dbcc checkdb 对数据库进行检查的时候，在数据结果的头部报下面的错误：
扩展盘区 (1:9508664) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508672) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508680) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508688) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508696) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508704) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508712) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508720) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508728) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508736) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508744) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508760) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508776) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508792) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508800) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508808) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508816) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508824) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508832) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508840) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
消息 8905 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9508848) （属于数据库 ID 7 ）在 GAM 中标记为已分配，但没有 SGAM 或 IAM 分配过该盘区。
.........................................
消息 8904 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
不止一个分配对象分配了扩展盘区 (1:9560016) （属于数据库 ID 7 ）。
消息 8913 ，级别 16 ，状态 1 ，服务器 SERVER ，行 1
扩展盘区 (1:9560016) 分配给了 'GAM' 和至少一个其它对象。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
DBCC 语句的修复级别导致回避了此修复。
CHECKALLOC 发现了 138 个分配错误和 0 个一致性错误，这些错误并不与任何单个的对象相关联。
***************************************************************
微软公司网站的解释如下：
FIX ： CheckDB 可能未修复错误 8909 或错误 8905
如果 DBCC CHECKDB 报告进行排序，使用 repair_allow_data_loss 选项运行 DBCC CHECKDB 时分配的页上的错误 8909 ，DBCC CHECKDB 报告该错误固定即使不固定的错误。错误 8905 通常伴随的错误 8909 的八个或多个匹配项的组。修复选项集，而不考虑的一个后续的 DBCC CHECKDB 执行报告相同的一组 8909 和 8905 错误消息。
这些错误消息的示例是：
服务器： 消息 8905 ，级别 16 ，1 ，状态行 1
在数据库 ID 为 10 的程度 （1:192 ） 被标记为在 GAM ，已分配但没有 SGAM 或 IAM 已分配。
- 或者-
服务器： 消息 8909 ，级别 16 ，1 ，状态行 1
表错误: 对象 ID 0 、 索引 ID 0 中的页面 ID （1:192 ）。页面页眉中的 PageId = （0: 0 ）。
原因
DBCC CHECKDB 语句的修复选项错误地报告错误 8909 固定。仅在扩展盘区和分配进行排序的页会出现此问题。用于排序的页具有下列字段值： 页类型设置为...
DBCC CHECKDB 语句的修复选项错误地报告错误 8909 固定。仅在扩展盘区和分配进行排序的页会出现此问题。用于排序的页具有下列字段值：
· 页类型设置为 SORT_PAGE (7) 。
· 对象 ID 为 0 。
· 索引 ID 为 0 。
解决方案
若要解决此问题，获得最新的 service pack ，对于 Microsoft SQL Server 2000 。有关更多的信息请单击下面的文章编号，以查看 Mi...
若要解决此问题，获得最新的 service pack ，对于 Microsoft SQL Server 2000 。有关更多的信息请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章：
290211 (http://support.microsoft.com/kb/290211/EN-US/ ) INF ： 如何获取最新的 SQL Server 2000 Service Pack
若要修复 CHECKDB 必须强制删除只包含分配给 objid 和 indid 0 页的扩展盘区分配修复此问题。
若要解决此问题，安装 SQL Server 2000 Service Pack 2 ，然后使用 repair_allow_data_loss 选项运行 DBCC CHECKDB 。
若要解决该问题，而不应用 SQL Server 2000 Service Pack 2 ，您可以使用数据转换服务 (DTS) 来传输出数据库。
状态
Microsoft 已经确认这是 Microsoft SQL Server 2000 中的问题。Microsoft SQL Server 2000 Servic...
Microsoft 已经确认这是 Microsoft SQL Server 2000 中的问题。
Microsoft SQL Server 2000 Service Pack 2 中，第一次已得到纠正此问题。
=============================================================================
Error 8905
Topic last updated -- January 2004
Severity Level 16
Message Text
Extent E_ID in database ID DB_ID is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Explanation
A GAM page has one bit per extent for all extents in the file interval it maps (approximately 4 GB). A bit is set if the extent is completely unallocated and clear if the extent is allocated.
An SGAM page has a similar structure to a GAM page, with the following differences:
- Each set bit represents an extent in which the pages are individually allocated to IAM pages for different indexes (called a 'mixed' extent).
- There is at least one unallocated page left to be used in the extent, and at least one allocated page. (It is not possible to have a mixed extent with no allocated pages—this is just an unallocated extent).
An IAM page again has a similar structure, with one bit per dedicated extent allocated to the index of which the IAM page is part.
The bit combinations are as follows. Combinations in italics are illegal; the one in bold corresponds to this error.
|
State |
GAM |
SGAM |
IAM |
Legal |
Meaning |
|
1 |
0 |
0 |
0 |
Y |
Extent is mixed, with all pages allocated. |
|
2 |
0 |
0 |
1 |
Y |
Extent is dedicated, allocated to the IAM. |
|
3 |
0 |
1 |
0 |
Y |
Extent is mixed, with unallocated pages. |
|
4 |
0 |
1 |
1 |
N |
Extent is dedicated and mixed. |
|
5 |
1 |
0 |
0 |
Y |
Extent is unallocated. |
|
6 |
1 |
0 |
1 |
N |
Extent is dedicated and unallocated. |
|
7 |
1 |
1 |
0 |
N |
Extent is mixed and unallocated. |
|
8 |
1 |
1 |
1 |
N |
Extent is mixed, dedicated, and unallocated. |
This error corresponds to the bits being in state 1 in the table above, but none of the individual pages in the extent were seen.
Action
HARDWARE FAILURE
Run hardware diagnostics and correct any problems. Also examine the Microsoft® Windows NT® system and application logs and the SQL Server™ 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 running DBCC CHECKDB with one of the repair clauses does not correct the problem, contact your primary support provider.
专业数据库恢复公司Stellar Phoenix SQL Recovery 的解释如下：
CHECKDB Fails to Fix Error 8909 and 8905
DBCC CHECKDB commands allow checking logical and physical SQL Server database integrity. When used with REPAIR_ALLOW_DATA_LOSS argument, it repairs all reported errors, but with the possibility of data loss. The corrupted data gets deleted in the process. But at times, when you run DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS argument, database corruption is reported as fixed though is not corrected. In such situations, you can use your recent data backup to restore the database. But if the backup fails to restore or is not present, you need to scan your damaged database with powerful SQL recovery applications.
Consider a practical instance, you scan your SQL Server 2000 database and get 8909 error, followed by a group of eight or more 8909 error messages:
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:192) in database ID 10 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Or
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:192). The PageId in the page header = (0:0).
Further, you use DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS option and it reports it as fixed. But again, when you run DBCC CHECKDB command to check database integrity, it reports the same error messages.
Cause
You encounter this issue when the following conditions are true:
- You run DBCC CHECKDB on pages and extents that are allocated for sorting
- Pages have field values such as Page type is set to SORT_PAGE (7), Index ID is 0 and Object ID is 0
- You haven’t installed SQL Server 2000 Service Pack 2 or later.
Solution
To resolve the issue, you can follow any of these methods:
- Install SQL Server 2000 Service Pack 2 and run DBCC CHECKDB command with repair_allow_data_loss option
- Use DTS (Data Transformation Services ) and transfer the database out.
If the above measures don’t help or the database still appears to be corrupt, you need to use efficient third-party SQL repair applications. These are the commercial tools provided to scan and repair damaged SQL Server databases. You can use these SQL Recovery products in any case of SQL database corruption.
处理：
DBCC CHECKALLOC
Checks the consistency of disk space allocation structures for a specified database.
Syntax
DBCC CHECKALLOC
(' database_name '
[ , NOINDEX
|
{ REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
}
]
Arguments
' database_name '
Is the database for which to check allocation and page usage. If not specified, the default is the current database. Database names must conform to the rules for identifiers. For more information, see Using Identifiers .
NOINDEX
Specifies that nonclustered indexes for nonsystem tables should not be checked.
Note NOINDEX is maintained for backward compatibility only. All indexes are checked when executing DBCC CHECKALLOC.
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Specifies that DBCC CHECKALLOC repair the found errors. The given database_name must be in single-user mode to use one of these repair options, and can be one of the following.
|
Value |
Description |
|
REPAIR_ALLOW_DATA_LOSS |
Performs all repairs done by REPAIR_REBUILD and includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. These repairs can result in some data loss. The repair can be done under a user transaction to allow the user to roll back the changes made. If repairs are rolled back, the database will still contain errors and should be restored from a backup. If a repair for an error has been skipped due to the provided repair level, any repairs that depend on the repair are also skipped. After repairs are completed, back up the database. |
|
REPAIR_FAST |
Performs minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss. |
|
REPAIR_REBUILD |
Performs all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss. |
WITH
Specifies options on the number of error messages returned, locks obtained, or estimating tempdb requirements. If neither ALL_ERRORMSGS nor NO_INFOMSGS is specified, Microsoft® SQL Server™ 2000 returns all error messages.
ALL_ERRORMSGS
Displays all error messages. If not specified, SQL Server displays a maximum of 200 error messages per object.
NO_INFOMSGS
Suppresses all informational messages and the report of space used.
TABLOCK
Causes DBCC command to obtain shared table locks. Ignored for DBCC CHECKALLOC.
ESTIMATE ONLY
Displays the estimated amount of tempdb space required to run DBCC CHECKALLOC with all of the other specified options.
Remarks
DBCC CHECKALLOC checks allocation and page usage in a database, including indexed views. The NOINDEX option, used only for backward compatibility, also applies to indexed views.
It is not necessary to execute DBCC CHECKALLOC if DBCC CHECKDB has already been executed. DBCC CHECKDB is a superset of DBCC CHECKALLOC and includes allocation checks in addition to checks of index structure and data integrity.
DBCC CHECKDB is the safest repair statement because it identifies and repairs the widest possible range of errors. If only allocation errors are reported for a database, execute DBCC CHECKALLOC with a repair option to correct them. However, to ensure that all errors (including allocation errors) are repaired properly, execute DBCC CHECKDB with a repair option. DBCC CHECKALLOC messages are sorted by object ID, except for those messages generated from tempdb . DBCC CHECKALLOC validates the allocation of all data pages in the database while DBCC CHECKDB validates the page information used in the storage of data in addition to validating the allocation information.
DBCC CHECKALLOC does not acquire table locks by default. Instead, it acquires schema locks that prevent meta data changes but allow changes to the data while the DBCC CHECKALLOC is in progress. The DBCC statement collects information, and then scans the log for any additional changes made, merging the two sets of information together to produce a consistent view of the data at the end of the scan.
Result Sets
This table describes the information DBCC CHECKALLOC returns.
|
Item |
Description |
|
FirstIAM |
Internal use only. |
|
Root |
Internal use only. |
|
Dpages |
Data page count from sysindexes. |
|
Pages used |
Allocated pages. |
|
Dedicated extents |
Extents allocated to the object. If mixed allocation pages are used, there may be pages allocated with no extents. |
The second part of a DBCC CHECKALLOC report is an allocation summary for each index in each file. This summary gives users an idea of the distribution of the data.
|
Item |
Description |
|
Reserved |
Pages allocated to the index and the unused pages in allocated extents. |
|
Used |
Pages allocated and in use by the index. |
Whether or not any options (except WITH NO_INFOMSGS) are specified, DBCC CHECKALLOC returns this result set (values may vary):
DBCC results for 'master'.
***************************************************************
Table sysobjects Object ID 1.
Index ID 1 FirstIAM (1:11) Root (1:12) Dpages 22.
Index ID 1. 24 pages used in 5 dedicated extents.
Index ID 2 FirstIAM (1:1368) Root (1:1362) Dpages 10.
Index ID 2. 12 pages used in 2 dedicated extents.
Index ID 3 FirstIAM (1:1392) Root (1:1408) Dpages 4.
Index ID 3. 6 pages used in 0 dedicated extents.
Total number of extents is 7.
***************************************************************
'...'
***************************************************************
Table spt_server_info Object ID 1938105945.
Index ID 1 FirstIAM (1:520) Root (1:508) Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Processed 52 entries in sysindexes for database ID 1.
File 1. Number of extents = 210, used pages = 1126, reserved pages = 1280.
File 1 (number of mixed extents = 73, mixed pages = 184).
Object ID 1, Index ID 0, data extents 5, pages 24, mixed extent pages 9.
'...'
Object ID 1938105945, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Total number of extents = 210, used pages = 1126, reserved pages = 1280 in this database.
(number of mixed extents = 73, mixed pages = 184) in this database.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'master'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC CHECKALLOC returns this result set when the ESTIMATE ONLY option is specified.
Estimated TEMPDB space needed for CHECKALLOC (KB)
-------------------------------------------------
34
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Permissions
DBCC CHECKALLOC permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, and are not transferable.
Examples
This example executes DBCC CHECKALLOC for the current database and for the pubs database.
-- Check the current database.
DBCC CHECKALLOC
GO
-- Check the pubs database.
DBCC CHECKALLOC ('pubs')
GO