存档
在用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
本文介绍如何识别当前的 Microsoft SQL Server 版本号和相应的产品或 Service Pack 级别。同时介绍如何识别正在使用的 SQL Server 具体版本。
如何确定正在运行的 SQL Server 2008 为哪个版本
若要确定正在运行的 SQL Server 2008 为哪个版本，请使用 SQL Server Management Studio 连接到 SQL Server 2008 ，然后运行下列 Transact-SQL 语句。
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
运行结果如下：
- 产品版本（例如，10.0.1600.22 ）
- 产品级别（例如，RTM ）
- 版本（例如， Enterprise ）
例如，运行结果可能类似于如下内容。
收起该表格展开该表格
|
10.0.1600.22 |
RTM |
Enterprise Edition |
下表列出了 Sqlservr.exe 版本号。
收起该表格展开该表格
版本
Sqlservr.exe
RTM
2007.100.1600.0
SQL Server 2008 Service Pack 1
2007.100.2531.0
PAD_INDEX
Specifies the space to leave open on each page (node) in the intermediate levels of the index. The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. By default, SQL Server ensures that each index page has enough empty space to accommodate at least one row of the maximum size the index can have, given the set of keys on the intermediate pages. If the percentage specified for FILLFACTOR is not large enough to accommodate one row, SQL Server internally overrides the percentage to allow the minimum.
Note The number of rows on an intermediate index page is never less than two, regardless of how low the value of
FILLFACTOR
FILLFACTOR = fillfactor
Specifies a percentage that indicates how full SQL Server should make the leaf level of each index page during index creation. When an index page fills up, SQL Server must take time to split the index page to make room for new rows, which is quite expensive. For update-intensive tables, a properly chosen FILLFACTOR value yields better update performance than an improper FILLFACTOR value. The value of the original FILLFACTOR is stored with the index in sysindexes .
When FILLFACTOR is specified, SQL Server rounds up the number of rows to be placed on each page. For example, issuing CREATE CLUSTERED INDEX ... FILLFACTOR = 33 creates a clustered index with a FILLFACTOR of 33 percent. Assume that SQL Server calculates that 5.2 rows is 33 percent of the space on a page. SQL Server rounds so that six rows are placed on each page.
Note An explicit FILLFACTOR setting applies only when the index is first created. SQL Server does not dynamically keep the specified percentage of empty space in the pages.
User-specified FILLFACTOR values can be from 1 through 100. If no value is specified, the default is 0. When FILLFACTOR is set to 0, only the leaf pages are filled. You can change the default FILLFACTOR setting by executing sp_configure .
Use a FILLFACTOR of 100 only if no INSERT or UPDATE statements will occur, such as with a read-only table. If FILLFACTOR is 100, SQL Server creates indexes with leaf pages 100 percent full. An INSERT or UPDATE made after the creation of an index with a 100 percent FILLFACTOR causes page splits for each INSERT and possibly each UPDATE.
Smaller FILLFACTOR values, except 0, cause SQL Server to create new indexes with leaf pages that are not completely full. For example, a FILLFACTOR of 10 can be a reasonable choice when creating an index on a table known to contain a small portion of the data that it will eventually hold. Smaller FILLFACTOR values also cause each index to take more storage space.
The following table illustrates how the pages of an index are filled up if FILLFACTOR is specified.
| FILLFACTOR | Intermediate page | Leaf page |
|---|---|---|
| 0 percent | One free entry | 100 percent full |
| 1 - 99 percent | One free entry | <= FILLFACTOR percent full |
| 100 percent | One free entry | 100 percent full |
One free entry is the space on the page that can accommodate another index entry.
Important Creating a clustered index with a FILLFACTOR affects the amount of storage space the data occupies because SQL Server redistributes the data when it creates the clustered index.
示例：
This example uses the FILLFACTOR clause set to 100. A FILLFACTOR of 100 fills every page completely and is useful only when you know that index values in the table will never change.
SET NOCOUNT OFF USE pubs IF EXISTS (SELECT name FROM sysindexes WHERE name = 'zip_ind') DROP INDEX authors.zip_ind GO USE pubs GO CREATE NONCLUSTERED INDEX zip_ind ON authors (zip) WITH FILLFACTOR = 100 GOcreate table 时： [WITH FILLFACTOR = fillfactor ]
Specifies how full SQL Server should make each index page used to store the index data. User-specified fillfactor values can be from 1 through 100, with a default of 0. A lower fill factor creates the index with more space available for new index entries without having to allocate new space.
Status bits, some of which can be set by using ALTER DATABASE as noted:
1 = autoclose (ALTER DATABASE)
4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY)
8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)
16 = torn page detection (ALTER DATABASE)
32 = loading
64 = pre recovery
128 = recovering
256 = not recovered
512 = offline (ALTER DATABASE)
1024 = read only (ALTER DATABASE)
2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER)
4096 = single user (ALTER DATABASE)
32768 = emergency mode
4194304 = autoshrink (ALTER DATABASE)
1073741824 = cleanly shutdown
Multiple bits can be ON at the same time.
status2
16384 = ANSI null default (ALTER DATABASE)
65536 = concat null yields null (ALTER DATABASE)
131072 = recursive triggers (ALTER DATABASE)
1048576 = default to local cursor (ALTER DATABASE)
8388608 = quoted identifier (ALTER DATABASE)
33554432 = cursor close on commit (ALTER DATABASE)
67108864 = ANSI nulls (ALTER DATABASE)
268435456 = ANSI warnings (ALTER DATABASE)
536870912 = full text enabled (set by using sp_fulltext_database )
转自：http://blog.csdn.net/obuntu/archive/2010/06/08/5655494.aspx
谢谢obuntu
| flag | Trace Flag Description (underlined are sp_configure’able) |
| -1 | Sets trace flags for all connections. Used only with DBCC TRACEON and TRACEOFF. The setting of the Trace flag -1 is not visible with DBCC TRACESTATUS command, but work without problems. |
| 105 | SQL Server 6.5 you can use maximum 16 tables or subqueries in a single select statement. There is no documented way, to avoid this restriction, but you can use undocumented trace flag 105 for this purpose. |
| 106 | Disables line number information for syntax errors. |
| 107 | Interprets numbers with a decimal point as float instead of decimal. |
| 110 | Turns off ANSI select characteristics. |
| 204 | A backward compatibility switch that enables non-ansi standard behavior. E.g. previously SQL server ignored trailing blanks in the like statement and allowed queries that contained aggregated functions to have items in the group by clause that were not in the select list. |
| 205 | Report when a statistics-dependent stored procedure is being recompiled as a result of AutoStat. |
| 206 | Provides backward compatibility for the setuser statement. |
| 208 | SET QUOTED IDENTIFIER ON. |
| 237 | Tells SQL Server to use correlated sub-queries in Non-ANSI standard backward compatibility mode. |
| 242 | Provides backward compatibility for correlated subqueries where non-ANSI-standard results are desired. |
| 243 | The behavior of SQL Server is now more consistent because null ability checks are made at run time and a null ability violation results in the command terminating and the batch or transaction process continuing. |
| 244 | Disables checking for allowed interim constraint violations. By default, SQL Server checks for and allows interim constraint violations. An interim constraint violation is caused by a change that removes the violation such that the constraint is met, all within a single statement and transaction. SQL Server checks for interim constraint violations for self-referencing DELETE statements, INSERT, and multi-row UPDATE statements. This checking requires more work tables. With this trace flag you can disallow interim constraint violations, thus requiring fewer work tables. |
| 246 | Derived or NULL columns must be explicitly named in a select….INTO or create view statement when not done they raise an error. This flag avoids that. |
| 253 | Prevents ad-hoc query plans to stay in cache. |
| 257 | Will invoke a print algorithm on the XML output before returning it to make the XML result more readable. |
在sql server 2000的错误日志文件中出现如下的错误：
2010-08-05 09:21:51.31 spid11 错误: 823，严重度: 24，状态: 2
2010-08-05 09:21:51.31 spid11 I/O error (torn page) detected during read at offset 0x0000116496a000 in file 'd:\Microsoft SQL Server\MSSQL\data\xxxxxxx_Data.MDF'.
======================================================
Description&Solution:
Torn_page_detection:
This recovery option allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages.
When set to ON, this option causes a bit to be reversed for each 512-byte sector in an 8-kilobyte (KB) database page when the page is written to disk. If a bit is in the wrong state when the page is later read by SQL Server, the page was written incorrectly; a torn page is detected. Torn pages are usually detected during recovery because any page that was written incorrectly is likely to be read by recovery.
Although SQL Server database pages are 8 KB, disks perform I/O operations using a 512-byte sector. Therefore, 16 sectors are written per database page. A torn page can occur if the system fails (for example, due to power failure) between the time the operating system writes the first 512-byte sector to disk and the completion of the 8-KB I/O operation. If the first sector of a database page is successfully written before the failure, the database page on disk will appear as updated, although it may not have succeeded.
Note Using battery-backed disk caches can ensure that data is successfully written to disk or not written at all.
If a torn page is detected, an I/O error is raised and the connection is killed. If the torn page is detected during recovery, the database is also marked suspect. The database backup should be restored, and any transaction log backups applied, because it is physically inconsistent.
By default, TORN_PAGE_DETECTION is ON.