Martin Guth's questions

我养成了将连接条件与其他附加条件分开的习惯。但是我理解逻辑执行顺序是：

1. 从
2. 在哪里

如果我在where子句而不是join子句中添加附加条件，是否会损害性能；还是这部分通常在查询优化阶段得到简化和同等处理？

以下是两个返回相同计划的简单示例查询：

USE StackOverflow2010;

-- additional filters in where clause
SELECT TOP 500 p.id
FROM        dbo.Posts p
INNER JOIN  dbo.Votes v ON p.id = v.PostId
WHERE 
    v.VoteTypeId = 2
ORDER BY p.id
;

-- all criteria in on clause
SELECT TOP 500 p.id
FROM        dbo.Posts p
INNER JOIN  dbo.Votes v ON p.id = v.PostId AND   v.VoteTypeId = 2
ORDER BY p.id
;

我想补充一点，如果我编写更长的分析语句，通常跨越 100 多行（格式化），我会尝试尽快减少结果集，通常使用派生表并在它到达连接之前添加一个额外的地方.

我目前正在摆弄零碎的恢复。我有一些来自 Jes Borland 的演示代码，来自她的精彩演讲“使用高级恢复方法最小化数据丢失”（如果您有兴趣，请查看https://www.youtube.com/watch?v=iSUGX3rdirY ）。

我运行了她演示代码的摘录。由于非常奇怪的原因，我收到一条错误消息

消息 3159，级别 16，状态 1，第 269 行 数据库“OrganizeMyLego”的日志尾部尚未备份。如果日志包含您不想丢失的工作，请使用 BACKUP LOG WITH NORECOVERY 备份日志。使用 RESTORE 语句的 WITH REPLACE 或 WITH STOPAT 子句仅覆盖日志的内容。

使用 Filegroup LargeLego 的第二个 RESTORE 命令。

我在不同的环境中运行代码，并注意到在 SQL 2017 开发人员版和 SQL 2014 企业版上运行良好，而在 SQL 2019 标准版和 SQL 2017 标准版上我收到此错误消息。

我是否错过了有关标准版中零碎恢复的支持？

/* 
Understanding Restore Methods to Ensure Database Recovery 
Copyright Jes Borland, 2015 
*/

/* Create ClientA and ClientB databases 
        --Database A
        CREATE DATABASE ClientA;
        GO 
        USE ClientA; 
        GO

        CREATE TABLE Products 
        (ProductID INT IDENTITY PRIMARY KEY, 
        ProductName VARCHAR(100), 
        Price FLOAT); 
        INSERT INTO Products 
        VALUES ('Thingamajig', 1.00), ('Gizmo', 2.00), ('Widget', 3.00); 

        CREATE TABLE OrdersClientA 
        (OrderID INT IDENTITY PRIMARY KEY, 
        OrderDate DATETIME2, 
        ClientID INT, 
        ProductID INT FOREIGN KEY REFERENCES Products(ProductID), 
        Quantity INT);  
        INSERT INTO OrdersClientA 
        VALUES ('1/15/2014', 75, 2, 10), ('2/1/2014', 89, 1, 100), ('2/7/2014', 65, 2, 5);  

        --Database B 
        CREATE DATABASE ClientB;
        GO
        USE ClientB; 
        GO

        CREATE TABLE Products 
        (ProductID INT IDENTITY PRIMARY KEY, 
        ProductName VARCHAR(100), 
        Price FLOAT);
        INSERT INTO Products 
        VALUES ('Thingamajig', 1.00), ('Gizmo', 2.00), ('Widget', 3.00);

        CREATE TABLE OrdersClientB 
        (OrderID INT IDENTITY PRIMARY KEY, 
        OrderDate DATETIME2, 
        ClientID INT, 
        ProductID INT FOREIGN KEY REFERENCES Products(ProductID), 
        Quantity INT); 
        INSERT INTO OrdersClientB
        VALUES ('1/28/2014', 87, 3, 25), ('2/4/2014', 87, 1, 10), ('3/9/2014', 23, 2, 16); 

        /* Full backups */
        BACKUP DATABASE ClientA TO DISK = 'B:\DB_Backups\ClientA_Full.bak';
        BACKUP DATABASE ClientB TO DISK = 'B:\DB_Backups\ClientB_Full.bak';
