本文共 1685 字,大约阅读时间需要 5 分钟。
Transact-SQL 编程语言提供 DBCC语句作为 Microsoft SQL Server的数据库控制台命令。这些语句对数据库的物理和逻辑一致性进行检查。许多 DBCC 语句能够对检测到的问题进行修复。
检测出问题点,在具体修复
dbcc checkdb (RMS_APP_NLH)
WITH ROLLBACK IMMEDIATE 是做设置单用户总是不成功的时候,加入WITH ROLLBACK IMMEDIATE 选项可以快速回滚事务,变成单用户模式。
use RMS_APP_NLH
go
alter database RMS_APP_NLHset single_user WITH ROLLBACK IMMEDIATE;
go
dbcc checktable('R_Traffic_Record',REPAIR_ALLOW_DATA_LOSS)
go
dbcc checktable('R_Traffic_Record',REPAIR_REBUILD)
go
dbcc checktable('S_103_Quarter',REPAIR_ALLOW_DATA_LOSS)
go
dbcc checktable('S_103_Quarter',REPAIR_REBUILD)
alter database RMS_APP_NLHset multi_user
go
DBCC检测的时候不能定位表或者对象。
-----------------------游标循环 check table------------------
DECLARE NYCURSOR CURSOR
--声明游标
FOR
SELECT name
FROM sys.tables
WHERE is_ms_shipped = 0;
OPEN NYCURSOR;
DECLARE @TAB_NAME VARCHAR(1000);
FETCH NEXT FROM NYCURSOR INTO @TAB_NAME;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @TAB_NAME = 'THIS4.dbo.' + @TAB_NAME;
DBCC CHECKTABLE (@TAB_NAME);
--DBCC CHECKTABLE运行
FETCH NEXT FROM NYCURSOR INTO @TAB_NAME;
END;
CLOSE NYCURSOR;
DEALLOCATE NYCURSOR;
use RMS_APP_NLH
go
alter database RMS_APP_NLHset single_user
go
dbcc checktable('R_Traffic_Record',REPAIR_ALLOW_DATA_LOSS)
go
dbcc checktable('R_Traffic_Record',REPAIR_REBUILD)
go
dbcc checktable('S_103_Quarter',REPAIR_ALLOW_DATA_LOSS)
go
dbcc checktable('S_103_Quarter',REPAIR_REBUILD)
alter database RMS_APP_NLHset multi_user
go
alter database t_branch set emergency
alter database t_branch set single_user
dbcc checkdb ('t_branch',repair_allow_data_loss)
dbcc checkdb ('t_branch',repair_rebuild)
alter database t_branch set multi_user
转载地址:http://mxbai.baihongyu.com/