sql server系统页错误（GAM、SGAM、PFS）

【1】dbcc checkdb发现错误信息

dbcc checkdb系统页错误（GAM、SGAM、PFS）

　　、

事务日志备份也全部报错

【2】查看错误页类型信息

【2.1】查看 msdb..suspect_pages 获取问题页类型

SELECT DB_NAME(database_id) dbname,[file_id],page_id,
CASE event_type
WHEN 1 THEN '823 or 824 or Torn Page'
WHEN 2 THEN 'Bad Checksum'
WHEN 3 THEN 'Torn Page'
WHEN 4 THEN 'Restored'
WHEN 5 THEN 'Repaired (DBCC)'
WHEN 7 THEN 'Deallocated (DBCC)'
END event_type,
error_count,
last_update_date
FROM msdb..suspect_pages

【2.2】查看错误页是属于哪种系统页（PFS）

Declare @PageID int;
-- Enter page number
-- e.g., 8088 = PFS page
Set @PageID = 32352;
Select Case
    When @PageID = 1 Or @PageID % 8088 = 0 Then 'Is PFS Page'
    When @PageID = 2 Or @PageID % 511232 = 0 Then 'Is GAM Page'
    When @PageID = 3 Or (@PageID - 1) % 511232 = 0 Then 'Is SGAM Page'
    Else 'Is Not PFS, GAM, or SGAM page'
    End

【3】无法使用dbcc checkdb repair_allow_data_loss 修复，报同样的错误

【4】解决思路

【4.-1】尝试直接修复（DBCC CHECKALLOC(dbname,REPAIR_ALLOW_DATA_LOSS )）

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

该命令官网：https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-checkalloc-transact-sql?view=sql-server-ver15

【4.0】利用备份页还原（推荐）

深入参考：https://www.cnblogs.com/gered/p/9435282.html

SQL Server页级别的数据恢复
--1.最近的完整备份
BACKUP DATABASE DBName TO DISK = N'C:\Test.bak'
 
--2.发现错误页(可以人为破坏)
SELECT * FROM msdb.dbo.suspect_pages
 
--3.立即备份日志1
BACKUP LOG DBName TO
DISK = 'C:\Test_LOG1.bak'
WITH INIT
 
--3.用完整备份,还原数据损坏的页
USE Master
RESTORE DATABASE DBName
PAGE = '1:832'
FROM DISK = 'C:\Test.bak'
WITH NORECOVERY
 
--4.备份日志2
BACKUP LOG DBName TO
DISK = 'C:\Test_LOG2.bak'
WITH INIT
 
--5.还原日志1
RESTORE LOG DBName FROM
DISK = 'C:\Test_LOG1.bak'
WITH NORECOVERY
 
--6.还原日志2
RESTORE LOG DBName FROM
DISK = 'C:\Test_LOG2.bak'
WITH NORECOVERY
 
--7.还原数据库状态,大工告成
RESTORE DATABASE DBName WITH RECOVERY
 
/*
人为破坏数据页
1.查看表使用的数据页
　　DBCC IND(DBName, TableName, -1)
2.修改数据库访问模式
　　ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
3.破坏数据页(1是PageFID,832是PagePID)
　　DBCC WRITEPAGE(DBName, 1, 832, 0, 1, 0x41, 1)
4.修改数据库访问模式
　　ALTER DATABASE DBName SET MULTI_USER
*/

【4.1】库小的时候

把库迁移到本实例另外一个新建的库

大概步骤：（也可以用Bcp）

（1）如果是2008级以上

　　　　停机=》利用右击数据库=》任务=》生成脚本得到数据库对象架构、数据脚本

（2）如果是2005

　　　　停机=》利用右击数据库=》任务=》生成脚本得到数据库对象架构脚本（不包含表数据）

　　　　然后利用 sys.tables来进行 insert into select ......迁移数据到另外一个库

具体操作：

直接利用SSMS自带的生成脚本，右击数据库=》任务=》生成脚本

　　然后选择架构和数据，相关的触发器、索引都可以。2008可以选择架构和数据，2005只能选择架构。

但2005，只能生成架构语句，也就是 create脚本，并不能生成对应的 insert value 。

所以2005的玩家，需要额外利用sys.tables把故障库的数据，利用 insert into select 插入到新库。

--要使用，@new_db内容用查找替换来全部替换掉，否则会有问题的

declare @old_db varchar(100),@new_db varchar(100),@sql varchar(max)
select @old_db='[10.1.4.234].db_tank',@new_db='test2'

-- 获取有自增列插入语句
select 'use '+@new_db+';set identity_insert '+t1.name+' on;insert into '+@new_db+'.dbo.'+t1.name+'('+
(select stuff((select',['+name+']' from test2.sys.syscolumns where id=t1.object_id for xml path('')),1,1,''))+')'+
' select '+(select stuff((select',['+name+']' from test2.sys.syscolumns where id=t1.object_id for xml path('')),1,1,''))
+' from '+@old_db+'.dbo.'+t1.name+';set identity_insert '+t1.name+' off;' 
from test2.sys.tables t1 
where exists 
(
select 1 from test2.sys.columns t2 where t2.is_identity=1 and t1.object_id=t2.object_id 
) 
and t1.type='u'


-- 获取无自增列插入语句 

select 'use '+@new_db+';insert into '+@new_db+'.dbo.'+t1.name+' select * from '+@old_db+'.dbo.'+t1.name+';' 
from test2.sys.tables t1 
where not exists 
(
select 1 from test2.sys.columns t2 where t2.is_identity=1 and t1.object_id=t2.object_id 
) 
and t1.type='u'

--------------核验--------------------
-- 更新统计信息
use test2
EXEC sys.sp_updatestats

use db_logs
EXEC sys.sp_updatestats
-- 根据聚集索引/堆对比行数

use master;
if object_id('temp1') is not null
drop table master.dbo.temp1

select t2.name tab_name,rows,indid into master..temp1 
from [10.1.4.234].db_tank.sys.sysindexes t1 join [10.1.4.234].db_tank.sys.sysobjects t2 on t1.id=t2.id 
where t1.indid in(0,1) and t2.type!='S'

use master;
if object_id('temp2') is not null
drop table master.dbo.temp2

select t2.name tab_name,rows,indid into master..temp2 
from test2.sys.sysindexes t1 join test2.sys.sysobjects t2 on t1.id=t2.id 
where t1.indid in(0,1) and t2.type!='S'

 
-- 根据聚集索引/堆对比行数，筛选行数不一致的
select t1.*,t2.*,t1.rows-t2.rows as flag from 
master..temp1 t1 join
master..temp2 t2 on t1.tab_name=t2.tab_name
where t1.rows-t2.rows>0
-- 对比表存在情况
select t1.*,t2.*,t1.rows-t2.rows as flag from 
master..temp1 t1 full join
master..temp2 t2 on t1.tab_name=t2.tab_name
where t1.tab_name is null or t2.tab_name is null