*/
/*Create OrganizeMyLego with multiple filegroups and files */

        CREATE DATABASE OrganizeMyLego
        ON PRIMARY
        (NAME = OrganizeMyLego_data,
         FILENAME = 'X:\DB_Data\OrganizeMyLego_data.mdf'),
        FILEGROUP LargeLego
        (NAME = LargeLego_1,
         FILENAME = 'X:\DB_Data\LargeLego_1.mdf')
        LOG ON
        (NAME = OrganizeMyLego_log,
         FILENAME = 'X:\DB_Data\OrganizeMyLego_log.ldf');

        ALTER DATABASE OrganizeMyLego
        ADD FILE
        (NAME = LargeLego_2,
         FILENAME = 'X:\DB_Data\LargeLego_2.mdf')
        TO FILEGROUP LargeLego;

        ALTER DATABASE OrganizeMyLego
        ADD FILEGROUP MediumLego; 
        ALTER DATABASE OrganizeMyLego
        ADD FILE
        (NAME = MediumLego_1,
         FILENAME = 'X:\DB_Data\MediumLego_1.mdf'), 
        (NAME = MediumLego_2,
         FILENAME = 'X:\DB_Data\MediumLego_2.mdf')
        TO FILEGROUP MediumLego; 

        ALTER DATABASE OrganizeMyLego 
        ADD FILEGROUP SmallLego;
        ALTER DATABASE OrganizeMyLego 
        ADD FILE
        (NAME = SmallLego_1,
         FILENAME = 'X:\DB_Data\SmallLego_1.mdf'),  
        (NAME = SmallLego_2,
         FILENAME = 'X:\DB_Data\SmallLego_2.mdf')
        TO FILEGROUP SmallLego; 

        ALTER DATABASE OrganizeMyLego 
        ADD FILEGROUP ArchiveLego;
        ALTER DATABASE OrganizeMyLego 
        ADD FILE
        ( NAME = LegoArchive_1,
         FILENAME = 'X:\DB_Data\LegoArchive_1.mdf')
        TO FILEGROUP ArchiveLego;

        /* Populate the database. */ 

        /* Archive table*/
        USE OrganizeMyLego;
        GO
        CREATE TABLE LegoSold 
            (ID INT IDENTITY, 
            DateSold DATETIME2, 
            SizeOfLego VARCHAR(25), 
            Color VARCHAR(25), 
            Quantity INT, 
            Price MONEY)
        ON ArchiveLego; 
        INSERT INTO LegoSold 
        VALUES ('2014/02/17', '32x32', 'Green', 10, 5.00);

        /* Large Lego */
        CREATE TABLE Large32x32 
            (ID INT IDENTITY, 
            Color VARCHAR(25), 
            Quantity INT) 
        ON LargeLego; 
        CREATE NONCLUSTERED INDEX IX_Large32x32_Color ON dbo.Large32x32(Color)
            ON MediumLego;
        INSERT INTO Large32x32 
        VALUES ('Green', 28), 
                ('Gray', 5), 
                ('Orange', 7), 
                ('Star Wars', 1); 

        /* Medium Lego */
        CREATE TABLE Medium8x2 
            (ID INT IDENTITY, 
            Color VARCHAR(25), 
            Quantity INT) 
        ON MediumLego; 
        INSERT INTO Medium8x2 
        VALUES ('Blue', 137), 
                ('Red', 222), 
                ('White', 143); 

        /* Small Lego */
        CREATE TABLE Small4x2 
            (ID INT IDENTITY, 
            Color VARCHAR(25), 
            Quantity INT) 
        ON SmallLego; 
        INSERT INTO Small4x2 
        VALUES ('Black', 98), 
                ('Red', 178), 
                ('Yellow', 166); 

        /* Full backup */
        BACKUP DATABASE OrganizeMyLego TO DISK = 'B:\DB_Backups\OrganizeMyLego_Full.bak' 
        WITH NOINIT, COMPRESSION; 


/* Restore copy of AdventureWorks 2014 for corruption */
    /*  RESTORE DATABASE CorruptMe
            FROM DISK=N'D:\Software Downloads\Adventure Works 2014 Full Database Backup\AdventureWorks2014.bak'
            WITH MOVE 'AdventureWorks2014_Data' to 'X:\DB_Data\CorruptMe_Data.mdf',
            MOVE 'AdventureWorks2014_Log' to 'X:\DB_Data\CorruptMe_log.ldf';*/

ALTER DATABASE  [OrganizeMyLego] SET RECOVERY FULL;



-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------

