SQL Server 系统架构损坏手动修复

SQLServer 2021-10-11

1584

CS模式下，数据是放在客户本地的服务器上。而大部分客户并没有专业能力去维护他们的SQL Server，老版本数据库系统设置更是较旧。因此，总是遇到客户反馈的损坏问题。客户 alter 或 drop 某个存储过程、或者打开存储过程列表时，执行中止并提示“架构损坏”。

-- checkdb 中断报错
DBCC CHECKDB(DBName)


-- 类似的，修复也报错
DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS);

CHECKDB 在数据库 'dbname' 中发现 0 个分配错误和 0 个一致性错误。

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

消息 211，级别 23，状态 16，第 1 行

可能发生了架构损坏。请运行 DBCC CHECKCATALOG。

除了这些错误信息，完全不知道哪些表有问题。又对这个库的所有表都 checktable ，也无报错。可以确认，当前的表结构及数据是没问题的，断定是当前数据库的系统表出现了问题。

好吧，打开 Profiler 跟着（RPC:Startding、RPC:Completed、SP:Startding、SP:Completed、SP:StmtStartding、SP:StmtCompleted、SQL:…），甚至还跟踪了锁的请求及释放（有点多余了）。然后删除某报错的存储过程，跟踪到以下SQL：

图一

把以上跟踪出现的涉及表查询一遍：

select * from sys.all_objects
select * from sys.database_principals
select * from sys.sql_modules
select * from sys.system_sql_modules

发现是系统视图 sys.sql_modules 报错！该视图返回函数、视图、存储过程的定义。查看该视图的定义：

sp_helptext 'sys.sql_modules'


--定义
CREATE VIEW sys.sql_modules AS  
 SELECT object_id = o.id,  
  definition = object_definition(o.id),  
  uses_ansi_nulls = sysconv(bit, o.status & 0x40000),    -- OBJMOD_ANSINULLS  
  uses_quoted_identifier = sysconv(bit, o.status & 0x80000),   -- OBJMOD_QUOTEDIDENT  
  is_schema_bound = sysconv(bit, o.status & 0x20000),    -- OBJMOD_SCHEMABOUND  
  uses_database_collation = sysconv(bit, o.status & 0x100000),  -- OBJMOD_USESDBCOLL  
  is_recompiled = sysconv(bit, o.status & 0x400000),     -- OBJMOD_NOCACHE  
  null_on_null_input = sysconv(bit, o.status & 0x200000),   -- OBJMOD_NULLONNULL  
  execute_as_principal_id = x.indepid  
 FROM sys.sysschobjs o  
 LEFT JOIN sys.syssingleobjrefs x ON x.depid = o.id AND x.class = 22 AND x.depsubid = 0 -- SRC_OBJEXECASOWNER  
 WHERE o.pclass <> 100 -- x_eunc_Server  
  AND ((o.type = 'TR' AND has_access('TR', o.id, o.pid, o.nsclass) = 1)  
   OR (type IN ('P','V','FN','IF','TF','RF','IS') AND has_access('CO', o.id) = 1)  
   OR (type IN ('R','D') AND o.pid = 0))

可以看到2个系统视图： sys.sysschobjs、sys.syssingleobjrefs。但是，这2个系统视图是无法直接查询的，难道到这里就终止了吗？不可能的！~

要查看这些系统视图，我们需要以专用管理员连接(DAC) 访问。添加 “-m” 到启动参数，然后重启服务。

图二

直接点击一个查询窗口，以DAC管理员访问：admin:<instancename>

图三

好了，进入损坏的数据库，查询系统视图：

select * from sys.sysschobjs
select * from sys.syssingleobjrefs

其实，这些系统视图，等价于我们常看到的那些系统视图。

Sysobjects = sys.sysschobjs
Syscolumns = sys.syscolpars
Sysindexes = sys.sysidxstats

废话不多说，再执行视图sys.sql_modules 的定义中的一部分sql：

SELECT object_id = o.id,  
definition = object_definition(o.id)
FROM sys.sysschobjs o  
LEFT JOIN sys.syssingleobjrefs x ON x.depid = o.id AND x.class = 22 AND x.depsubid = 0

可以确认是 object_definition 获取定义的函数出错。回头看看那个报错的存储过程，查看其定义：

select object_definition(id),id from sys.sysschobjs where name='usp_mytest'

果然是报错的就是它，错误就是最开始的信息。但是，不确定是否其他对象也可能出错，所以执行以下SQL，把所有输出都执行一遍。

select concat('selelct object_definition(',id,')') from sys.sysschobjs

既然确定了该出错的信息，那么就只能把该行数据删掉了！那要删除哪些表呢？以下这些表，可以都查看一遍，与对象id相关的，都可以查询出来删掉。

select id,name,type,concat('select * from sys.',name) from sys.sysschobjs WHERE NAME LIKE 'sys%' order by type,NAME

以下几张表要删除的：

select id from sys.sysschobjs where name='usp_mytest'


delete from sys.sysschobjs where id=xxxxxxxxxxx
delete from sys.syscolpars where id=xxxxxxxxxxx
delete from sys.syssoftobjrefs where depid=xxxxxxxxxxx

如果只删除 sys.sysschobjs ，checkdb 的时候还是报以下错误，所以把其他相关表也删除。

Attribute (parent_object_id=xxxxxxxxxxx) of row (object_id=xxxxxxxxxxx) in sys.objects does not have a matching row (object_id=xxxxxxxxxxx) in sys.objects.

再执行 checkdb，发现已经没有错误了。上面提到的一些查询也操作正常，SSMS存储过程列表也可以打开了！

--    可创建原来的存储过程
--    Create procedure usp_mytest   
ALTER DATABASE dbname SET EMERGENCY;
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB (dbname) WITH TABLOCK
GO
ALTER DATABASE dbname SET MULTI_USER;
GO
ALTER DATABASE dbname SET ONLINE;
GO

此时，可以把启动参数“-m”去掉，重启服务！至此，完美解决。checkdb 无法修复的系统对象，通过手动修改解决了！

sql server

文章转载自SQLServer，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL Server 系统架构损坏手动修复

评论