SQL Server数据库管理员必备的DBCC命令

DBCC是SQL Server提供的一组控制台命令,功能很强大,掌握一些必要的语句,对操作数据库有不少帮助,所以决定整理一下,发现已有不少类似的整理,减少了不少工作,归类如下:

 

 

一、DBCC 帮助类命令

 

* DBCC HELP('?')

查询所有的DBCC命令

* DBCC HELP('checktable')

查询指定的DBCC命令的语法说明

* DBCC USEROPTIONS

返回当前连接的活动(设置)的SET选项

 

二、DBCC 检查验证类命令

 

* DBCC CHECKALLOG ('数据库名称')

检查指定数据库的磁盘空间分配结构的一致性

* DBCC CHECKCATALOG ('数据库名称')

检查指定数据库的系统表内和系统表间的一致性

* DBCC CHECKCONSTAINTS ('tablename')

检查指定表上的指定约束或所有约束的完整性

* DBCC CHECKDB

检查数据库中的所有对象的分配和结构完整性

* DBCC CHECKFILEGROUP

检查指定文件组中所有表在当前数据库中的分配和结构完整性

* DBCC CHECKTABLE

检查指定表或索引视图的数据、索引及test、ntest和image页的完整性

* DBCC CHECKIDENT

检查指定的当前标识值

* DBCC SQLPERF(UMSSTATS) undocumented in BOL

可以用来检查是否CPU使用达到瓶颈

最关键的一个参考数据num runnable,表明当前有多少个线程再等待运行

如果大于等于,考虑CPU达到瓶颈

 

三、DBCC 维护类命令

 

* DBCC CLEANTABLE ('db_name','table_name')

回收Alter table drop column语句删除可变长度列或text

* DBCC DBREINDEX

重建指定数据库的一个或多个索引

* DBCC INDEXDEFRAG

对表或视图上的索引和非聚集索引进行碎片整理

* DBCC PINTABLE (db_id,object_id)

将表数据驻留在内存中

查看哪些表驻留在内存的方法是:

select objectproperty(object_id('tablename'),'tableispinned')

* DBCC UNPINTABLE (db_id,object_id)

撤消驻留在内存中的表

* DBCC SHRINKDATABASE(db_id,int)

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

* DBCC SHRINKFILE(file_name,int)

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

 

四、DBCC 性能调节命令

 

* DBCC dllname(FREE)

sp_helpextendedproc 查看加载的扩展PROC

在内存中卸载指定的扩展过程动态链接库(dll)

* DBCC DROPCLEANBUFFERS

从缓冲池中删除所有缓冲区

* DBCC FREEPROCCACHE

从过程缓冲区删除所有元素

* DBCC INPUTBUFFER

显示从客户机发送到服务器的最后一个语句

* DBCC OPENTRAN (db_name)

查询某个数据库执行时间最久的事务,由哪个程序拥有

* DBCC SHOW_STATISTICS

显示指定表上的指定目标的当前分布统计信息

* DBCC SHOWCONTIG

显示指定表的数据和索引的碎片信息

* DBCC SQLPERF

(logspace) 查看各个DB的日志情况

(iostats) 查看IO情况

(threads) 查看线程消耗情况

返回多种有用的统计信息

* DBCC CACHESTATS

显示SQL Server 2000内存的统计信息

* DBCC CURSORSTATS

显示SQL Server 2000游标的统计信息

* DBCC MEMORYSTATS

显示SQL Server 2000内存是如何细分的

* DBCC SQLMGRSTATS

显示缓冲中先读和预读准备的SQL语句

 

五、DBCC 未公开的命令

 

* DBCC ERRLOG

初始化SQL Server 2000的错误日志文件

* DBCC FLUSHPROCINDB (db_id)

清除SQL Server 2000服务器内存中的某个数据库的存储过程缓存内容

* DBCC BUFFER (db_name,object_name,int(缓冲区个数))

显示缓冲区的头部信息和页面信息

* DBCC DBINFO (db_name)

显示数据库的结构信息

* DBCC DBTABLE

显示管理数据的表(数据字典)信息

* DBCC IND (db_name,table_name,index_id)