/* Piecemeal restore */

        /* Check setup */
        /* Filegroups and files */
        USE OrganizeMyLego;
        GO 
        SELECT FG.name as FilegroupName, 
            DF.name as [FileName] 
        FROM sys.database_files DF 
            INNER JOIN sys.filegroups FG ON FG.data_space_id = DF.data_space_id; 

        /* Objects on filegroups */
        USE OrganizeMyLego;
        GO 
        SELECT FG.name AS FilegroupName, 
            OBJ.name AS ObjectName, 
            OBJ.type_desc AS ObjectType, 
            PA.index_id AS IndexID 
        FROM sys.filegroups FG
            INNER JOIN sys.allocation_units AU ON AU.data_space_id = FG.data_space_id
            INNER JOIN sys.partitions PA ON PA.partition_id = AU.container_id 
            INNER JOIN sys.objects OBJ ON OBJ.object_id = PA.object_id
        WHERE OBJ.type_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE')
        ORDER BY FG.name;

        /* DB must be (FULL recovery) OR (SIMPLE recovery AND read-only) */
        ALTER DATABASE OrganizeMyLego SET RECOVERY FULL;

        /* Mark ArchiveLego read-only */
        ALTER DATABASE OrganizeMyLego SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
        ALTER DATABASE OrganizeMyLego MODIFY FILEGROUP ArchiveLego READONLY;
        ALTER DATABASE OrganizeMyLego SET MULTI_USER;

        /* Full backup. */
        BACKUP DATABASE OrganizeMyLego TO DISK = N'B:\DB_Backups\OrganizeMylego_Full.bak'
        WITH INIT; 

        /* Add record. */
        USE OrganizeMyLego;
        INSERT INTO dbo.Large32x32
        VALUES  ('Blue', 2); 

        /* Transaction log backup. */
        USE master;
        GO
        BACKUP LOG OrganizeMyLego TO DISK = N'B:\DB_Backups\OrganizeMyLego_Log1.trn'
            WITH INIT ; 

        /* Add record. */
        USE OrganizeMyLego;
        INSERT INTO dbo.Medium8x2
        VALUES  ('Orange', 45);

        --Back up tail of the log. 
        USE master;
        BACKUP LOG OrganizeMyLego TO DISK = N'B:\DB_Backups\OrganizeMyLego_LogTail.trn' 
            WITH 
            --NORECOVERY, 
            INIT, 
            NO_TRUNCATE;

        /* DISASTER! */ 
        USE master;
        DROP DATABASE OrganizeMyLego;

        /* This is a "partial-restore sequence". 
           First restore must include PRIMARY filegroup. 
           Must include WITH PARTIAL. 
           All restores must include logs if in FULL. */
        RESTORE DATABASE OrganizeMyLego 
        FILEGROUP = 'Primary'
        FROM DISK = N'B:\DB_Backups\OrganizeMylego_Full.bak'
            WITH PARTIAL, 
            NORECOVERY; 
        RESTORE LOG OrganizeMyLego  
        FROM DISK = N'B:\DB_Backups\OrganizeMyLego_Log1.trn' 
            WITH NORECOVERY; 
        RESTORE LOG OrganizeMyLego 
        FROM DISK = N'B:\DB_Backups\OrganizeMyLego_LogTail.trn' 
            WITH NORECOVERY;
        RESTORE DATABASE OrganizeMyLego
            WITH RECOVERY;

        /* Check file state. */
        SELECT [name], [state_desc] 
        FROM OrganizeMyLego.sys.database_files;

        /* Try to select from the Large32x32 table. */
        USE OrganizeMyLego; 
        GO
        SELECT Color, Quantity
        FROM dbo.Large32x32;

        /* Restore LargeLego
           NOTE: no "PARTIAL" here. 
           "Filegroup-restore sequence". */
        USE master;
        RESTORE DATABASE OrganizeMyLego 
        FILEGROUP = 'LargeLego'
        FROM DISK = N'B:\DB_Backups\OrganizeMylego_Full.bak' 
            WITH NORECOVERY;
        RESTORE LOG OrganizeMyLego 
        FROM DISK = N'B:\DB_Backups\OrganizeMyLego_Log1.trn' 
            WITH NORECOVERY; 
        RESTORE LOG OrganizeMyLego 
        FROM DISK = N'B:\DB_Backups\OrganizeMyLego_LogTail.trn' 
        WITH NORECOVERY;
        RESTORE DATABASE OrganizeMyLego
            WITH RECOVERY;

   /* Fehlermeldung:
   Msg 3159, Level 16, State 1, Line 269
The tail of the log for the database "OrganizeMyLego" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 269
RESTORE DATABASE is terminating abnormally.

Problem von Standard Edition ? 
*/

        /* Try to select from the Large32x32 table again. */
        USE OrganizeMyLego; 
        GO
        SELECT Color, Quantity
        FROM dbo.Large32x32;

        /* Try to insert into Large32x32 table 
           Has both clustered and non-clustered indexes. */
        INSERT INTO dbo.Large32x32
        VALUES  ('White', 2); 

        /* Try to select from Medium8x2 */
        SELECT Color, Quantity 
        FROM dbo.Medium8x2;

        /* Restore MediumLego. */
        USE master;
        RESTORE DATABASE OrganizeMyLego 
        FILEGROUP = 'MediumLego'
        FROM DISK = N'B:\DB_Backups\OrganizeMylego_Full.bak' 
            WITH NORECOVERY; 
        RESTORE LOG OrganizeMyLego 
        FROM DISK = N'B:\DB_Backups\OrganizeMyLego_Log1.trn' 
            WITH NORECOVERY; 
        RESTORE LOG OrganizeMyLego 
        FROM DISK = N'B:\DB_Backups\OrganizeMyLego_LogTail.trn' 
            WITH NORECOVERY;
        RESTORE DATABASE OrganizeMyLego
            WITH RECOVERY;

        /* Try to insert into Large32x32  
           Has both clustered and non-clustered indexes. */
        USE OrganizeMyLego;
        GO
        INSERT INTO dbo.Large32x32
        VALUES  ('White', 2); 

        /* Try to select from Medium8x2 */
        SELECT Color, Quantity 
        FROM dbo.Medium8x2;

        /* Try to insert into Medium 8x2 */
        INSERT INTO Medium8x2 
        VALUES ('Yellow', 55);

        /* Try to select from LegoSold */
        SELECT ID, DateSold, Quantity 
        FROM dbo.LegoSold; 

        /* Restore ArchiveLego. 
           Because this is a read-only filegroup, no log restores necessary. */
        USE master;
        RESTORE DATABASE OrganizeMyLego
        FILEGROUP = 'ArchiveLego'
        FROM DISK = N'B:\DB_Backups\OrganizeMylego_Full.bak' 
            WITH NORECOVERY;
        RESTORE DATABASE OrganizeMyLego
            WITH RECOVERY; 

        /* Try to select from LegoSold */
        USE OrganizeMyLego; 
        GO 
        SELECT ID, DateSold, Quantity 
        FROM dbo.LegoSold; 

        /* Check file state. */
        SELECT [name], [state_desc] 
        FROM OrganizeMyLego.sys.database_files;


            USE master;
        RESTORE DATABASE OrganizeMyLego 
        FILEGROUP = 'SmallLego'
        FROM DISK = N'B:\DB_Backups\OrganizeMylego_Full.bak' 
            WITH NORECOVERY; 
        RESTORE LOG OrganizeMyLego 
        FROM DISK = N'B:\DB_Backups\OrganizeMyLego_Log1.trn' 
            WITH NORECOVERY; 
        RESTORE LOG OrganizeMyLego 
        FROM DISK = N'B:\DB_Backups\OrganizeMyLego_LogTail.trn' 
            WITH NORECOVERY;
        RESTORE DATABASE OrganizeMyLego
            WITH RECOVERY;

    /* Check file state. */
        SELECT [name], [state_desc] 
        FROM OrganizeMyLego.sys.database_files;



