数据库913错误 菠萝兄设置数据库只读
数据库913错误 菠萝兄设置数据库只读
传说】北京-肖磊
日
Could not find database ID 7. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur, contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred.
【传说】北京-肖磊
2014/7/2 10:06:53
宋桑碰到过么？
【传说】北京-肖磊
2014/7/2 10:06:57
913错误
【传说】北京-肖磊
2014/7/2 10:07:10
消息 913，级别 16，状态 4，第 2 行
找不到数据库 ID 7。可能该数据库尚未激活，也可能正处于转换过程中。一旦数据库可用，请重新发出查询。如果您认为此错误不是由于数据库正在转换其状态而导致的，并且此错误仍然存在，请与主要支持提供商联系。请允许复查 Microsoft SQL Server 错误日志以及与发生错误时的环境相关的任何其他信息。
【传说】北京-肖磊
2014/7/2 10:07:48
数据库set read_only的时候被阻塞了，我取消后就变成这个样子了
【话唠】杭州-范舟琦
2014/7/2 10:08:47
长知识的时候来了，让俺们看看 数据库现在啥状态
【传说】北京-肖磊(79428744) 10:09:19
SELECT collation_name FROM sys.databases
这个是空
【传说】北京-肖磊(79428744) 10:09:27
刷不出状态了
【话唠】杭州-范舟琦(32116057) 10:10:12
也就是说数据库 不再 sys.databases 了？ 还是只是没状态了
【传说】北京-宋沄剑(5817141) 10:10:33
boot page损坏了.....
【话唠】杭州-范舟琦(32116057) 10:10:43
双击查看原图
【活跃】上海-刘志峰(252982469) 10:10:43
数据库现在有状态吗
【话唠】杭州-范舟琦(32116057) 10:11:10
boot page 顺坏，第一次碰到
【传说】北京-肖磊(79428744) 10:11:28
擦，非常诡异的好了
【传说】北京-宋沄剑(5817141) 10:11:37
我擦?
【传说】北京-宋沄剑(5817141) 10:11:42
不可能吧?
【话唠】杭州-范舟琦(32116057) 10:12:03
双击查看原图
【传说】北京-肖磊(79428744) 10:12:07
重新执行以下alter database dbname set read_only with rollback immediately
【传说】北京-肖磊(79428744) 10:12:23
太TM诡异了
【传说】北京-宋沄剑(5817141) 10:12:28
913错误能自己好?
【传说】北京-肖磊(79428744) 10:12:31
我这小心脏受不鸟啊
【传说】北京-肖磊(79428744) 10:12:54
不是自己好的，我又重新执行了一下alter database dbname set read_only with rollback immediately
然后状态就刷新过来了
【传说】北京-宋沄剑(5817141) 10:13:20
双击查看原图
【传说】北京-宋沄剑(5817141) 10:14:05
http://www.sqlskills.com/blogs/paul/search-engine-qa-20-boot-pages-and-boot-page-corruption/
【传说】北京-肖磊(79428744) 10:14:14
本来今天要迁移的，本来业务说这个库现在没有新数据写入了。我说保险点，改成只读再做备份，然后就这个鬼样子了
【传说】北京-肖磊(79428744) 10:14:24
吓屎偶了
【传说】北京-宋沄剑(5817141) 10:14:46
为什么会报913错误呢...
【话唠】杭州-范舟琦(32116057) 10:15:38
可能在修改root page
【传说】北京-肖磊(79428744) 10:16:13
开始我就是alter database dbname set read_only with rollback immediately，但5分钟没有完成。看了一下发现有些进程被这个阻塞了，然后我就取消，也提示100%回滚了
【传说】北京-肖磊(79428744) 10:16:18
接着就是913错误
http://www.sqlskills.com/blogs/paul/search-engine-qa-20-boot-pages-and-boot-page-corruption/
ow that I've done all the business-related blog posts, back to the good stuff to stop people complaining!
Something that's cropped up a few times over the summer so far is people trying to repair boot page corruptions.
First off, what's a boot page? Every database has a single page that stores critical information about the database itself. It's always page 9 in file 1 (the first file in the PRIMARY filegroup). You can examine the page using DBCC PAGE and it will interpret all the fields for you, but there's another command, DBCC DBINFO, that also dumps all this info (in fact the DBCC PAGE code calls the same underlying dumping code). This command is undocumented and unsupported but widely known and 'documented' in lots of places on the web – given that it uses the same code as DBCC PAGE, it's just as safe to use IMHO.
Note that the output of DBCC DBINFO and DBCC PAGE of the boot page may change from release to release!
So what's on the boot page?
DBCC DBINFO ('BootPageTest');
GO
DBINFO STRUCTURE:
DBINFO @0x5BF6EF84dbi_dbid = 19 dbi_status = 65536 dbi_nextid = 2073058421
dbi_dbname = BootPageTest dbi_maxDbTimestamp = 2000 dbi_version = 611
dbi_createVersion = 611 dbi_ESVersion = 0
dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_crdate = 2008-07-10 15:53:18.843
dbi_filegeneration = 0
dbi_checkptLSNm_fSeqNo = 41 m_blockOffset = 29 m_slotId = 55
dbi_RebuildLogs = 0 dbi_dbccFlags = 2
dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000
dbi_dbbackupLSNm_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_oldestBackupXactLSN
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_LastLogBackupTime = 1900-01-01 00:00:00.000
dbi_differentialBaseLSNm_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_createIndexLSN
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_versionChangeLSN
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_familyGUID = a4e88c13-b4cf-4320-834e-92b237244d4b
dbi_recoveryForkNameStack
entry 0m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
m_guid = a4e88c13-b4cf-4320-834e-92b237244d4bentry 1
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
m_guid = 00000000-0000-0000-0000-000000000000
dbi_differentialBaseGuid = 00000000-0000-0000-0000-000000000000 dbi_firstSysIndexes = 0001:00000014
dbi_collation = 872468488 dbi_category = 0 dbi_maxLogSpaceUsed = 231936
dbi_localState = 0 dbi_roleSequence = 0
dbi_failoverLsnm_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_dbmRedoLsn
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_dbmOldestXactLsn
m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_dbMirrorId = 00000000-0000-0000-0000-000000000000
dbi_pageUndoLsnm_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_disabledSequence = 0
dbi_dvSplitPointm_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0
dbi_CloneCpuCount = 0 dbi_CloneMemorySize = 0
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
There's all kinds on interesting things in there, for instance:
-
dbi_version and dbi_createversion: the physical version number of the database (and when it was created). See question 1 in the August 2008 SQL Q&A column in TechNet Magazine for an explanation (see here).
-
dbi_RebuildLogs: a count of the number of times the transaction log has been rebuilt for the database. PSS can use this to tell whether corruption problems could have been caused by DBAs rebuilding the log
-
dbi_dbccLastKnownGood: the completion time of the last 'clean' run of DBCC CHECKDB
-
a bunch of different LSNs related to checkpoint, backups, database mirroring
-
dbi_LastLogBackupTime: self-explanatory
-
dbi_differentialBaseGuid: the GUID generated by the last full database backup. Differential backups can only be restored on top of a matching full backup – so an out-of-band full backup could screw-up your disaster recovery – see this blog post for more info.
Now, what about if this page is corrupt in some way? I corrupted the BootPageTest database to have a corrupt boot page. Let's see what happens:
USE BootPagetest;
GOMsg 913, Level 16, State 4, Line 1
Could not find database ID 19. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur, contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred.
Okay – let's try setting the database into EMERGENCY mode:
ALTER DATABASE BootPageTest SET EMERGENCY;
GOMsg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xcdee22fa; actual: 0xcb6ea2fa). It occurred during a read of page (1:9) in database ID 19 at offset 0×00000000012000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\BootPageTest.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Hmm. What about running DBCC CHECKDB?
DBCC CHECKDB ('BootPageTest') WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
Msg 922, Level 14, State 1, Line 1
Database 'BootPageTest' is being recovered. Waiting until recovery is finished.
It's not looking good. Obviously the change to EMERGENCY mode couldn't complete properly. What's the database status?
SELECT [state_desc] FROM sys.databases WHERE [name] = 'BootPageTest';
GOstate_desc
————————————————————
RECOVERY_PENDING
The boot page is inaccessible so in effect the database is inaccessible too, this is what the database state means in this case.
So what does this mean? If the boot page is corrupt, you can't run DBCC CHECKDB so you can't possibly run repair, and you can't put the database into EMERGENCY mode so you can't extract data into a new database. It means that there's NO WAY to recover from a corrupt boot page EXCEPT to restore from backups. One more reason to have backups…
