欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-19 16:06:13
今年没怎么整理博客,不过遇到的问题也非常多。今日整理一个,alter或drop某个存储过程、或者打开存储过程列表时,提示“架构损坏”。-- checkdb 中断报错DBCC CHECKDB(DBName)--类似的,修复液报错DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS);checkdb 中断报错CHECKDB 在数据库 'dbname' 中发现 0 个分配错误和 0 个一致性错误。DBCC 执行完毕。如果 DBCC 输出了错误信息,请与...

今年没怎么整理博客,不过遇到的奇怪问题也非常多。今日整理一个,alter或drop某个存储过程、或者打开存储过程列表时,提示“架构损坏”。

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

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

checkdb 中断报错

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:

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

把以上出现的表查询一遍:

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个系统视图是无法直接查询的,难道到这里就终止了吗?

NO!~不可能的!~

 

要查看这些系统视图,我们需要以专用管理员连接(DAC) 访问。

添加 “-m” 到启动参数,然后重启服务。

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

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

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

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

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 无法修复的系统对象,通过手动修改解决了!

 

 

本文地址:https://blog.csdn.net/kk185800961/article/details/109633086