-------------------------------------------------------------------------
/* 
Clean up
Drop databases 
*/
USE master;
GO
DROP DATABASE ClientA;
DROP DATABASE ClientB;
DROP DATABASE OrganizeMyLego; 
DROP DATABASE CorruptMe; 

/* Delete backups from D:\SQLBackups\Advanced Restores */ 

我已经通过普通安装的 SQL Server 2019 RTM 安装了 SQL Server 2019 CU 2。

现在我对启动时错误日志中的以下消息感到困惑：

RecoveryUnit::InitPersistentVersionStore 失败，数据库 [1] 名称 [master] RecoveryUnit::InitPersistentVersionStore 失败，数据库 [4] 名称 [msdb]

AFAIK PersistentVersionStore 链接到新功能加速数据库恢复，但是我没有配置它。

这是错误日志的屏幕截图：

重新启动服务后再次出现该错误。

CU2 是否自动为系统数据库启用？为了解决这个错误，我能做些什么吗？

我已经使用错误消息进行了网络搜索，但最令人惊讶的是没有找到任何有关它的信息。

快速更新（3月16日）：

• 通过 Microsoft 支持打开票证（尚无答复）
• 已安装 SQL Server 2019 CU 3：与 SP 2 一样，服务重新启动时错误仍然存​​在

今天我使用了系统对象 sys.dm_tran_active_transactions（老实说，这是第一次）。我遇到了分布式事务（Microsoft DTC）的问题，并且在我对 sp_whoIsActive 输出一无所知之后去那里挖掘。这个问题已经解决，但现在我看到表中有相当旧的事务，类型为“工作表”。这是以下查询的输出

SELECT  *,
 case transaction_type   
      when 1 then 'Read/Write'   
      when 2 then 'Read-Only'    
      when 3 then 'System'   
      when 4 then 'Distributed'  
      else 'Unknown - ' + convert(varchar(20), transaction_type)     
 end as tranType,    
 case transaction_state 
      when 0 then 'Uninitialized' 
      when 1 then 'Not Yet Started' 
      when 2 then 'Active' 
      when 3 then 'Ended (Read-Only)' 
      when 4 then 'Committing' 
      when 5 then 'Prepared' 
      when 6 then 'Committed' 
      when 7 then 'Rolling Back' 
      when 8 then 'Rolled Back' 
      else 'Unknown - ' + convert(varchar(20), transaction_state) 
 end as tranState, 
 case dtc_state 
      when 0 then NULL 
      when 1 then 'Active' 
      when 2 then 'Prepared' 
      when 3 then 'Committed' 
      when 4 then 'Aborted' 
      when 5 then 'Recovered' 
      else 'Unknown - ' + convert(varchar(20), dtc_state) 
 end as dtcState
FROM    sys.dm_tran_active_transactions 
ORDER BY transaction_begin_time

transaction_begin_time 恰好与 SQL Server 服务上次重新启动的时间相关。

我应该以任何方式担心这个吗？我确实查看了https://www.sqlservergeeks.com/sys-dm_tran_active_transactions/以了解有关 dmv 的更多信息。但是它只是提到工作表用于在 tempdb 中存储临时查询结果。但是我的理解是，如果我启动一个执行计划决定假脱机到 tempdb 并使用工作表的查询，则每次需要时都会创建一个新的工作表。因此，有 6 个这么旧的工作台似乎很奇怪。我可以假设这些是自服务重新启动以来重复使用的表，用于监视不断运行的查询或内部 SQL 服务器的东西。无论如何，只读事务类型似乎是无害的，我没有注意到阻塞或版本存储增长的迹象。

提前感谢你的帮助

马丁

问题

我在 SQL Server 2017 CU 5 上设置管理数据仓库时遇到问题。作业“collection_set_1_noncached_collect_and_upload”一直失败。它与“磁盘使用情况”集合有关。

错误消息如下（我突出显示了与恕我直言最相关的部分）：

以用户身份执行：RZN\d_sqlagent_cl_live。SSIS 错误。组件名称：GenerateTSQLPackageTask，代码：-1071636471，子组件：OLE DB Source 1，描述：SSIS 错误代码 DTS_E_OLEDBERROR。发生 OLE DB 错误。错误代码：0x80004005。OLE DB 记录可用。来源：“Microsoft SQL Server Native Client 11.0” Hresult：0x80004005 描述：“无法确定元数据，因为过程“AUDIT_TO_OTHER_FG”中的语句“EXEC sp_executesql @sql”包含动态 SQL。考虑使用 WITH RESULT SETS 子句明确描述结果集。 ”。. SSIS 错误。组件名称：GenerateTSQLPackageTask，代码：-1071636406，子组件：OLE DB Source 1, 描述：无法从数据源中检索列信息。确保数据库中的目标表可用。. SSIS 错误。组件名称：GenerateTSQLPackageTask，代码：-1071636406，子组件：生成 T-SQL 包任务，描述：无法从数据源检索列信息。确保数据库中的目标表可用。.master 包错误退出，之前的错误信息应该解释原因。处理退出代码 5。该步骤失败。

