DBCC工具程序--数据库维护

DBCC工具程序维护数据库：

1.DBCC DBREINDEX

对指定数据库中的表重新生成一个或多个索引。

语法：

DBCC DBREINDEX

(
    'table_name'
    [ , 'index_name' [ , fillfactor ] ]
)
    [ WITH NO_INFOMSGS ]

 

table_name 包含要重新生成的指定索引的表的名称。 index_name 要重新生成的索引名 fillfactor 在创建或重新生成索引时，每个索引页上用于存储数据的空间百分比。 with no_infomsg 取消显示严重级别从 0 到 10 的所有信息性消息。

例：

USE AdventureWorks;

GO

DBCC DBREINDEX ("HumanResources.Employee", PK_Employee_EmployeeID,80) WITH NO_INFOMSGS ;

GO

 

2.DBCC INDEXDEFRAG

指定表或视图的索引碎片整理

语法：

DBCC INDEXDEFRAG ( { 'database_name' | database_id | 0 } , { 'table_name' | table_id | 'view_name' | view_id } [ , { 'index_name' | index_id } [ , { partition_number | 0 } ] ] ) [ WITH NO_INFOMSGS ]

例：

DBCC INDEXDEFRAG (AdventureWorks, "Production.Product", PK_Product_ProductID)
GO

 

3.DBCC SHRINKDATABASE

收缩指定数据库中的数据文件和日志文件的大小

语法：

