DBCC语句在数据库中的运用！

-- Create the table to accept the results
CREATE TABLE #tracestatus (
   TraceFlag INT,
   Status INT
   )

-- Execute the command, putting the results in the table
INSERT INTO #tracestatus
   EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')

-- Display the results
SELECT *
FROM #tracestatus
GO
/**
语句分类                    执行
维护语句   对数据库、索引或文件组进行维护的任务。 
杂项语句   诸如启用行级锁定或从内存中删除动态链接库 (DLL) 等杂项任务。
状态语句   状态检查。
验证语句   对数据库、表、索引、目录、文件组、系统表或数据库页的分配进行的验证操作。
其中维护语句与验证语句是经常用到，诸如：DBCC CHECKDB、DBCC DBREINDEX等等。 
**/
一:维护语句   对数据库、索引或文件组进行维护的任务。
---1/DBCC DBREINDEX
A. 重建某个索引
下例使用填充因子 80 重建 pubs 数据库中 authors 表上的 au_nmind 聚集索引。
DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)

B. 重建所有索引
下例使用填充因子值 70 重建 authors 表上的所有索引。
DBCC DBREINDEX ('pubs.dbo.authors', '', 70)

--2/除去损坏的数据库。
DBCC DBREPAIR

--3/DBCC INDEXDEFRAG
/**
用DBCC showcontig
DBCC SHOWCONTIG
显示指定的表的数据和索引的碎片信息。

整理指定的表或视图的聚集索引和辅助索引碎片。
DBCC INDEXDEFRAG 可以对表或视图上的索引和非聚集索引进行碎片整理。
DBCC INDEXDEFRAG 对索引的叶级进行碎片整理，以便页的物理顺序与叶节点从左到右的逻辑顺序相匹配，从而提高索引扫描性能。
**/
DBCC INDEXDEFRAG (Northwind, Orders, CustomersOrders)

--4/下例将 UserDB 用户数据库中的文件减小，以使 UserDB 中的文件有 10% 的可用空间。
DBCC SHRINKDATABASE (UserDB, 10)  /**(收缩指定数据库中的数据文件大小。)**/

--5/DBCC SHRINKFILE( 适用于当前数据库中的文件)
收缩相关数据库的指定数据文件或日志文件大小。
DBCC SHRINKFILE (DataFil1, 7)

--6/DBCC UPDATEUSAGE
报告和更正 sysindexes 表的不正确内容，该内容可能会导致通过 sp_spaceused 系统存储过程产生不正确的空间使用报表。

二:验证语句   对数据库、表、索引、目录、文件组、系统表或数据库页的分配进行的验证操作。

--6/DBCC CHECKALLOC
检查指定数据库的磁盘空间分配结构的一致性。
-- Check the current database
DBCC CHECKALLOC
-- Check the pubs database.
DBCC CHECKALLOC ('pubs')

--7/DBCC CHECKCATALOG
检查指定数据库中的系统表内及系统表间的一致性。
-- Check the current database.
DBCC CHECKCATALOG
GO
-- Check the pubs database.
DBCC CHECKCATALOG ('pubs')

--8/DBCC CHECKCONSTRAINTS
检查指定表上的指定约束或所有约束的完整性。
DBCC CHECKCONSTRAINTS ('authors')
--下例检查当前数据库表上的所有启用和禁用约束的完整性。
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

--9/DBCC CHECKDB
/**
检查指定数据库中的所有对象的分配和结构完整性。
对于数据库中每个表，DBCC CHECKDB 检查其：
索引和数据页是否已正确链接。
索引是否按照正确的顺序排列。
各指针是否一致。
每页上的数据是否均合理。
页面偏移量是否合理。
**/
DBCC CHECKDB

下例检查当前数据库，并禁止显示所有信息性消息。
DBCC CHECKDB WITH NO_INFOMSGS

--10/DBCC CHECKFILEGROUP
/**
DBCC CHECKFILEGROUP 和 DBCC CHECKDB 是相似的 DBCC 语句。
主要差别在于 DBCC CHECKFILEGROUP 所执行的检查：仅限于单个的指定文件组和所需的表。
**/
DBCC CHECKFILEGROUP

