DBCC CheckDB command 用法

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了DBCC CheckDB command 用法，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4385字，纯文字阅读大概需要7分钟。

内容图文

verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF select db.name, db.is_auto_update_stats_async_on from sys.databases db

2 设置DB 处于single user 访问模式

--set single user can access database
alter database db_study
set single_user with ROLLBACK IMMEDIATE

Syntax

ALTER DATABASE  database_name
SET <db_user_access_option> 
[ WITH <termination> ] 

<db_user_access_option> ::=
    { SINGLE_USER | RESTRICTED_USER | MULTI_USER }
<termination>  ::= 
{
    ROLLBACK AFTER integer [ SECONDS ] 
  | ROLLBACK IMMEDIATE 
  | NO_WAIT
}

 

<db_user_access_option> ::=

Controls user access to the database.

SINGLE_USER

Specifies that only one user at a time can access the database. If SINGLE_USER is specified and there are other users connected to the database the ALTER DATABASE statement will be blocked until all users disconnect from the specified database. To override this behavior, see the WITH <termination> clause.

The database remains in SINGLE_USER mode even if the user that set the option logs off. At that point, a different user, but only one, can connect to the database.

Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. To view the status of this option, query the is_auto_update_stats_async_on column in the sys.databases catalog view. If the option is set to ON, perform the following tasks:

1. Set AUTO_UPDATE_STATISTICS_ASYNC to OFF.

2. Check for active asynchronous statistics jobs by querying the sys.dm_exec_background_job_queue dynamic management view.

If there are active jobs, either allow the jobs to complete or manually terminate them by using KILL STATS JOB.

RESTRICTED_USER

RESTRICTED_USER allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but does not limit their number. All connections to the database are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. After the database has transitioned to the RESTRICTED_USER state, connection attempts by unqualified users are refused.

MULTI_USER

All users that have the appropriate permissions to connect to the database are allowed.

The status of this option can be determined by examining the user_access column in the sys.databases catalog view or the UserAccess property of the DATABASEPROPERTYEX function.

 

WITH <termination> ::=

Specifies when to roll back incomplete transactions when the database is transitioned from one state to another. If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely if there is any lock on the database. Only one termination clause can be specified, and it follows the SET clauses.

ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE

Specifies whether to roll back after the specified number of seconds or immediately.

NO_WAIT

Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.

3，exec dbcc checkdb command

The Repair_rebuild option attempts to repair errors only when losing data is not possible.

--exec dbcc checkdb
dbcc checkdb(‘db_study‘,repair_rebuild)

The Repair_Allow_Data_Loss option attempts to repair all errors, including those in which data will likely be lost(the option name was carefully chosen).

--exec dbcc checkdb command
dbcc checkdb(‘db_study‘,repair_allow_data_loss)

Syntax

DBCC CHECKDB 
[
    [ ( database_name | database_id | 0
        [ , NOINDEX 
        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
    ) ]
    [ WITH 
        {
            [ ALL_ERRORMSGS ]
            [ , EXTENDED_LOGICAL_CHECKS ] 
            [ , NO_INFOMSGS ]
            [ , TABLOCK ]
            [ , ESTIMATEONLY ]
            [ , { PHYSICAL_ONLY | DATA_PURITY } ]
        }
    ]
]

MSDN URL：https://msdn.microsoft.com/zh-cn/library/ms176064.aspx

4，将db的access mode 切换到multiple user

-- switch to multiple user access mode
alter database [SIS_DataWarehouseV2_SIS]
set MULTI_USER with ROLLBACK IMMEDIATE

5， 查看 msdb.dbo.suspect_pages

url：https://msdn.microsoft.com/en-us/library/ms191301(v=sql.110).aspx

 

参考文档：

https://msdn.microsoft.com/en-us/library/bb522682.aspx

DBCC CheckDB command 用法

标签：

本文系统来源：http://www.cnblogs.com/ljhdo/p/5057323.html

内容总结

以上是互联网集市为您收集整理的DBCC CheckDB command 用法全部内容，希望文章能够帮你解决DBCC CheckDB command 用法所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---