过程“ AUDIT_TO_OTHER_FG”是一个数据库级触发器。它的目的是将审计表（带有历史数据）放入另一个文件组。我们在数据库之上运行的 Java 应用程序使用的是 Hibernate，不需要指定文件组。然而，所有这些审计表都遵循特定的命名约定。因此触发器在 CREATE_TABLE 事件中触发，回滚表创建并在不同的文件组上再次创建表。

也许这不是将表放在与默认文件组不同的最优雅的版本......但是它在过去工作得很好并且直到现在才成为问题。

我之前为该环境设置了管理数据仓库数据收集器，因为它在 SQL Server 2008 上运行。该版本中的这些触发器没有任何问题。最近我们迁移到 SQL Server 2017，现在我遇到了这些问题。

我暂时放下了触发器，数据收集器工作正常。因此，它似乎以某种方式干扰了数据收集器的操作，问题在于所使用的动态 SQL。但是我不明白为什么这会导致问题，因为数据收集器似乎没有在我的用户数据库中创建任何表，并且触发器在数据收集器运行时不会触发。

解决方法已尝试

我已经阅读了一些“ WITH RESULT SETS ”并尝试按如下方式更改我的触发器：

• 将我的动态 SQL 执行代码从更改sp_execute_sql为EXECUTE(@sql) WITH RESULT SETS NONE;--> 相同的错误消息
• 将我的动态 SQL 执行代码更改为：+ N''; SELECT 1 AS output;''; /* append to dynamic sql generation */ EXECUTE(@sql) WITH RESULT SETS (out INT); /* add one output variable */
  • 收到一条新的错误消息：

作为用户执行的消息：RZN\d_sqlagent_cl_live。SSIS 错误。组件名称：DFT - 收集查询 0，代码：-1071636471，子组件：OLE DB 源 [14]，说明：SSIS 错误代码 DTS_E_OLEDBERROR。发生 OLE DB 错误。错误代码：0x80004005。OLE DB 记录可用。来源：“Microsoft SQL Server Native Client 11.0” Hresult：0x80004005 描述：“无法确定元数据，因为过程‘AUDIT_TO_OTHER_FG’中的语句‘EXECUTE(@sql) WITH RESULT SETS ((out INT))’不兼容在主批次中使用语句'SELECT @dbsize as 'dbsize', @logsize as 'logsize', @ftsize as 'ftsize','". . SSIS错误。组件名称：DFT - Collect Query 0，代码：-1071636406，子组件：OLE DB Source [14]，描述：无法从数据源中检索列信息。请确保您的目标表在数据库中可用。.SSIS 错误。组件名称：DFT - 收集查询 0，代码：-1073450982，子组件：SSIS.Pipeline，描述：OLE DB 源在预执行阶段失败并返回错误代码 0xC020204A。主程序包退出时显示error, previous error messages should explain the cause. Process Exit Code 5. 该步骤失败。

• 试图将 SQL 的输出列从数据收集器伪造到我的数据库触发器中，如下所示： SET @sql = N''DROP TABLE '' + QUOTENAME(@tableName) + N''; SELECT 1 AS dbsize, 2 AS logsize, 3 AS ftsize, 4 AS reservedpages, 5 AS usedpages, 6 AS pages;''; EXECUTE(@sql) WITH RESULT SETS ((dbsize BIGINT), (logsize BIGINT), (ftsize BIGINT), (reservedpages BIGINT), (usedpages BIGINT), (pages BIGINT));
  • 不幸的是，这不起作用......我现在在调用触发器时收到运行时错误

消息 11537，级别 16，状态 1，第 1 行 EXECUTE 语句失败，因为其 WITH RESULT SETS 子句为结果集编号 1 指定了 1 列，但该语句在运行时发送了 6 列。

所以这一切都没有奏效。您能否想出一种替代方法，如何让触发器正常工作，同时让数据收集器正常工作？

资源

这是数据库级触发器的原始源代码：

CREATE TRIGGER [AUDIT_TO_OTHER_FG]
ON DATABASE
FOR CREATE_TABLE
AS
  DECLARE @eventData XML;
DECLARE @sql NVARCHAR(4000);
DECLARE @tableName NVARCHAR(MAX);
DECLARE @defaultFG NVARCHAR(MAX);
DECLARE @auditSchema NVARCHAR(MAX) = 'CCC_AUDIT';
DECLARE @stmt NVARCHAR(MAX);
SET @eventData = EVENTDATA();
SET ANSI_PADDING ON

SET @tableName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)');

IF (@tableName LIKE '%_AUD' OR @tableName LIKE 'SubjectRevisionEntity')

  BEGIN
    SET @defaultFG = (SELECT
                        name
                      FROM sys.filegroups
                      WHERE is_default = 1);
    SET @sql = N'DROP TABLE ' + QUOTENAME(@tableName);
    EXEC sp_executesql @sql;
    SET @sql = @eventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)');
    SET @sql = REPLACE(@sql, ';', '')
    SET @sql = +@sql + ' ON ' + QUOTENAME(@auditSchema);
    EXEC sp_executesql @sql;
  END
GO

ENABLE TRIGGER [AUDIT_TO_OTHER_FG] ON DATABASE
GO

通过运行探查器跟踪，我发现罪魁祸首是以下看似无害的声明：

declare @p1 int
set @p1=7
exec sp_prepare @p1 output,NULL,N'
DECLARE @dbsize bigint 
DECLARE @logsize bigint 
DECLARE @ftsize bigint 
DECLARE @reservedpages bigint 
DECLARE @pages bigint 
DECLARE @usedpages bigint

