笔记17-徐 用户数据库常见问题和解决方法 附案例

笔记17-徐 用户数据库常见问题和解决方法 附案例

--用户数据库常见问题和解决方法 附案例
--查询数据库的状态
SELECT state_desc FROM sys.databases

--1、文件打开问题  消息：database 'sales' cannot be opened due to inaccessible files or insufficient memory or disk space
--错误日志文件显示内容：
--一、主文件组：
--(1)访问主文件组的主文件出错
--starting up database 'sales'
--error:17207 ,severity:16 ,state:1
--FCB::OPEN operating system error2(cannot find the file specified) occured while creating or opening file
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\spridat.mdf'
--(2)访问主文件组的辅助文件出错
--starting up database 'sales'
--error :17207,severity:16 ,state:1
--FileMgr::StartPrimaryDataFiles operating system error2(cannot find the file specified) occured while creating or opening file
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\spridat.ndf'

--不管是上面哪一个情况，数据库都无法正常开启。只有将文件上的问题解决，数据库才能打开，如果问题不能解决，只能恢复数据库备份

--二、辅助文件组：
--starting up database 'sales'
--error :17207,severity:16 ,state:1
-- FileMgr::StartSecondaryDataFiles:operating system error2(cannot find the file specified) occured while creating or opening file
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\spridat2.ndf'

--和主文件组不同，一个数据库可以在辅助文件组OFFLINE的时候打开。我们可以标记辅助文件组OFFLINE，重新打开数据库
ALTER DATABASE sales MODIFY FILE(NAME=spridat2,OFFLINE)
ALTER DATABASE sales SET ONLINE

--这样打开的数据库能够看到主文件组和其他辅助文件组里的内容，但是offline的那个文件组内容不可见。
--这是你可以将数据导出到一个新数据库，或者用文件组备份恢复的方式恢复那个出问题的文件组

--三、日志文件
--简单模式：当日志文件不可访问的时候，如果数据库的恢复模式为简单恢复模式，上次数据库关闭正常（所有该提交的事务都已经写入硬盘
--，所有该回滚的事务都已经撤销），那么在下次SQL启动这个数据库的时候，如果发现日志文件不存在，则会自动创建一个新的
--日志文件。这也是为什麽有时候日志文件被删除后，数据库照样能启动的原因。

--完整模式：如果上次数据库没有正常关闭，或者恢复模式是完整，SQL就不会给这个数据库创建日志文件。

--错误日志文件显示：
--starting up database 'sales'
--error :17207,severity:16 ,state:1
-- FileMgr::StartLogFiles:operating system error2(cannot find the file specified) occured while creating or opening file
--'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\salelog.ldf'
--the log cannot be rebuilt because the database was not cleanly shut down

--如果我们不能修复日志上的错误，那么最好的方法是恢复数据库备份。
--因为这时候数据文件里的数据正处在一个不一致的状态。可能有些事务已经提交了，但是数据在日志文件里
--也有一些事务已经撤销了，但数据文件里的数据还是撤销前的状态。如果这些信息找不回来，就会发生事务没有
--完全提交或回滚的现象。

