快捷搜索:
来自 网络数据库 2019-06-20 08:33 的文章
当前位置: 67677新澳门手机版 > 网络数据库 > 正文

系统数据库,master_files不可能精确展现数据库脱

 

master数据库

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

数据库记录 SQL Server 系统的所有系统级信息。这包括实例范围的元数据(例如登录帐户)、端点、链接服务器和系统配置设置。此外,master 数据库还记录了所有其他数据库的存在、数据库文件的位置以及 SQL Server 的初始化信息。因此,如果 master 数据库不可用,则 SQL Server 无法启动。在 SQL Server 中,系统对象不再存储在 master 数据库中,而是存储在 Resource 数据库.aspx)中。

 

 

 

不能在 master 数据库中执行下列操作:

 

USE master;

GO

ALTER DATABASE TEST SET OFFLINE WITH ROLLBACK IMMEDIATE;

GO

 

 

SELECT  name ,

        physical_name ,

        state ,

        state_desc

FROM    sys.master_files

WHERE   database_id = DB_ID('test');

 

 

SELECT  name ,

        state ,

        state_desc

FROM    sys.databases

WHERE   name = 'test';
  • 添加文件或文件组。

  • 更改排序规则。默认排序规则为服务器排序规则。

  • 更改数据库所有者。masterdbo 所有。

  • 创建全文目录或全文索引。

  • 在数据库的系统表上创建触发器。

  • 删除数据库。

  • 从数据库中删除 guest 用户。

  • 启用变更数据捕获。

  • 参与数据库镜像。

  • 删除主文件组、主数据文件或日志文件。

  • 重命名数据库或主文件组。

  • 将数据库设置为 OFFLINE。

  • 将数据库或主文件组设置为 READ_ONLY。

 

model 数据库

 

model 数据库用作在 SQL Server 实例上创建的所有数据库的模板。因为每次启动 SQL Server 时都会创建 tempdb,所以 model 数据库必须始终存在于 SQL Server 系统中。

图片 1

 

 

不能在 model 数据库中执行下列操作:

 

  • 添加文件或文件组。

  • 更改排序规则。默认排序规则为服务器排序规则。

  • 更改数据库所有者。modeldbo 所有。

  • 删除数据库。

  • 从数据库中删除 guest 用户。

  • 启用变更数据捕获。

  • 参与数据库镜像。

  • 删除主文件组、主数据文件或日志文件。

  • 重命名数据库或主文件组。

  • 将数据库设置为 OFFLINE。

  • 将数据库或主文件组设置为 READ_ONLY。

  • 使用 WITH ENCRYPTION 选项创建过程、视图或触发器。加密密钥与在其中创建对象的数据库绑定在一起。在 model 数据库中创建的加密对象只能用于 model 中。

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

msdb 数据库

 

msdb 数据库由 SQL Server 代理用于计划警报和作业,也可以由其他功能(如 Service Broker 和数据库邮件)使用。

 

不能在 msdb 数据库中执行下列操作:

 

  • 更改排序规则。默认排序规则为服务器排序规则。

  • 删除数据库。

  • 从数据库中删除 guest 用户。

  • 启用变更数据捕获。

  • 参与数据库镜像。

  • 删除主文件组、主数据文件或日志文件。

  • 重命名数据库或主文件组。

  • 将数据库设置为 OFFLINE。

  • 将主文件组设置为 READ_ONLY。

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

