主页 / dba / 问题 / 258048
Accepted
Definite
Asked: 2020-01-24 01:18:56 +0800 CST

在 SSDT 中创建内存优化文件组时出错

  • 772

我正在尝试将内存优化文件组添加到 SSDT 中的 SQL 服务器数据库项目。这适用于使用 Visual Studio 2017 的 SQL Server 2017。

但是编译项目（按 F5 构建）会导致错误。部署时不会发生此错误（通过部署）。

文件组正常创建（使用 SSDT 编写的 SQLCMD 变量）：

ALTER DATABASE [$(DatabaseName)]
ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA

但是，这会导致错误：

具有 MEMORY_OPTIMIZED_DATA 文件组的数据库不支持操作“AUTO_CLOSE”。

但是，数据库设置显示确实禁用了自动关闭：

自动关闭

SSDT 出于某种原因生成的部署脚本正在创建数据库，创建文件组，然后仅将 AUTO_CLOSE 设置为关闭。这可能会导致错误：

CREATE DATABASE [$(DatabaseName)]
ON 
PRIMARY(NAME = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS
GO
PRINT N'Creating [MemoryOptimizedFilegroup]...';

GO
ALTER DATABASE [$(DatabaseName)]
    ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA;

GO
ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [MemoryOptimizedFilegroup_69323650], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_MemoryOptimizedFilegroup_69323650.mdf') TO FILEGROUP [MemoryOptimizedFilegroup];

GO
USE [$(DatabaseName)];

GO
IF EXISTS (SELECT 1
    FROM   [master].[dbo].[sysdatabases]
    WHERE  [name] = N'$(DatabaseName)')
BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET ANSI_NULLS ON,
            ANSI_PADDING ON,
            ANSI_WARNINGS ON,
            ARITHABORT ON,
            CONCAT_NULL_YIELDS_NULL ON,
            NUMERIC_ROUNDABORT OFF,
            QUOTED_IDENTIFIER ON,
            ANSI_NULL_DEFAULT ON,
            CURSOR_DEFAULT LOCAL,
            CURSOR_CLOSE_ON_COMMIT OFF,
            AUTO_CREATE_STATISTICS ON,
            AUTO_SHRINK OFF,
            AUTO_UPDATE_STATISTICS ON,
            RECURSIVE_TRIGGERS OFF 
        WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [$(DatabaseName)]
        SET AUTO_CLOSE OFF 
        WITH ROLLBACK IMMEDIATE;
END

我不确定在 CREATE DATABASE 之后是默认启用还是禁用 AUTO_CLOSE。如果默认为 ON，则 SSDT 似乎以错误的顺序生成部署脚本。如果默认关闭，那么我不明白为什么会出现错误。

有没有人在 SSDT 项目中成功创建内存优化文件组？

sql-server ssdt

2 个回答

  1. Best Answer

    我不确定在 CREATE DATABASE 之后是默认启用还是禁用 AUTO_CLOSE。如果默认为 ON，则 SSDT 似乎以错误的顺序生成部署脚本。如果默认关闭，那么我不明白为什么会出现错误。

    似乎 LocalDB 硬编码使用 AUTO_CLOSE，忽略模型

    本地数据库/SQLEXPRESS

    是的，当连接到 localdb 并运行标准的创建数据库命令时

    在此处输入图像描述

    CREATE DATABASE TEST

GO

SELECT name,database_id
FROM sys.databases 
WHERE is_auto_close_on = 1;

name    database_id
TEST    5

    is_auto_close_on即使model禁用了 auto_close ，它似乎也设置为 1。

    通常默认设置基于model系统数据库。

    理论上解决这个问题

    0您可以创建一个触发器，为新创建的数据库自动更改自动关闭。这是一个有点老套的方法，但有效。

    尝试/实施需要您自担风险。

    CREATE TRIGGER Change_Auto_Close 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
    DECLARE 
        @DatabaseName NVARCHAR(128)
        ,@SQL NVARCHAR(4000);
    SELECT 
        @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)')

    SET @SQL = 'USE [master] ALTER DATABASE ['+@DatabaseName+'] SET AUTO_CLOSE OFF WITH NO_WAIT;';

    IF @@TRANCOUNT = 1
    COMMIT TRAN; -- commit previous tran, otherwise we cannot alter the database
    -- PRINT @SQL;
    EXEC (@SQL);
    BEGIN TRAN; --begin new tran as not get error that the transaction ended in the trigger

GO

    来自这里和这里的想法

    其他版本

    可能是您的model数据库具有is_auto_close_on = 1，因为数据库是作为model. model以下是在数据库上启用和禁用 auto_close 的一些测试。

    设置自动关闭为model

    USE [master]
GO
ALTER DATABASE [model] SET AUTO_CLOSE ON WITH NO_WAIT;
GO

    创建默认数据库

    CREATE DATABASE AutoClosedDB;

    验证新创建的数据库的自动关闭状态

    SELECT name, is_auto_close_on 
FROM sys.databases 
WHERE name = 'AutoClosedDB';

    结果

    name            is_auto_close_on
AutoClosedDB    1

    设置自动关闭为model

    USE [master]
GO
ALTER DATABASE [model] SET AUTO_CLOSE OFF WITH NO_WAIT;
GO

    使用默认设置创建数据库

    CREATE DATABASE NoAutoClosedDB;

    验证新创建的数据库的自动关闭状态

    SELECT name, is_auto_close_on 
FROM sys.databases 
WHERE name = 'NoAutoClosedDB';

    结果

    name            is_auto_close_on
NoAutoClosedDB  0

    尝试在两个数据库上创建内存优化文件组

    ALTER DATABASE NoAutoClosedDB
    ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA;

    成功

    ALTER DATABASE AutoClosedDB
    ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA;

    失败并显示错误消息：

    消息 10794，级别 16，状态 125，第 33 行 具有 MEMORY_OPTIMIZED_DATA 文件组的数据库不支持操作“AUTO_CLOSE”。

    要验证model数据库的自动关闭状态，请运行以下脚本

    SELECT name, is_auto_close_on 
FROM sys.databases 
WHERE name = 'model';

    禁用自动关闭

    USE [master]
GO
ALTER DATABASE [model] SET AUTO_CLOSE OFF WITH NO_WAIT;
GO
    • 2

  2. 除了上面 Randi Vertongen 的回答之外，另一个解决方案是安装 SQL Server 实例，并在调试设置中编辑 SSDT 项目以连接到该实例而不是 LocalDB。

    例如，我现在只为 SSDT 构建安装了一个命名的 SQL Server 实例（按 F5 进行调试构建测试时），以及我用于实际部署的默认实例（使用 Publish 时）。

    这就克服了 LocalDB 的限制，默认的 AUTO_CLOSE 只是其中之一。

    • 2

相关问题