SELECT @dbsize = SUM(convert(bigint,case when type = 0 then size else 0 end)) 
      ,@logsize = SUM(convert(bigint,case when type = 1 then size else 0 end)) 
      ,@ftsize = SUM(convert(bigint,case when type = 4 then size else 0 end)) 
FROM sys.database_files

DECLARE @allocateUnits table( 
        total_pages bigint
,       used_pages bigint
,       data_pages bigint
,       container_id bigint
,       type tinyint
); 

INSERT @allocateUnits SELECT total_pages, used_pages, data_pages, container_id, type FROM sys.allocation_units;

SELECT @reservedpages = SUM(a.total_pages) 
       ,@usedpages = SUM(a.used_pages) 
       ,@pages = SUM(CASE 
                        WHEN it.internal_type IN (202,204) THEN 0 
                        WHEN a.type != 1 THEN a.used_pages 
                        WHEN p.index_id < 2 THEN a.data_pages 
                        ELSE 0 
                     END) 
FROM sys.partitions p  
JOIN @allocateUnits a ON p.partition_id = a.container_id 
LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id 

SELECT 
        @dbsize as ''dbsize'',
        @logsize as ''logsize'',
        @ftsize as ''ftsize'',
        @reservedpages as ''reservedpages'',
        @usedpages as ''usedpages'',
        @pages as ''pages''
',1
select @p1

这分别使用 sp_describe_first_result_set：

    exec [sys].sp_describe_first_result_set N'
DECLARE @dbsize bigint 
DECLARE @logsize bigint 
DECLARE @ftsize bigint 
DECLARE @reservedpages bigint 
DECLARE @pages bigint 
DECLARE @usedpages bigint

SELECT @dbsize = SUM(convert(bigint,case when type = 0 then size else 0 end)) 
      ,@logsize = SUM(convert(bigint,case when type = 1 then size else 0 end)) 
      ,@ftsize = SUM(convert(bigint,case when type = 4 then size else 0 end)) 
FROM sys.database_files

DECLARE @allocateUnits table( 
        total_pages bigint
,       used_pages bigint
,       data_pages bigint
,       container_id bigint
,       type tinyint
); 

INSERT @allocateUnits SELECT total_pages, used_pages, data_pages, container_id, type FROM sys.allocation_units;

SELECT @reservedpages = SUM(a.total_pages) 
       ,@usedpages = SUM(a.used_pages) 
       ,@pages = SUM(CASE 
                        WHEN it.internal_type IN (202,204) THEN 0 
                        WHEN a.type != 1 THEN a.used_pages 
                        WHEN p.index_id < 2 THEN a.data_pages 
                        ELSE 0 
                     END) 
FROM sys.partitions p  
JOIN @allocateUnits a ON p.partition_id = a.container_id 
LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id 

SELECT 
        @dbsize as ''dbsize'',
        @logsize as ''logsize'',
        @ftsize as ''ftsize'',
        @reservedpages as ''reservedpages'',
        @usedpages as ''usedpages'',
        @pages as ''pages''
',NULL,1

我在具有两个节点（单实例集群）的故障转移集群中运行 SQL Server 2017 Standard CU 5。我已按照这两 本手册停用 CEIP aka 间谍软件功能：

• CEIP 服务被禁用和停止，
• 从集群中删除的 CEIP 角色
• 并且有关 CustomerFeedback 和 EnableErrorReporting 的所有注册表项都设置为 0。

但是我仍然收到消息“软件使用指标已启用”。在服务重新启动后的 SQL Server 错误日志中。此外，我的 Windows 事件日志中经常出现来自 ESENT 的错误，如下所示：

有针对此问题的MS 知识库文章。但是它适用于 SQL Server 2012。它表示此错误与“软件使用指标功能”有关。我已经按照建议授予 SQL Server 服务帐户的权限。但是错误消息不断出现，我不希望 Microsoft 收集数据。

以下是我的问题：

1. 这是与 CEIP 相同的功能还是其他东西？
2. 如何正确停用软件使用指标？

我做了一些搜索，但没有找到解决我的问题的方法。

我从一些用户不关心命名 PK 约束、外键或索引的数据库开始，以系统生成的名称结束，例如PK__CarRenta__3213E83F2E5BD364. 在mssqltips上，Aaron Bertrand 发表了一篇关于如何使用存储过程重命名这些对象的精彩文章。

太好了……但是我如何避免用户或应用程序创建没有名称的新对象，然后由 SQL Server 为其分配系统生成的名称？

我已经读过同一位伟大的 Aaron Bertrand撰写的关于如何使用基于策略的管理来执行命名约定的文章。然而，这似乎只适用于马已经狂奔（或对象已创建）后的通知。

你能想出一种方法来自动回滚试图创建一个没有名字的对象的语句吗？

我目前正在将数据库从 SQL Server 2008 (SP4) 迁移到 SQL Server 2017 (CU3)。一个显着的变化是所有文件组在迁移后都有两个数据文件。为完成此任务，我恢复了一个备份，添加了包含两个大小相同的文件以及相同的自动增长设置的新文件组，并使用以下语法传输数据：

CREATE UNIQUE CLUSTERED INDEX <PK of the table> ..... WITH (DROP_EXISTING = ON ,...) ON <new Filegroup>

不幸的是，我还必须移动一些 LOB，然后事情会变得有点复杂：

