查看: 428|回复: 0

[SQLServer] SQL Server系统视图sys.master_files不能正确显示数据库脱机状态 2018-04-21 00:27 by 潇湘隐者, ... 阅读,

发表于 2018-4-27 08:00:05

最近发现在SQL Server数据库(目前测试过SQL Server 2008, 2012,2014,2016各个版本)中,即使数据库处于脱机(OFFLINE)状态,但是sys.master_files依然显示是联机状态。本文测试环境为Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 。具体测试过程如下所示:

复制代码
  1. USE master;
复制代码
  1. GO
复制代码
  1. ALTER DATABASE TEST SET OFFLINE WITH ROLLBACK IMMEDIATE;
复制代码
  1. GO
复制代码
复制代码
复制代码
  1. SELECT name ,
复制代码
  1. physical_name ,
复制代码
  1. state ,
复制代码
  1. state_desc
复制代码
  1. FROM sys.master_files
复制代码
  1. WHERE database_id = DB_ID('test');
复制代码
复制代码
复制代码
  1. SELECT name ,
复制代码
  1. state ,
复制代码
  1. state_desc
复制代码
  1. FROM sys.databases
复制代码
  1. WHERE name = 'test';
复制代码

clip_image001

如上所示,sys.databases系统视图正确的显示数据库处于脱机状态(OFFLINE),但是系统视图sys.master_files显示的依然是联机(ONLINE),我们可以获取系统视图sys.master_files的定义,如下所示(至于如何获取视图定义,如果你不清楚,可以参考我的博客SQL Server查看视图定义总结),

  1. SET QUOTED_IDENTIFIER ON
复制代码
  1. SET ANSI_NULLS ON
复制代码
  1. GO
复制代码
  1. CREATE VIEW sys.master_files AS
复制代码
  1. SELECT
复制代码
  1. database_id = f.dbid,
复制代码
  1. file_id = f.fileid,
复制代码
  1. file_guid = f.fileguid,
复制代码
  1. type = f.filetype,
复制代码
  1. type_desc = ft.name,
复制代码
  1. data_space_id = f.grpid,
复制代码
  1. name = f.lname,
复制代码
  1. physical_name = f.pname,
复制代码
  1. state = convert(tinyint, case f.filestate -- Map enum EMDFileState to AvailablityStates
复制代码
  1. when 0 then 0 when 10 then 0 -- ONLINE
复制代码
  1. when 4 then 7 -- DEFUNCT
复制代码
  1. when 5 then 3 when 9 then 3 -- RECOVERY_PENDING
复制代码
  1. when 7 then 1 when 8 then 1 when 11 then 1 -- RESTORING
复制代码
  1. when 12 then 4 -- SUSPECT
复制代码
  1. else 6 end), -- OFFLINE
复制代码
  1. state_desc = st.name,
复制代码
  1. f.size,
复制代码
  1. max_size = f.maxsize,
复制代码
  1. f.growth,
复制代码
  1. is_media_read_only = sysconv(bit, f.status & 8), -- FIL_READONLY_MEDIA
复制代码
  1. is_read_only = sysconv(bit, f.status & 16), -- FIL_READONLY
复制代码
  1. is_sparse = sysconv(bit, f.status & 256), -- FIL_SPARSE_FILE
复制代码
  1. is_percent_growth = sysconv(bit, f.status & 32), -- FIL_PERCENT_GROWTH
复制代码
  1. is_name_reserved = sysconv(bit, case f.filestate when 3 then 1 else 0 end), -- x_efs_DroppedReusePending
复制代码
  1. create_lsn = GetNumericLsn(f.createlsn),
复制代码
  1. drop_lsn = GetNumericLsn(f.droplsn),
复制代码
  1. read_only_lsn = GetNumericLsn(f.readonlylsn),
复制代码
  1. read_write_lsn = GetNumericLsn(f.readwritelsn),
复制代码
  1. differential_base_lsn = GetNumericLsn(f.diffbaselsn),
复制代码
  1. differential_base_guid = f.diffbaseguid,
复制代码
  1. differential_base_time = nullif(f.diffbasetime, 0),
复制代码
  1. redo_start_lsn = GetNumericLsn(f.redostartlsn),
复制代码
  1. redo_start_fork_guid = f.redostartforkguid,
复制代码
  1. redo_target_lsn = GetNumericLsn(f.redotargetlsn),
复制代码
  1. redo_target_fork_guid = f.forkguid,
复制代码
  1. backup_lsn = GetNumericLsn(f.backuplsn),
复制代码
  1. credential_id = cr.credential_id
复制代码
  1. FROM sys.sysbrickfiles f