GO

 CREATE VIEW sys.master_files AS

    SELECT

        database_id         = f.dbid,

        file_id             = f.fileid,

        file_guid           = f.fileguid,

        type                = f.filetype,

        type_desc           = ft.name,

        data_space_id       = f.grpid,

        name                = f.lname,

        physical_name       = f.pname,

        state               = convert(tinyint, case f.filestate        -- Map enum EMDFileState to AvailablityStates

                                when 0 then 0 when 10 then 0    -- ONLINE

                                when 4 then 7    -- DEFUNCT

                                when 5 then 3 when 9 then 3    -- RECOVERY_PENDING

                                when 7 then 1 when 8 then 1 when 11 then 1    -- RESTORING

                                when 12 then 4    -- SUSPECT

                                else 6 end),    -- OFFLINE

        state_desc          = st.name,

        f.size,

        max_size            = f.maxsize,

        f.growth,

        is_media_read_only  = sysconv(bit, f.status & 8),        -- FIL_READONLY_MEDIA

        is_read_only        = sysconv(bit, f.status & 16),    -- FIL_READONLY

        is_sparse           = sysconv(bit, f.status & 256),    -- FIL_SPARSE_FILE

        is_percent_growth   = sysconv(bit, f.status & 32),    -- FIL_PERCENT_GROWTH

        is_name_reserved    = sysconv(bit, case f.filestate when 3 then 1 else 0 end), -- x_efs_DroppedReusePending

        create_lsn          = GetNumericLsn(f.createlsn),

        drop_lsn            = GetNumericLsn(f.droplsn),

        read_only_lsn       = GetNumericLsn(f.readonlylsn),

        read_write_lsn      = GetNumericLsn(f.readwritelsn),

        differential_base_lsn     = GetNumericLsn(f.diffbaselsn),

        differential_base_guid    = f.diffbaseguid,

        differential_base_time    = nullif(f.diffbasetime, 0),

        redo_start_lsn            = GetNumericLsn(f.redostartlsn),

        redo_start_fork_guid      = f.redostartforkguid,

        redo_target_lsn           = GetNumericLsn(f.redotargetlsn),

        redo_target_fork_guid     = f.forkguid,

        backup_lsn                = GetNumericLsn(f.backuplsn),

        credential_id             = cr.credential_id

    FROM sys.sysbrickfiles f

    LEFT JOIN sys.syspalvalues st ON st.class = 'DBFS' AND st.value = f.filestate

    LEFT JOIN sys.syspalvalues ft ON ft.class = 'DBFT' AND ft.value = f.filetype

    LEFT JOIN sys.credentials cr ON f.pname LIKE cr.name   N'%' COLLATE database_default

    WHERE f.dbid < 0x7fff -- consistent with sys.databases

        AND f.pruid = 0

        AND f.filestate NOT IN (1, 2)    -- x_efs_Dummy, x_efs_Dropped

        AND has_access('MF', 1) = 1

 

GO

Resource 数据库

 

Resource 数据库是只读数据库,它包含了 SQL Server 中的所有系统对象。SQL Server 系统对象(例如 sys.objects)在物理上持续存在于 Resource 数据库中,但在逻辑上,它们出现在每个数据库的 sys 架构中。Resource 数据库不包含用户数据或用户元数据。

 

tempdb 数据库

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

tempdb 系统数据库是一个全局资源,可供连接到 SQL Server 实例的所有用户使用,并可用于保存下列各项:

 

  • 显式创建的临时用户对象,例如全局或局部临时表、临时存储过程、表变量或游标。

  • SQL Server 数据库引擎创建的内部对象,例如,用于存储假脱机或排序的中间结果的工作表。

  • 由使用已提交读(使用行版本控制隔离或快照隔离事务)的数据库中数据修改事务生成的行版本。

  • 由数据修改事务为实现联机索引操作、多个活动的结果集 (MARS) 以及 AFTER 触发器等功能而生成的行版本。

 

不能对 tempdb 数据库执行以下操作:

 

  • 添加文件组。

  • 备份或还原数据库。

  • 更改排序规则。默认排序规则为服务器排序规则。

  • 更改数据库所有者。tempdb 的所有者是 dbo

  • 创建数据库快照。

  • 删除数据库。

  • 从数据库中删除 guest 用户。

  • 启用变更数据捕获。

  • 参与数据库镜像。

  • 删除主文件组、主数据文件或日志文件。

  • 重命名数据库或主文件组。

  • 运行 DBCC CHECKALLOC。

  • 运行 DBCC CHECKCATALOG。

  • 将数据库设置为 OFFLINE。

  • 将数据库或主文件组设置为 READ_ONLY。

图片 2

 

 

本文由67677新澳门手机版发布于网络数据库,转载请注明出处:系统数据库,master_files不可能精确展现数据库脱

关键词: