数据库紧急修复

dbcc checkdb

alter database MALL_Test set emergency　　--设置紧急模式
ALTER DATABASE MALL_Test SET ONLINE

EXEC sp_dboption 'MALL_Test', 'single user', 'TRUE'　　--单用户
DBCC CHECKDB('MALL_Test',REPAIR_FAST)
DBCC CHECKDB('MALL_Test',REPAIR_REBUILD)
dbcc checkdb('MALL_Test',repair_allow_data_loss)

DBCC CHECKTABLE('Game_PlayLog',REPAIR_ALLOW_DATA_LOSS)
DBCC CHECKTABLE('Game_PlayLog',REPAIR_REBUILD)
EXEC sp_dboption 'MALL_Test', 'single user','FALSE'　　--多用户

 

ALTER DATABASE Mall_Test SET SINGLE_USER with rollback IMMEDIATE
ALTER INDEX ALL ON Game_PlayLog
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON)
DBCC CHECKTABLE('Game_PlayLog',REPAIR_ALLOW_DATA_LOSS)
DBCC CHECKTABLE('Game_PlayLog',REPAIR_REBUILD)
ALTER DATABASE Mall_Test SET MULTI_USER　　--多用户

 

检查数据库死锁：

select request_session_id spid,  
    OBJECT_NAME(resource_associated_entity_id) tableName   
from   
    sys.dm_tran_locks  
where   
    resource_type='OBJECT'

检查外键和索引：

-- 获取表的外键
SELECT
    fk.name AS '外键名称',
    tp.name AS '父表',
    cp.name AS '父表列',
    tr.name AS '子表',
    cr.name AS '子表列'
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
WHERE 
    tp.name = '你的表名' OR tr.name = '你的表名';

-- 获取表的索引
SELECT 
    t.name AS '表名',
    i.name AS '索引名称',
    i.type_desc AS '索引类型',
    i.is_unique AS '是否唯一',
    i.is_primary_key AS '是否主键',
    i.is_unique_constraint AS '是否唯一约束',
    c.name AS '列名'
FROM 
    sys.indexes i
INNER JOIN 
    sys.tables t ON i.object_id = t.object_id
INNER JOIN 
    sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
INNER JOIN 
    sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
WHERE 
    t.name = '你的表名';

 

https://blog.csdn.net/qq_35844043/article/details/131957779