复制代码
  1. LEFT JOIN sys.syspalvalues st ON st.class = 'DBFS' AND st.value = f.filestate
复制代码
  1. LEFT JOIN sys.syspalvalues ft ON ft.class = 'DBFT' AND ft.value = f.filetype
复制代码
  1. LEFT JOIN sys.credentials cr ON f.pname LIKE cr.name + N'%' COLLATE database_default
复制代码
  1. WHERE f.dbid < 0x7fff -- consistent with sys.databases
复制代码
  1. AND f.pruid = 0
复制代码
  1. AND f.filestate NOT IN (1, 2) -- x_efs_Dummy, x_efs_Dropped
复制代码
  1. AND has_access('MF', 1) = 1
复制代码
复制代码
  1. GO
复制代码

可以看出sys.master_files的state值来自于系统基表sys.sysbrickfiles的filestate字段,我们从DAC模式去查看,发现TEST数据库(dbid=21)的filestat为0,这个值应该为6才对,另外,还有一个让人意外的是,这个系统表里面关于TEST数据库有两个事务日志文件记录,实际上只有一个(其实这个是前阵子写这篇博客MS SQL 事务日志管理小结时,测试添加、删除数据事务日志文件遗留下来的记录,不清楚是Bug还是什么问题导致在系统基表还存在这样的一条记录

clip_image002

那么我们接下来看看sys.sysbrickfiles的具体定义,如下所示:

  1. SET QUOTED_IDENTIFIER ON
复制代码
  1. SET ANSI_NULLS ON
复制代码
  1. GO
复制代码
  1. CREATE VIEW sys.databases AS
复制代码
  1. SELECT d.name, d.id AS database_id,
复制代码
  1. r.indepid AS source_database_id,
复制代码
  1. d.sid AS owner_sid,
复制代码
  1. d.crdate AS create_date,
复制代码
  1. d.cmptlevel AS compatibility_level,
复制代码
  1. -- coll.value = null means that a collation wasn't specified for the DB and the server default is used instead
复制代码
  1. convert(sysname, case when serverproperty('EngineEdition') = 5 AND d.id = 1 then serverproperty('collation')
复制代码
  1. else CollationPropertyFromID(convert(int, isnull(coll.value, p.cid)), 'name') end) AS collation_name,
复制代码
  1. p.user_access, ua.name AS user_access_desc,
复制代码
  1. sysconv(bit, d.status & 0x400) AS is_read_only, -- DBR_RDONLY
复制代码
  1. sysconv(bit, d.status & 1) AS is_auto_close_on, -- DBR_CLOSE_ON_EXIT
复制代码
  1. sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on, -- DBR_AUTOSHRINK
复制代码
  1. case when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000020) = 1) then cast (1 as tinyint) -- RESTORING
复制代码
  1. when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000080) = 1) then cast (7 as tinyint) -- COPYING
复制代码
  1. when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000100) = 1) then cast (4 as tinyint) -- SUSPECT
复制代码
  1. else p.state
复制代码
  1. end AS state, -- 7 is COPYING and 4 is SUSPECT state for database copy (UNDO: Need to have a clean way to set states in dbtable for a user db)
复制代码
  1. case when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000020) = 1) then 'RESTORING'
复制代码
  1. when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000080) = 1) then 'COPYING'
复制代码
  1. when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000100) = 1) then 'SUSPECT'
复制代码
  1. else st.name
复制代码
  1. end AS state_desc,
复制代码
  1. sysconv(bit, d.status & 0x200000) AS is_in_standby, -- DBR_STANDBY
复制代码
  1. case when serverproperty('EngineEdition') = 5 then convert(bit, 0) else p.is_cleanly_shutdown end AS is_cleanly_shutdown,
复制代码
  1. sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled, -- DBR_SUPPLEMENT_LOG
复制代码
  1. p.snapshot_isolation_state, si.name AS snapshot_isolation_state_desc,
复制代码
  1. sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on, -- DBR_READCOMMITTED_SNAPSHOT
复制代码
  1. p.recovery_model, ro.name AS recovery_model_desc,
复制代码
  1. p.page_verify_option, pv.name AS page_verify_option_desc,
复制代码
  1. sysconv(bit, d.status2 & 0x1000000) AS is_auto_create_stats_on, -- DBR_AUTOCRTSTATS
复制代码
  1. sysconv(bit, d.status2 & 0x00400000) AS is_auto_create_stats_incremental_on, -- DBR_AUTOCRTSTATSINC
复制代码
  1. sysconv(bit, d.status2 & 0x40000000) AS is_auto_update_stats_on, -- DBR_AUTOUPDSTATS