DBCC SHRINKDATABASE ( { 'database_name' | database_id | 0 } [ , target_percent ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ) [ WITH NO_INFOMSGS ]

例：

/*

DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
5      2           968         70          968         64

*/

 

说明：CurrentSize一般跟UsedPages  相同，用来标识当前DB所应用的页数量；

EstimatedPages标识收缩后的页数量；

 

4.DBCC SHRINKFILE

收缩当前数据库的指定数据或日志文件的大小，或通过将数据从指定的文件移动到相同文件组中的其他文件来清空文件，以允许从数据库中删除该文件。文件大小可以收缩到比创建该文件时所指定的大小更小。这样会将最小文件大小重置为新值.

语法：

DBCC SHRINKFILE ( { 'file_name' | file_id } { [ , EMPTYFILE] | [ [, target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ] } ) [ WITH NO_INFOMSGS ]

例：

例1：清空数据库文件所有数据 DBCC SHRINKFILE (AdventureWorks, EMPTYFILE); GO 例2:将文件收缩到指定大小 DBCC SHRINKFILE (AdventureWorks_Log, 1); GO 例3：截断数据文件 DBCC SHRINKFILE (1, TRUNCATEONLY);

5.DBCC UPDATEUSAGE

报告目录视图中的页数和行数错误并进行更正.

DBCC UPDATEUSAGE ( { 'database_name' | database_id | 0 } [ , { 'table_name' | 'view_name' | object_id } [ , { 'index_name' | index_id } ] ] ) [ WITH { [ NO_INFOMSGS ] [ , [ COUNT_ROWS ] ] } ]

例：

DBCC UPDATEUSAGE(0)

/*

DBCC UPDATEUSAGE: 已更新表 'sysobjvalues' 的使用计数(索引 'clst'，分区 1):
        保留页 (LOB Data): 已从(60)页更改为(56)页。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

*/

 

6.DBCC TAB
You can use the following undocumented command to view the data pages structure (in comparison with DBCC PAGE, this command will return information about all data pages for viewed table, not only for particular number).

语法：
DBCC tab (dbid, objid)

例：
DBCC TRACEON (3604) DECLARE @dbid int, @objectid int SELECT @dbid = DB_ID('master') SELECT @objectid = object_id('sysdatabases') DBCC TAB (@dbid,@objectid)

7.DBCC DBTABLE
This command displays the contents of the DBTABLE structure.

Syntax:
DBCC DBTABLE ({dbid|dbname})

DBCC TRACEON (3604)
DBCC DBTABLE (master)

DBTABLES: DBTABLE @0x07418040 dbt_dbid = 1 dbt_cmptlevel = 100 dbt_crtime = 2009-08-16 20:08:02.420 dbt_protstamp = 189156 dbt_auditstamp = 189156 dbt_cryptostamp = 189156 dbt_nextid = 2139154666 dbt_dbname = master dbt_stat = 0x10008 dbt_stat2 = 0x0 dbt_relstat = 0x41000400 dbt_maxDbTimestamp = 4000 dbt_dbTimestamp = 4000 database version = 655 dbt_DEK = 0x00000000 dbt_repltrans = -2081099653 dbt_replcount = 0 dbt_replrate = 0.000000 dbt_repllatency = 0.000000 dbt_dbMirroring = 0x00000000 dbt_dbMirroringState = 0x0 m_DbmHistoryCount = 1 m_HasMirroringStarted = 0x0 Mirror History = Entry 0(0x0), State 0x0, Caller 0x025AA79C distbackuplsn = (0:0:0) distlastlsn = (0:0:0) replbeginlsn = (0:0:0) replnextlsn = (0:0:0) dbt_category = 0 RecoveryUnit 1:0, @0x07418500 m_StartupState = Available m_CurrentState = Online m_DesiredState = Online m_OnDiskVersion = 655 m_CommitLsnTickCount = 3899612 ((LSN)m_CommitLsn) = 324:360:1 (0x00000144:00000168:0001) m_RecoveryMode = SimpleRecovery m_CkptState.cps_pagesOutstanding = 0 m_IsFinishedStarting = 0 m_IsReadOnly = 0 m_IsShuttingDown = 0 m_MayHaveDeferred = 0 m_IsCkptReqPending = 0 m_IsOutOfSpace = 0 m_StartupThread = 0x00000000 m_RollbackThread = 0x00000000 m_StartupTick = 189156 FCB @0x15AEA040 fcb_hdl = 0x00000330 fcb_fileid = 1 fcb_dbid = 1 m_status = 1048578 m_ContainerState = 1 m_OpenError = 0 m_IsStarted = 1 m_IsOkToDirtyForBackup = 0 m_IsInSetup = 0 m_IsResizing = 0 m_AutoGrowFailed = 0 m_SelectedForMediaRedo = 0 m_OpenIsDisabled = 0 m_size = 512 m_fgid = 1 m_maxSize = 4294967295 m_minSize = 512 m_preShrinkSize = 0 m_maxPreShrinkSize = 0 m_allocSize = 512 m_growth = 10 m_perf = 0 m_userShrinkSize = 4294967295 m_FreeExtentCount = 17 m_resetExtents = 1 m_propExtents = 1 m_FormattedSectorSize = 4096 m_ActualSectorSize = 512 m_lastLongIOWaitWarning = 0 m_numOfLongIOsSinceLastWarning = 0 FCB::m_backupLsn m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0 FCB::m_OldestRestoredLsn m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0 FCB::m_firstLsn m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0 FCB::m_maxLsn m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0 FCB::m_CreateLsn m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0 FCB::m_differentialBaseLsn m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0 FCB::m_RestoreDifferentialBaseLsn m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0 FCB::m_firstUpdateLsn m_fSeqNo = 123 m_blockOffset = 216 m_slotId = 10 FCB::m_fileOfflineLsn m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0 FCB::m_ReadOnlyLsn m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0 FCB::m_ReadWriteLsn m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0 m_RestoreStatus = 0 FCB::m_RedoStartLsn m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0 m_LastUniformExtent = 0 m_LastMixedExtent = 0 m_FormattedSectorSize = 4096 m_ActualSectorSize = 512 m_primaryFCB = 00000000 m_splitSize = 0 fcb_encryptionState = 0 fcb_DEK = 0x00000000 fcb_prevDEK = 0x00000000 m_spm_lastLongIOWaitWarninglitSize = 0 m_numOfLongIOsSinceLastWarning = 0 fcb_filepath = D:/Microsoft/Microsoft SQL Server 2008/MSSQL10.MSSQLSERVER/MSSQL/DATA/master.mdf fcb_lname = master FCB @0x15AEAA60 fcb_hdl = 0x0000032C fcb_fileid = 2 fcb_dbid = 1 m_status = 1048642 m_ContainerState = 1 m_OpenError = 0 m_IsStarted = 1 m_IsOkToDirtyForBackup = 0 m_IsInSetup = 0 m_IsResizing = 0 m_AutoGrowFailed = 0 m_SelectedForMediaRedo = 0 m_OpenIsDisabled = 0 m_size = 160 m_fgid = 0 m_maxSize = 4294967295 m_minSize = 64 m_preShrinkSize = 0 m_maxPreShrinkSize = 0 m_allocSize = 160 m_growth = 10 m_perf = 0 m_userShrinkSize = 4294967295 m_FreeExtentCount = 0 m_resetExtents = 0 m_propExtents = 0 m_FormattedSectorSize = 4096 m_ActualSectorSize = 512 m_lastLongIOWaitWarning = 0 m_numOfLongIOsSinceLastWarning = 0

8.DBCC IND
Shows all pages in use by indexes of the specified table.

dbid|dbname   - database id|database name.
objid|objname - object id|object name
printopt      - print option

DBCC TRACEON (3604)
DBCC IND (Northwind,CustomerData, 0)

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1       20927       NULL   NULL        2021582240  1           1               72057594043367424    In-row data          10       NULL       0           0           0           0
1       20926       1      20927       2021582240  1           1               72057594043367424    In-row data          1        0          0           0           0           0