• 添加分区方案和分区函数（基本上具有相同的目标）
• 在新的分区方案上创建聚簇索引（WITH DROP_EXISTING=ON）
• 在新文件组上创建聚簇索引 (WITH DROP_EXISTING=ON)
• DROP 分区方案和函数

这种技术由 Kimberly Tripp在这里描述，可以追溯到 Brad Hoff。

感谢您的关注，现在回答我的问题：

通过重建这样的索引，文件组中需要多少可用空间？

我给你举个例子：

• 我有一个大小为 220GB 的 LOB 表（根据 sys.allocation_units 中的总页数除以 128 除以 1024）。
• 将我的新（空）文件组（仅托管该表）中的两个文件预置为 220/2 = 110 GB，每个文件增长 = 0。
• 尝试使用上述技术传输表但收到错误消息

无法为数据库“abc”中的对象“xyz”分配空间。“xyz_pk”因为“def”文件组已满。通过删除不需要的文件、删除文件组中的对象、向文件组添加其他文件或为文件组中的现有文件设置自动增长来创建磁盘空间。

• 将每个文件大小增加到 112 GB...再次尝试并收到相同的错误消息
• 最后打开文件的自动增长并且该过程成功完成

但是，每个文件的大小为 220 GB，每个文件中还有 50% 的可用空间。

这是建议的诊断查询 的输出文件大小加起来为 227,22 GB。

到目前为止，我不知道有任何其他补救措施DBCC SHRINKFILE可以消除可笑的大量可用空间。然而，这并不是我特别引以为豪的事情……它需要很长时间才能留下腐败等。

你能帮我理解为什么 SQL Server 分配了这么多可用空间，然后又愉快地按比例填充我的两个文件吗？

之后我会尝试准备一个演示……抱歉，我现在时间不够，也许你们中的一些专家已经知道这样做的原因。

在此先感谢您的帮助

马丁

我在 SQL Server 2017 CU3 上遇到了一些奇怪的错误消息。我正在迁移数据库并重新组织文件组。“重组”是指我使用存储过程在新文件组上为对象创建分区函数和分区方案，在分区时重建索引，然后删除分区。

最后我得到了一些空文件组。他们的文件被删除。文件组本身也被删除。这在大多数情况下都很有效。但是对于两个数据库，我删除了文件...留下一个文件组，但没有关联文件

ALTER DATABASE REMOVE FILEGROUP

引发错误 5042：

无法删除文件组“xyz”，因为它不为空。

问题

我怎样才能摆脱那个空文件组......可能是什么问题？

我已经阅读了一些常见问题，但它们在我的系统中不存在：

• 检查：

  SELECT * FROM sys.partition_schemes;
  SELECT * FROM sys.partition_functions;
  

  0 行...数据库中没有剩余分区对象

• UPDATE STATISTICS对于数据库中的所有对象

  没有效果

• 检查文件组上的索引：

  SELECT * FROM  sys.data_spaces ds
  INNER JOIN sys.indexes i
  ON ds.data_space_id = i.data_space_id
  WHERE ds.name = 'xyz'
  

  0 行

• 检查文件组中的对象：

  SELECT
      au.*,
      ds.name AS [data_space_name],
      ds.type AS [data_space_type],
      p.rows,
      o.name AS [object_name]
  FROM sys.allocation_units au
      INNER JOIN sys.data_spaces ds
          ON au.data_space_id = ds.data_space_id
      INNER JOIN sys.partitions p
          ON au.container_id = p.partition_id
      INNER JOIN sys.objects o
          ON p.object_id = o.object_id
  WHERE au.type_desc = 'LOB_DATA'
  AND ds.name ='xyz'
  

  0 行

在从文件组中删除文件之前，我还尝试了DBCC SHRINKFILE参数。EMPTYFILE这对我来说真的没有意义，但是我阅读了将其描述为修复的解决方案。反正没有效果。

我有一些希望阅读有关服务器故障的这个问题并尝试了以下方法：

• 更新所有统计信息
• 删除所有与索引无关的统计信息

然而，这没有任何效果。我仍然有一个没有关联文件的文件组，并且无法删除该文件组。我完全感到困惑，因为这发生在某些数据库中而不是其他数据库中（具有相同的结构）。当我DBCC CHECK FILEGROUP在这个空文件组上执行时，我收到一堆错误消息，如下所示：

无法处理对象“STORY_TRANSLATIONSCCC”（ID 120387498）、索引“Ref90159CCC”（ID 2）的行集 ID 72057594712162304，因为它位于未检查的文件组“CCC_APPLICATION_new”（ID 8）上。

“STORY_TRANSLATIONSCCC”的 DBCC 结果。对象“STORY_TRANSLATIONSCCC”的 0 页中有 0 行。

这是正常的还是它指向不寻常的事情？

这个问题可能是重复的，但是我在 dba.stackexchange 上的其他问题中找不到适合我的解决方法。请查看我已经尝试过的列表。这与无法删除未使用的文件组中描述的解决方案相同。

更多细节

也许它有助于理解我在错误发生之前做了什么。我正在计划迁移到新服务器。我目前正在测试实例上对此进行测试。数据库从 prod 服务器恢复，恢复模式切换为简单。我的目标是重组文件组并从每个文件组一个文件的模型转移到每个文件组两个文件的模型。为了实现这一点，我创建了新的空文件组，每个文件组有两个文件，然后将数据移过来。不幸的是，大多数对象都有 LOB 数据（XML 和二进制）......所以我也利用分区作为帮助来移动 lob 数据。最后，所有数据都驻留在新文件组中，旧文件组为空。然后我删除所有文件并删除相应的文件组。主文件组仍然存在，只是添加了另一个文件。我的问题。此过程工作正常，但在两个数据库中可以删除文件，但不能删除文件组。令人惊讶的是，这些数据库的结构应该与其他数据库的结构相同，在移动数据和删除旧文件组的过程中没有遇到任何问题。