复制代码
  1. sysconv(bit, d.status2 & 0x80000000) AS is_auto_update_stats_async_on, -- DBR_AUTOUPDSTATSASYNC
复制代码
  1. sysconv(bit, d.status2 & 0x4000) AS is_ansi_null_default_on, -- DBR_ANSINULLDFLT
复制代码
  1. sysconv(bit, d.status2 & 0x4000000) AS is_ansi_nulls_on, -- DBR_ANSINULLS
复制代码
  1. sysconv(bit, d.status2 & 0x2000) AS is_ansi_padding_on, -- DBR_ANSIPADDING
复制代码
  1. sysconv(bit, d.status2 & 0x10000000) AS is_ansi_warnings_on, -- DBR_ANSIWARNINGS
复制代码
  1. sysconv(bit, d.status2 & 0x1000) AS is_arithabort_on, -- DBR_ARITHABORT
复制代码
  1. sysconv(bit, d.status2 & 0x10000) AS is_concat_null_yields_null_on, -- DBR_CATNULL
复制代码
  1. sysconv(bit, d.status2 & 0x800) AS is_numeric_roundabort_on, -- DBR_NUMEABORT
复制代码
  1. sysconv(bit, d.status2 & 0x800000) AS is_quoted_identifier_on, -- DBR_QUOTEDIDENT
复制代码
  1. sysconv(bit, d.status2 & 0x20000) AS is_recursive_triggers_on, -- DBR_RECURTRIG
复制代码
  1. sysconv(bit, d.status2 & 0x2000000) AS is_cursor_close_on_commit_on, -- DBR_CURSCLOSEONCOM
复制代码
  1. sysconv(bit, d.status2 & 0x100000) AS is_local_cursor_default, -- DBR_DEFLOCALCURS
复制代码
  1. sysconv(bit, d.status2 & 0x20000000) AS is_fulltext_enabled, -- DBR_FTENABLED
复制代码
  1. sysconv(bit, d.status2 & 0x200) AS is_trustworthy_on, -- DBR_TRUSTWORTHY
复制代码
  1. sysconv(bit, d.status2 & 0x400) AS is_db_chaining_on, -- DBR_DBCHAINING
复制代码
  1. sysconv(bit, d.status2 & 0x08000000) AS is_parameterization_forced, -- DBR_UNIVERSALAUTOPARAM
复制代码
  1. sysconv(bit, d.status2 & 64) AS is_master_key_encrypted_by_server, -- DBR_MASTKEY
复制代码
  1. sysconv(bit, d.status2 & 0x00000010) AS is_query_store_on, -- DBR_QDSENABLED
复制代码
  1. sysconv(bit, d.category & 1) AS is_published,
复制代码
  1. sysconv(bit, d.category & 2) AS is_subscribed,
复制代码
  1. sysconv(bit, d.category & 4) AS is_merge_published,
复制代码
  1. sysconv(bit, d.category & 16) AS is_distributor,
复制代码
  1. sysconv(bit, d.category & 32) AS is_sync_with_backup,
复制代码
  1. d.svcbrkrguid AS service_broker_guid,
复制代码
  1. sysconv(bit, case when d.scope = 0 then 1 else 0 end) AS is_broker_enabled,
复制代码
  1. p.log_reuse_wait, lr.name AS log_reuse_wait_desc,
复制代码
  1. sysconv(bit, d.status2 & 4) AS is_date_correlation_on, -- DBR_DATECORRELATIONOPT
复制代码
  1. sysconv(bit, d.category & 64) AS is_cdc_enabled,
复制代码
  1. sysconv(bit, d.status2 & 0x100) AS is_encrypted, -- DBR_ENCRYPTION
复制代码
  1. convert(bit, d.status2 & 0x8) AS is_honor_broker_priority_on, -- DBR_HONORBRKPRI
复制代码
  1. sgr.guid AS replica_id,
复制代码
  1. sgr2.guid AS group_database_id,
复制代码
  1. ssr.indepid AS resource_pool_id,
复制代码
  1. default_language_lcid = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(smallint, p.default_language) else null end,
复制代码
  1. default_language_name = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(sysname, sld.name) else null end,
复制代码
  1. default_fulltext_language_lcid = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(int, p.default_fulltext_language) else null end,
复制代码
  1. default_fulltext_language_name = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(sysname, slft.name) else null end,
复制代码
  1. is_nested_triggers_on = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(bit, p.allow_nested_triggers) else null end,
复制代码
  1. is_transform_noise_words_on = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(bit, p.transform_noise_words) else null end,
复制代码
  1. two_digit_year_cutoff = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(smallint, p.two_digit_year_cutoff) else null end,
