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

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

  1 --用户数据库常见问题和解决方法 附案例
  2 --查询数据库的状态
  3 SELECT state_desc FROM sys.databases
  4 
  5 --1、文件打开问题  消息:database 'sales' cannot be opened due to inaccessible files or insufficient memory or disk space
  6 --错误日志文件显示内容:
  7 --一、主文件组:
  8 --(1)访问主文件组的主文件出错
  9 --starting up database 'sales'
 10 --error:17207 ,severity:16 ,state:1
 11 --FCB::OPEN operating system error2(cannot find the file specified) occured while creating or opening file
 12 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\spridat.mdf'
 13 --(2)访问主文件组的辅助文件出错
 14 --starting up database 'sales'
 15 --error :17207,severity:16 ,state:1
 16 --FileMgr::StartPrimaryDataFiles operating system error2(cannot find the file specified) occured while creating or opening file
 17 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\spridat.ndf'
 18 
 19 --不管是上面哪一个情况,数据库都无法正常开启。只有将文件上的问题解决,数据库才能打开,如果问题不能解决,只能恢复数据库备份
 20 
 21 --二、辅助文件组:
 22 --starting up database 'sales'
 23 --error :17207,severity:16 ,state:1
 24 -- FileMgr::StartSecondaryDataFiles:operating system error2(cannot find the file specified) occured while creating or opening file
 25 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\spridat2.ndf'
 26 
 27 --和主文件组不同,一个数据库可以在辅助文件组OFFLINE的时候打开。我们可以标记辅助文件组OFFLINE,重新打开数据库
 28 ALTER DATABASE sales MODIFY FILE(NAME=spridat2,OFFLINE)
 29 ALTER DATABASE sales SET ONLINE
 30 
 31 --这样打开的数据库能够看到主文件组和其他辅助文件组里的内容,但是offline的那个文件组内容不可见。
 32 --这是你可以将数据导出到一个新数据库,或者用文件组备份恢复的方式恢复那个出问题的文件组
 33 
 34 --三、日志文件
 35 --简单模式:当日志文件不可访问的时候,如果数据库的恢复模式为简单恢复模式,上次数据库关闭正常(所有该提交的事务都已经写入硬盘
 36 --,所有该回滚的事务都已经撤销),那么在下次SQL启动这个数据库的时候,如果发现日志文件不存在,则会自动创建一个新的
 37 --日志文件。这也是为什麽有时候日志文件被删除后,数据库照样能启动的原因。
 38 
 39 --完整模式:如果上次数据库没有正常关闭,或者恢复模式是完整,SQL就不会给这个数据库创建日志文件。
 40 
 41 --错误日志文件显示:
 42 --starting up database 'sales'
 43 --error :17207,severity:16 ,state:1
 44 -- FileMgr::StartLogFiles:operating system error2(cannot find the file specified) occured while creating or opening file
 45 --'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\salelog.ldf'
 46 --the log cannot be rebuilt because the database was not cleanly shut down
 47 
 48 --如果我们不能修复日志上的错误,那么最好的方法是恢复数据库备份。
 49 --因为这时候数据文件里的数据正处在一个不一致的状态。可能有些事务已经提交了,但是数据在日志文件里
 50 --也有一些事务已经撤销了,但数据文件里的数据还是撤销前的状态。如果这些信息找不回来,就会发生事务没有
 51 --完全提交或回滚的现象。
 52 
 53 --解决日志文件错误办法:
 54 --checkdb:后果是有数据丢失
 55 --(1)SQL2000:DBCC REBUILD_LOG 跟SQL2005效果差不多
 56 --(2)SQL2005:如下
 57 ALTER DATABASE sales SET EMERGENCY
 58 GO
 59 ALTER DATABASE sales SET SINGLE_USER
 60 GO
 61 DBCC CHECKDB(sales,REPAIR_ALLOW_DATA_LOSS)
 62 GO
 63 ALTER DATABASE sales SET MULTI_USER
 64 GO
 65 
 66 
 67 
 68 
 69 --2、恢复失败
 70 --数据库进入恢复后,要做重做(roll forward) 和撤销(roll back)的工作中间可能会遇到3类问题
 71 --(1)在重做过程中遇到的能延迟的错误(deferrable error)
 72 --(2)在撤销的过程中遇到能延迟的错误
 73 --(3)在任何时间遇到的不能延迟的错误
 74 
 75 --(1)重做遇到的错误
 76 --举到的例子:由于1:153上的数据没有被正常重做,所以这个页面是不能正常访问的。SQL会标记这个页面为RestorePending
 77 --任何操作想要访问这个页面,SQL都会抛出下面错误
 78 --Database ID 5 ,PAGE(1:153)is marked RestorePending
 79 --如果想要修复上面的错误,必须运行dbcc checkdb 或者使用数据库备份进行恢复
 80 
 81 
 82 --(2)撤销过程中的失败
 83 --举到的例子:从这些信息可以看到,SQL在页面1:153上面发生访问错误,以至于日志(51:104:5)所代表
 84 --的那个事务不能正常回滚。SQL标记这个事务(不光是这个页面)为延迟事务,然后继续其他撤销工作。
 85 --最后数据库正常上线。但是由于有事务没有被正常回滚,所以在和这个事务有关的那些对象上,SQL
 86 --申请了一些锁,以防止问题解决之前有其他用户访问这些对象。锁的主人编号是-3。
 87 --你可以运行下面代码来检查SQL有没有这样的锁
 88 SELECT
 89 CONVERT(CHAR(22),resource_description)AS resource_desc,
 90 CONVERT(CHAR(15),resource_type) AS resource_type,
 91 request_session_id,
 92 CONVERT(CHAR(15),request_mode) AS request_mode,
 93 CONVERT(CHAR(15),request_status) AS request_status
 94 from sys.dm_tran_locks
 95 WHERE request_session_id=-3
 96 
 97 --和RestorePending的页面一样,要修复页面,最好的方式是备份恢复。因为这样能保证数据库里
 98 --的数据都是事务一致的,而且也对数据的丢失量心中有数。
 99 --如果没有备份,只能DBCC CHECKDB或者DBCC CHECKTABLE ,修复物理损坏,但是逻辑错误就无能为力了