因此，这是出现问题的两个数据库的文件组和文件的列表：

1. CCC_GENTE

前

+-----------------+------------+
| Filegroup       | Filename   |
+-----------------+------------+
| CCC_APPLICATION | CCC_APP    |
+-----------------+------------+
| CCC_ARCHIVE     | CCC_ARCHIV |
+-----------------+------------+
| CCC_AXN         | CCC_AXN    |
+-----------------+------------+
| CCC_GDV         | CCC_GDV    |
+-----------------+------------+
| PRIMARY         | CCC        |
+-----------------+------------+

后

    +-----------------+--------------------------+--------------------+----------------------------------------------------+
| Filegroup name  | Filegroup temporary name | Filename (logical) | Status                                             |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_APPLICATION | -                        | CCC_APP            | file removed, filegroup  cannot be removed (error) |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_ARCHIVE     | -                        | CCC_ARCHIV         | file and filegroup removed                         |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_AXN         | -                        | CCC_AXN            | file and filegroup removed                         |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_GDV         | -                        | CCC_GDV            | file and filegroup removed                         |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| PRIMARY         | -                        | CCC                | file renamed to PRIMARY_1                          |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| PRIMARY         | -                        | PRIMARY_2          | new file added                                     |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_APPLICATION | CCC_APPLICATION_new      | CCC_APPLICATION_1  | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_APPLICATION | CCC_APPLICATION_new      | CCC_APPLICATION_2  | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_ARCHIVE     | CCC_ARCHIVE_new          | CCC_ARCHIVE_1      | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_ARCHIVE     | CCC_ARCHIVE_new          | CCC_ARCHIVE_2      | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_AXN         | CCC_AXN_new              | CCC_AXN_1          | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_AXN         | CCC_AXN_new              | CCC_AXN_2          | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_GDV         | CCC_GDV_new              | CCC_GDV_1          | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_GDV         | CCC_GDV_new              | CCC_GDV_2          | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+

我希望这会有所帮助。还有第二个数据库，其中文件组名称不同，但为简洁起见，我将其省略。

我很快就会迁移到 SQL Server 2017，因此我正在努力美化我们数据库中一些被忽视的方面。一方面是文件组：我的 DBA 前任创建了多个文件组，但是因为没有人真正被告知大多数对象只是转到默认文件组 (PRIMARY)。

现在我想强制创建表和索引的人实际命名他们的对象应该驻留的文件组。含义：没有“ON [Filegroup]”的 CREATE TABLE 或 CREATE INDEX 应该回滚并返回文件组丢失的错误。有什么办法可以做到这一点，您会建议继续采用这种方法吗？我刚刚发现基于策略的管理无法阻止在默认文件组上创建对象。

这样做的要点是，someboy 应该首先为对象选择文件组，而不是在默认文件组中创建它们，然后必须根据 DBA 的咆哮移动对象:-)。

我想到了两种技术......但是由于缺少知识而无法开始使用它们中的任何一种：

• 使用数据库触发器检查是否有 on 子句，如果没有则回滚 --> 这似乎很脆弱，因为它取决于命令文本的文本分析
• 使主文件组的大小非常小（100KB？）以便系统表仍然能够容纳但其他中等大小的表会导致错误，因为没有足够的剩余空间 --> 这可能是个坏主意可能对数据库可用性产生严重的副作用，并且在防止在默认文件组中创建每个用户定义的表或索引方面不够精确

非常感谢您的帮助

马丁

我知道，这类问题已经回答过好几次了，但我没有找到一些当前的答案，我想知道 SQL Server Standard Edition 2016 SP1 是否改变了这件事，因为它支持企业版早期支持的所有“可编程性功能”只要。

我的具体情况如下：我有一个中央备份服务器，它将运行 SQL Server 2017 标准版，并定期恢复从其他 SQL Server 实例获取的新备份。大多数实例也将在 SQL Server 2017 Standard 上......但是有一个运行 SQL Server 2014 Enterprise Edition 的数据仓库。

• 是否可以在 SQL Server 2017 Standard Edition实例上恢复从 SQL Server 2014 Enterprise Edition实例获取的备份，或者这是否会导致有关Enterprise Edition功能的错误？
• 有什么方法可以免费试用吗？SQL Server 评估版等同于企业版，不是吗？或者微软现在引入了一种方法来将开发人员和评估许可证降级为 SQL Server 标准版功能？

非常感谢您的帮助

我们目前有一些索引ALLOW_PAGELOCKS设置为关闭。这样做大概是为了减少死锁。但是，我怀疑它在当时是否真的会产生影响。

现在我试图了解什么时候 SQL Server 实际上选择开始锁定页面而不是聚集索引中的键。我最近问过 Jonathan Keyhaisas，他告诉我，如果我在几个后续页面上触摸行，就会发生这种情况。但是，我没有设法通过使用示例查询更新聚集索引中的行来获得任何独占页锁。

您能否通过示例查询和表帮助我更好地理解页锁？我正在运行 SQL Server 2008 SP4。

提前致谢

马丁