查看某个索引使用的页面信息

* DBCC REBUILDLOG

重建SQL Server 2000事务日志文件

* DBCC LOG (db_name,3) (-1--4)

查看某个数据库使用的事物日志信息

* DBCC PAGE

查看某个数据库数据页面信息

* DBCC PROCBUF

显示过程缓冲池中的缓冲区头和存储过程头

* DBCC PRTIPAGE

查看某个索引页面的每行指向的页面号

* DBCC PSS (user,spid,1)

显示当前连接到SQL Server 2000服务器的进程信息

* DBCC RESOURCE

显示服务器当前使用的资源情况

* DBCC TAB (db_id,object_id)

显示数据页面的结构

 

六、DBCC跟踪标记

 

跟踪标记用于临时设置服务器的特定特征或关闭特定行为,常用于诊断性能问题或调试存储过程或复杂的计算机系统

* DBCC TRACEON (3604)

打开跟踪标记

* DBCC TRACEOFF

关闭跟踪标记

* DBCC TRACESTATS

查看跟踪标记状态

 

七、使用 DBCC 结果集输出

 

　　许多 DBCC 命令可以产生表格格式的输出(使用 WITH TABLERESULTS 选项)。该信息可装载到表中以便将来使用。以下显示一个示例脚本:

 

　　

　　CREATE TABLE DBCCResult (

 

　　DBCCFlag INT,

 

　　Result INT

 

　　)

 

　　INSERT INTO DBCCResult

 

　　EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')

 

　　SELECT *

 

　　FROM DBCCResult

八、官方使用DBCC的建议

1、在系统使用率较低时运行 CHECKDB。

2、请确保未同时执行其它磁盘 I/O 操作,例如磁盘备份。

3、将 tempdb 放到单独的磁盘系统或快速磁盘子系统中。

4、允许 tempdb 在驱动器上有足够的扩展空间。使用带有 ESTIMATE ONLY 的 DBCC

估计 tempdb 将需要多少空间。

5、避免运行占用大量 CPU 的查询或批处理作业。

6、在 DBCC 命令运行时,减少活动事务。

7、使用 NO_INFOMSGS 选项显著减少处理和 tempdb 的使用。

8、考虑使用带有 PHYSICAL_ONLY 选项的 DBCC CHECKDB 来检查页和记录首部

的物理结构。当硬件导致的错误被置疑时,这个操作将执行快速检查。

 

在发布,订阅复制时要用服务器实名时可以这样:

 

select * from sysservers   (可以找到原来服务器的名称)

 

exec sp_dropserver 'jmsql9'    (删除原来的服务器名)

 

exec sp_addserver 'jmSQL9' ,LOCAL    (改为新的服务器名)

 

ALTER DATABASE [jm] SET SINGLE_USER          (改为单用户模式)

 

DBCC CHECKDB("databasename",REPAIR_REBUILD) WITH TABLOCK    (修复数据库) 

 

DBCC  CHECKTABLE("tablename",repair_rebuild) with tablock  (修复表)

 

DBCC DBREINDEX ('t_icitem'  ,  '   ')       修复此表所有的索引。

 

ALTER DATABASE [jm] SET MULTI_USER                    (改为多用户模式)

 

REPAIR_ALLOW_DATA_LOSS:执行由REPAIR_REBUILD 完成的所有修复,包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误,以及删除已损坏的文本对象。这些修复可能会导致一些数据丢失。修复操作可以在用户事务下完成以允许用户回滚所做的更改。如果回滚修复,则数据库仍会含有错误,应该从备份进行恢复。如果由于所提供修复等级的缘故遗漏某个错误的修复,则将遗漏任何取决于该修复的修复。修复完成后,备份数据库。

 

REPAIR_FAST 进行小的、不耗时的修复操作,如修复非聚集索引中的附加键。这些修复可以很快完成,并且不会有丢失数据的危险。

 

REPAIR_REBUILD 执行由REPAIR_FAST 完成的所有修复,包括需要较长时间的修复(如重建索引),执行这些修复时不会有丢失数据的危险。

 

dbcc shrinkdatabase  (jm)      压缩数据库