--11/DBCC CHECKIDENT
检查指定表的当前标识值，如有必要，还对标识值进行更正。
DBCC CHECKIDENT (jobs)
DBCC CHECKIDENT (jobs, NORESEED)

--12/DBCC CHECKTABLE
检查指定表或索引视图的数据、索引及 text、ntext 和 image 页的完整性。
DBCC CHECKTABLE ('authors')

--13/DBCC NEWALLOC
检查数据库的扩展结构内的每个表的数据和索引页的分配。
DBCC NEWALLOC 等同于 DBCC CHECKALLOC

三:状态语句
--1、DBCC INPUTBUFFER (spid)
  显示从客户端发送到 Microsoft® SQL Server™ 的最后一个语句。
  execute sp_who
  DBCC inputbuffer (10)

--2、DBCC SHOWCONTIG
显示指定的表的数据和索引的碎片信息。

--（1）显示带指定表名的表的碎片信息。
USE Northwind
GO
DBCC SHOWCONTIG (Employees)
GO
--（2）使用 OBJECT_ID 和 sysindexes 获得 authors 表的 aunmind 索引的表 ID 和索引 ID。
USE pubs
GO
DECLARE @id int, @indid int
SET @id = OBJECT_ID('authors')
SELECT @indid = indid
FROM sysindexes
WHERE id = @id
   AND name = 'aunmind'
DBCC SHOWCONTIG (@id, @indid)
GO
--（3）显示 pubs 数据库中每个表上的每个索引的完整表结果集。
USE pubs
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES

--（4）使用 DBCC SHOWCONTIG 和 DBCC INDEXDEFRAG 对数据库中的索引进行碎片整理
   显示对数据库中碎片数量在声明的阈值之上的所有索引进行碎片整理的简单方法。

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0

-- Declare cursor
DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE  (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
   FETCH NEXT
      FROM tables
      INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%'
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')'
   EXEC (@execstr)

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
GO
SELECT * FROM #fraglist

--3/DBCC SQLPERF
提供有关所有数据库中的事务日志空间使用情况的统计信息。
DBCC SQLPERF ( LOGSPACE )

--4/DBCC TRACESTATUS
显示跟踪标记的状态。
DBCC tracestatus (-1)--对当前跟踪标记的状态的显示
DBCC tracestatus (1024)

--5/DBCC USEROPTIONS
返回当前连接的活动（设置）的 SET 选项。
DBCC USEROPTIONS

四：杂项语句
--1/ DBCC dllname (FREE)
从内存中卸载指定的扩展存储过程动态链接库 (DLL)。
当执行扩展存储过程时，DLL 仍保持由 Microsoft® SQL Server™ 装载，直到关闭服务器为止。此语句允许从内存中卸载 DLL，而不用关闭 SQL Server。
执行 sp_helpextendedproc 以显示当前由 SQL Server 装载的 DLL 文件。

--2/DBCC HELP
返回指定的 DBCC 语句的语法信息。
DBCC help ('?')
DECLARE @dbcc_stmt sysname
SET @dbcc_stmt = 'CHECKDB'
DBCC HELP (@dbcc_stmt)

--3/DBCC TRACEOFF
禁用指定的跟踪标记。
DBCC traceoff(-1)

--DBCC TRACEON
打开（启用）指定的跟踪标记。

--4/DBCC UNPINTABLE
将表标记为不在内存驻留。将表标记为不在内存驻留后，可以清空高速缓存中的表页。
--DBCC UNPINTABLE ( database_id , table_id )
DECLARE @db_id int, @tbl_id int
USE pubs
SET @db_id = DB_ID('pubs')
SET @tbl_id = OBJECT_ID('pubs..authors')
DBCC UNPINTABLE (@db_id, @tbl_id)

DB_ID
返回数据库标识 (ID) 号。

USE master
SELECT name, DB_ID(name) AS DB_ID
FROM sysdatabases
ORDER BY dbid

OBJECT_ID
返回数据库对象标识号。
USE master
SELECT OBJECT_ID('pubs..authors')