复制代码
  1. containment = sysconv(tinyint, (d.status2 & 0x80000)/0x80000), -- DBR_IS_CDB
复制代码
  1. containment_desc = convert(nvarchar(60), cdb.name),
复制代码
  1. p.recovery_seconds AS target_recovery_time_in_seconds,
复制代码
  1. p.delayed_durability,
复制代码
  1. case when (p.delayed_durability = 0) then CAST('DISABLED' AS nvarchar(60)) -- LCOPT_DISABLED
复制代码
  1. when (p.delayed_durability = 1) then CAST('ALLOWED' AS nvarchar(60)) -- LCOPT_ALLOWED
复制代码
  1. when (p.delayed_durability = 2) then CAST('FORCED' AS nvarchar(60)) -- LCOPT_FORCED
复制代码
  1. else NULL
复制代码
  1. end AS delayed_durability_desc,
复制代码
  1. convert(bit, d.status2 & 0x80) AS is_memory_optimized_elevate_to_snapshot_on -- DBR_HKELEVATETOSNAPSHOT
复制代码
  1. FROM sys.sysdbreg d OUTER APPLY OpenRowset(TABLE DBPROP, (case when serverproperty('EngineEdition') = 5 then DB_ID() else d.id end)) p
复制代码
  1. LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB
复制代码
  1. LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state
复制代码
  1. LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access
复制代码
  1. LEFT JOIN sys.syspalvalues si ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state
复制代码
  1. LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model
复制代码
  1. LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option
复制代码
  1. LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait
复制代码
  1. LEFT JOIN sys.syssingleobjrefs agdb ON agdb.depid = d.id AND agdb.class = 104 AND agdb.depsubid = 0 -- SRC_AVAILABILITYGROUP
复制代码
  1. LEFT JOIN master.sys.syssingleobjrefs ssr ON ssr.class = 108 AND ssr.depid = d.id -- SRC_RG_DB_TO_POOL
复制代码
  1. LEFT JOIN master.sys.sysclsobjs ag ON ag.id = agdb.indepid AND ag.class = 67 -- SOC_AVAILABILITY_GROUP
复制代码
  1. LEFT JOIN master.sys.sysguidrefs sgr ON sgr.class = 8 AND sgr.id = ag.id AND sgr.subid = 1 -- GRC_AGGUID / AGGUID_REPLICA_ID
复制代码
  1. LEFT JOIN master.sys.sysguidrefs sgr2 ON sgr2.class = 9 AND sgr2.id = ag.id AND sgr2.subid = d.id -- GRC_AGDBGUID
复制代码
  1. LEFT JOIN sys.syspalvalues cdb ON cdb.class = 'DCDB' AND cdb.value = CASE WHEN (d.status2 & 0x80000)=0x80000 THEN 1 ELSE 0 END
复制代码
  1. LEFT JOIN sys.syslanguages sld ON sld.lcid = p.default_language
复制代码
  1. LEFT JOIN sys.fulltext_languages slft ON slft.lcid = p.default_fulltext_language
复制代码
  1. LEFT JOIN sys.sysobjvalues coll ON coll.valclass = 102 AND coll.subobjid = 0 AND coll.objid = d.id -- SVC_DATACOLLATION
复制代码
  1. WHERE d.id < 0x7fff
复制代码
  1. AND has_access('DB', (case when serverproperty('EngineEdition') = 5 then DB_ID() else d.id end)) = 1
复制代码
复制代码
  1. GO
复制代码

EngineEdition

服务器上安装的 数据库引擎 实例的 SQL Server版本。

1 = Personal 或 Desktop Engine(不适用于 SQL Server 2005?和更高版本。)

2 = Standard(对 Standard、Web 和 Business Intelligence 返回该值。)

3 = Enterprise(对 Enterprise、Developer 以及两个 Enterprise 版本返回该值。)

4 = Express(对 Express、Express with Tools 和 Express with Advanced Services 返回该值)

5 = SQL Database

6 = SQL 数据仓库

8 = 托管实例

基本数据类型:int

因为当前数据库版本为标准版,所以stated的值来自OpenRowset(TABLE DBPROP, (case when serverproperty('EngineEdition') = 5 then DB_ID() else d.id end)) p

我们可以在单用户专用连接服务器模式下查看相关记录的值。如下截图所示:

clip_image003

clip_image004

其实发现这个问题(其实我更愿意称其为一个bug)是一次查询时的意外发现。实验测试让我有点吃惊。居然这么多版本都是这种情况! 这到底是一个bug还是数据库什么内部机制呢?



回复

使用道具 举报