--解决日志文件错误办法：
--checkdb:后果是有数据丢失
--（1）SQL2000:DBCC REBUILD_LOG 跟SQL2005效果差不多
--（2）SQL2005:如下
ALTER DATABASE sales SET EMERGENCY
GO
ALTER DATABASE sales SET SINGLE_USER
GO
DBCC CHECKDB(sales,REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE sales SET MULTI_USER
GO




--2、恢复失败
--数据库进入恢复后，要做重做(roll forward) 和撤销(roll back)的工作中间可能会遇到3类问题
--（1）在重做过程中遇到的能延迟的错误(deferrable error)
--（2）在撤销的过程中遇到能延迟的错误
--（3）在任何时间遇到的不能延迟的错误

--（1）重做遇到的错误
--举到的例子：由于1:153上的数据没有被正常重做，所以这个页面是不能正常访问的。SQL会标记这个页面为RestorePending
--任何操作想要访问这个页面，SQL都会抛出下面错误
--Database ID 5 ,PAGE(1:153)is marked RestorePending
--如果想要修复上面的错误，必须运行dbcc checkdb 或者使用数据库备份进行恢复


--（2）撤销过程中的失败
--举到的例子：从这些信息可以看到，SQL在页面1:153上面发生访问错误，以至于日志(51:104:5)所代表
--的那个事务不能正常回滚。SQL标记这个事务（不光是这个页面）为延迟事务，然后继续其他撤销工作。
--最后数据库正常上线。但是由于有事务没有被正常回滚，所以在和这个事务有关的那些对象上，SQL
--申请了一些锁，以防止问题解决之前有其他用户访问这些对象。锁的主人编号是-3。
--你可以运行下面代码来检查SQL有没有这样的锁
SELECT
CONVERT(CHAR(22),resource_description)AS resource_desc,
CONVERT(CHAR(15),resource_type) AS resource_type,
request_session_id,
CONVERT(CHAR(15),request_mode) AS request_mode,
CONVERT(CHAR(15),request_status) AS request_status
from sys.dm_tran_locks
WHERE request_session_id=-3

--和RestorePending的页面一样，要修复页面，最好的方式是备份恢复。因为这样能保证数据库里
--的数据都是事务一致的，而且也对数据的丢失量心中有数。
--如果没有备份，只能DBCC CHECKDB或者DBCC CHECKTABLE ，修复物理损坏，但是逻辑错误就无能为力了
--因为管理员也不知道到底哪些数据出了逻辑问题
ALTER DATABASE sales SET EMERGENCY
GO
ALTER DATABASE sales SET SINGLE_USER
GO
DBCC CHECKDB(sales,REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE sales SET MULTI_USER
GO
-------------------------------------------
--DBCC CHECKTABLE()
dbcc checktable('需要修复的数据表的名称',REPAIR_ALLOW_DATA_LOSS)
dbcc checktable('需要修复的数据表的名称',REPAIR_REBUILD)


--（3）可疑模式
--当数据库做恢复的时候遇到的错误很严重，无法延迟错误时，整个数据库会进入可疑模式。
--这时整个数据库都无法访问。发生这种情况，
--1要不就是数据库损坏范围太大，
--2要不就是一些比较重要的数据库分配页出现了问题。
--3如果因为是硬件的问题，可以在硬件修复后，重启SQL，SQL会对每个数据库都做恢复，哪怕这个数据库上次被标记为可疑。


--可疑模式解决办法：
--（1）恢复备份
--（2）先移走数据库日志文件，重启SQL，数据库会进入RECOVERY_PENDING状态，然后
ALTER DATABASE sales SET EMERGENCY
GO
ALTER DATABASE sales SET SINGLE_USER
GO
DBCC CHECKDB(sales,REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE sales SET MULTI_USER
GO
--SQL会创建一个新的日志文件。由于这个日志文件是空的，数据库无须做任何重做和撤销的工作，
--也就不容易进入可疑模式。数据库可以被打开，但是很多有问题的对象还是不能访问，只能将它们
--导入到新建的数据库里面，能挽救多少是多少。
--（3）只有符号要求的备份才能挽救数据




--------------------------------------------------------------------------------
--案例
-----------------------------------------------------------------------------------
--数据库恢复模式为 完整 ，先前做过一次完整备份。原先的表格里所有的值都是AAAA...
--上次SQL关闭的时候，有个将100条记录修改成BBBB....的事务没来得及提交。
UPDATE test SET col2=REPLICATE('B',300) WHERE col1<100

--现在它启动的时候，由于数据文件发生损坏，在撤销时遇到1:200页面上的824错误，
--相关事务被标记为延迟的事务
--数据库能正常打开，运行下面的查询会有许多被-3持有的锁，不但在key上，还在很多page上

--查询SQL现有的锁
USE pagenumber
GO
SELECT
p.object_id,
OBJECT_NAME(p.object_id) AS OBJECT_NAME,
request_session_id,
resource_type,
resource_description,
request_mode,
resource_associated_entity_id,
request_status
FROM sys.dm_tran_locks AS lock LEFT JOIN sys.partitions p
ON lock.resource_associated_entity_id=p.hobt_id
WHERE resource_database_id=DB_ID('pagenumber')
ORDER BY request_session_id,request_mode,resource_type,resource_associated_entity_id
GO

--如果运行下面的查询，就会被阻塞住
SELECT * FROM pagenumber..test

--查询sys.sysprocesses视图，就能够看到连接被-3阻塞住了
SELECT * FROM sys.sysprocesses WHERE spid>50

--两个方式修复数据库
--(1)DBCC CHECKDB
ALTER DATABASE pagenumber SET EMERGENCY
GO
ALTER DATABASE pagenumber SET SINGLE_USER
GO
DBCC CHECKDB(pagenumber,REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE pagenumber SET MULTI_USER
GO

--结果:错误的结果 只撤销了部分记录修改

--(2)由于数据库是完整恢复模式,先前又做过完整备份,所以可以用页面恢复的方式,很快恢复数据库
--到一个一致的状态
--首先需要找到所有有问题的页面，查看errorlog，或者数据库不大的话可以在紧急模式运行不带参数的
--checkdb，把所有错误页面打出来

ALTER DATABASE pagenumber SET EMERGENCY
GO
DBCC CHECKDB('pagenumber')
GO
ALTER DATABASE pagenumber SET ONLINE
GO
--现在我们知道是1:200这个页面有问题，就只需恢复这个页面就可以了
USE master
GO
RESTORE DATABASE pagenumber PAGE='1:200'
FROM DISK='pagenumber.bak'


--这时候查询还是不能正常运行，因为那个事务还没有回滚。现在我们做一次尾日志备份
BACKUP LOG pagenumber TO DISK='pagenumbergood.trn' WITH init,FORMAT
GO

--然后我们把日志备份依次恢复。这里假设只有刚才做的一份
RESTORE LOG pagenumber FROM DISK='pagenumbergood.trn' WITH recovery
GO
--现在再做查询可以发现数据是一致的


--用checkdb命令恢复处理的数据虽然在物理上恢复了正常，但是一些可能已经是错误的了。
--所以管理员要少用checkdb+repair_allow_data_loss，尽可能使用备份恢复的方法