100 --因为管理员也不知道到底哪些数据出了逻辑问题
101 ALTER DATABASE sales SET EMERGENCY
102 GO
103 ALTER DATABASE sales SET SINGLE_USER
104 GO
105 DBCC CHECKDB(sales,REPAIR_ALLOW_DATA_LOSS)
106 GO
107 ALTER DATABASE sales SET MULTI_USER
108 GO
109 -------------------------------------------
110 --DBCC CHECKTABLE()
111 dbcc checktable('需要修复的数据表的名称',REPAIR_ALLOW_DATA_LOSS)
112 dbcc checktable('需要修复的数据表的名称',REPAIR_REBUILD)
113 
114 
115 --(3)可疑模式
116 --当数据库做恢复的时候遇到的错误很严重,无法延迟错误时,整个数据库会进入可疑模式。
117 --这时整个数据库都无法访问。发生这种情况,
118 --1要不就是数据库损坏范围太大,
119 --2要不就是一些比较重要的数据库分配页出现了问题。
120 --3如果因为是硬件的问题,可以在硬件修复后,重启SQL,SQL会对每个数据库都做恢复,哪怕这个数据库上次被标记为可疑。
121 
122 
123 --可疑模式解决办法:
124 --(1)恢复备份
125 --(2)先移走数据库日志文件,重启SQL,数据库会进入RECOVERY_PENDING状态,然后
126 ALTER DATABASE sales SET EMERGENCY
127 GO
128 ALTER DATABASE sales SET SINGLE_USER
129 GO
130 DBCC CHECKDB(sales,REPAIR_ALLOW_DATA_LOSS)
131 GO
132 ALTER DATABASE sales SET MULTI_USER
133 GO
134 --SQL会创建一个新的日志文件。由于这个日志文件是空的,数据库无须做任何重做和撤销的工作,
135 --也就不容易进入可疑模式。数据库可以被打开,但是很多有问题的对象还是不能访问,只能将它们
136 --导入到新建的数据库里面,能挽救多少是多少。
137 --(3)只有符号要求的备份才能挽救数据
138 
139 
140 
141 
142 --------------------------------------------------------------------------------
143 --案例
144 -----------------------------------------------------------------------------------
145 --数据库恢复模式为 完整 ,先前做过一次完整备份。原先的表格里所有的值都是AAAA...
146 --上次SQL关闭的时候,有个将100条记录修改成BBBB....的事务没来得及提交。
147 UPDATE test SET col2=REPLICATE('B',300) WHERE col1<100
148 
149 --现在它启动的时候,由于数据文件发生损坏,在撤销时遇到1:200页面上的824错误,
150 --相关事务被标记为延迟的事务
151 --数据库能正常打开,运行下面的查询会有许多被-3持有的锁,不但在key上,还在很多page上
152 
153 --查询SQL现有的锁
154 USE pagenumber
155 GO
156 SELECT
157 p.object_id,
158 OBJECT_NAME(p.object_id) AS OBJECT_NAME,
159 request_session_id,
160 resource_type,
161 resource_description,
162 request_mode,
163 resource_associated_entity_id,
164 request_status
165 FROM sys.dm_tran_locks AS lock LEFT JOIN sys.partitions p
166 ON lock.resource_associated_entity_id=p.hobt_id
167 WHERE resource_database_id=DB_ID('pagenumber')
168 ORDER BY request_session_id,request_mode,resource_type,resource_associated_entity_id
169 GO
170 
171 --如果运行下面的查询,就会被阻塞住
172 SELECT * FROM pagenumber..test
173 
174 --查询sys.sysprocesses视图,就能够看到连接被-3阻塞住了
175 SELECT * FROM sys.sysprocesses WHERE spid>50
176 
177 --两个方式修复数据库
178 --(1)DBCC CHECKDB
179 ALTER DATABASE pagenumber SET EMERGENCY
180 GO
181 ALTER DATABASE pagenumber SET SINGLE_USER
182 GO
183 DBCC CHECKDB(pagenumber,REPAIR_ALLOW_DATA_LOSS)
184 GO
185 ALTER DATABASE pagenumber SET MULTI_USER
186 GO
187 
188 --结果:错误的结果 只撤销了部分记录修改
189 
190 --(2)由于数据库是完整恢复模式,先前又做过完整备份,所以可以用页面恢复的方式,很快恢复数据库
191 --到一个一致的状态
192 --首先需要找到所有有问题的页面,查看errorlog,或者数据库不大的话可以在紧急模式运行不带参数的
193 --checkdb,把所有错误页面打出来
194 
195 ALTER DATABASE pagenumber SET EMERGENCY
196 GO
197 DBCC CHECKDB('pagenumber')
198 GO
199 ALTER DATABASE pagenumber SET ONLINE
200 GO
201 --现在我们知道是1:200这个页面有问题,就只需恢复这个页面就可以了
202 USE master
203 GO
204 RESTORE DATABASE pagenumber PAGE='1:200'
205 FROM DISK='pagenumber.bak'
206 
207 
208 --这时候查询还是不能正常运行,因为那个事务还没有回滚。现在我们做一次尾日志备份
209 BACKUP LOG pagenumber TO DISK='pagenumbergood.trn' WITH init,FORMAT
210 GO
211 
212 --然后我们把日志备份依次恢复。这里假设只有刚才做的一份
213 RESTORE LOG pagenumber FROM DISK='pagenumbergood.trn' WITH recovery
214 GO
215 --现在再做查询可以发现数据是一致的
216 
217 
218 --用checkdb命令恢复处理的数据虽然在物理上恢复了正常,但是一些可能已经是错误的了。
219 --所以管理员要少用checkdb+repair_allow_data_loss,尽可能使用备份恢复的方法

 

posted @ 2013-07-27 15:37 桦仔 阅读(...) 评论(...)  编辑 收藏