Tom_W's questions

我正在做一个需要缩小数据库的项目。在缩小之后，我检查sys.dm_db_index_physical_stats并可以看到我的很多索引都超过 99% 是碎片化的 ( avg_fragmentation_in_percent)。

对此，我IndexOptimize由 Ola Hallengren 执行，代码如下：

EXECUTE [dbo].[IndexOptimize]
    @Databases = 'Dynamic.Migrate',
    @FragmentationLow = 'INDEX_REORGANIZE',
    @FragmentationMedium = 'INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_OFFLINE',
    @FillFactor = '80',
    @UpdateStatistics = 'ALL',
    @StatisticsSample = '100',
    @LogToTable = 'Y'

这成功完成，但是当我sys.dm_db_index_physical_stats再次检查时，avg_fragmentation_in_percent值没有改变，并且状态我的索引仍然碎片化超过 99%。

我在这里错过了什么吗？

我有一个包含三个主要模式的数据库；

1. 全球的
2. 权限
3. 基于客户 ID 号的唯一架构名称（1、2、3、4 等...）

每个客户在所有三个模式中都有对象，具有全局和权限模式对象名称，包含客户 ID 号，后跟一个 _

例子;

[1].[Test]
[Global].[1_Test]
[Permissions].[1_Test]

到目前为止，我已经编写了执行此操作的波纹管脚本，但只保留一个客户数据，而不是多个；

    DECLARE @TenantId NVARCHAR(10)
DECLARE @Commands NVARCHAR(MAX)
DECLARE @Statements TABLE
    (
        Commands NVARCHAR(500)
    )

SET @TenantId = '686'

INSERT INTO @Statements
    EXECUTE  (

'SELECT ''DROP TABLE'' + '' '' + ''['' + s.[name] + '']'' + ''.'' + ''['' + t.[name] + '']'' AS ''Commands''

FROM sys.schemas s
    JOIN sys.tables t on s.schema_id = t.schema_id

WHERE   s.name <> '''+ @TenantId +''' 
AND         s.name <> ''dbo''
AND         t.name NOT LIKE '''+ @TenantId +'_%''
AND         t.name NOT IN (''DatabaseVersion'', ''PermissionTypes'')

ORDER BY s.name, t.name'    )

SELECT * FROM @Statements

--SET @Commands =(
--SELECT STRING_AGG (CAST([Commands] AS NVARCHAR(MAX)), ' ') FROM @Statements )

--SELECT (@Commands)

我如何转换它以便考虑多个 ID，除了一个？我觉得我需要在这里使用光标，但不确定它的结构。

提前致谢。

我将 Azure VM 与 SQL Server 可用性组和 Ola Hallengren 的脚本一起使用。备份工作没有问题，但是我注意到旧的备份文件没有从服务器中删除。

要删除文件，我正在运行此命令；

DECLARE @BackupDir NVARCHAR(4000) = (SELECT BackupDir = dbo.fn_SQLServerBackupDir())
DECLARE @Date DATETIME = DATEADD(DAY, -4, GETDATE())

EXECUTE [master].[dbo].xp_delete_file 0, @BackupDir, 'bak', @Date, 1;

注意：该函数获取 SQL 实例的默认备份位置。

每当我运行此命令时，它都会成功完成，但不会删除任何文件。Ola Hallengren 脚本中包含的清理过程也无法删除文件。

在非 AG 的其他环境中，上述命令以及 Ola Hallengren 脚本中的 Cleanup 可以正常工作。

这让我认为这个问题要么与环境有关，要么与在 AG 中的数据库上执行的备份有关。

以前有没有其他人遇到过这样的事情并且可能能够提供一些见解？

备份存储在 F:\ 驱动器上，它是服务器上的本地磁盘。我还确认已设置正确的权限。目前，我已经为所有人设置了完全控制权。（环境尚未上线）。

我尝试修改函数以\在备份路径的末尾添加 ，但这并没有解决问题。我已经确认也有满足日期要求的备份。

编辑1： 这是正在运行的备份命令，其中包括@Cleanup 参数，它不会删除文件；

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@BackupType = 'Log',
@Verify = 'Y',
@Checksum = 'Y',
@Compress = 'Y',
@LogToTable = 'Y',
@CleanupTime = 0,
@AvailabilityGroupDirectoryStructure = '{DatabaseName}',
@AvailabilityGroupFileName = '{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}_{Month}_{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256'
, @ServerCertificate = 'Cert_Name'

我目前正在编写许多“ AFTER INSERT, DELETE ”触发器，它们将用于在不同情况下在两个相同表之间同步数据。由于表格的设计方式，我现在遇到的问题是让 DELETE 部分工作。

该表称为“ MobileUsageReportPackageLog ”，PK 由以下列组成：UserId、DeviceId、PackageNumber。

我现在不确定如何将此表链接到“已删除”表，以确保如果删除了一条记录，那么触发器也会删除另一个表中的相同记录。

我有一种感觉，我错过了一些简单的东西，但如果有人对这种情况有任何建议或建议，我很想听听他们的意见。

提前致谢！

编辑：我应该提到，作为一个组合，所有三列都是唯一的。然而，作为单独的列，它们将包含重复数据。

截至目前的代码：

IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
    BEGIN
        DELETE murpl
            FROM [Audits].[dbo].[MobileUsageReportPackageLog] murpl
                JOIN deleted del ON murpl.[UserId] = del.[UserId]
                JOIN deleted del2 ON murpl.DeviceId = del2.[DeviceId]
                JOIN deleted del3 ON murpl.[PackageNumber] = del3.[PackageNumber]

我目前正在使用 Ola Hallengren 脚本为数据库备份整理代理作业。我已经编写了一个脚本，该脚本创建了一个代理作业来执行完整备份，并且运行良好。

现在，我想让脚本动态化。为什么？每个环境都有不同的证书用于加密备份，并且清理时间可能会有所不同。

这就是我迄今为止所拥有的；

DECLARE @SQLCertificate NVARCHAR(50)
SET @SQLCertificate = N'00132'

EXECUTE ('
IF NOT EXISTS (SELECT [Name] FROM [dbo].[sysjobs] WHERE [Name] = N''Backups_Encrypted_FULL'')
BEGIN
    EXECUTE sp_add_job
        @job_name = N''Backups_Encrypted_FULL'',
        @enabled = 1,
        @notify_level_eventlog=2, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @owner_login_name = N''DBA_Maintenance'',
        @description = N''Performs a FULL backup of all User Databases'',
        @category_name = N''[Database Backups]''

    EXECUTE sp_add_jobstep  
        @step_id=1, 
        @job_name = N''Backups_Encrypted_FULL'',
        @step_name = N''Execute T-SQL'',
        @subsystem = N''TSQL'',
        @command = N''EXECUTE [dbo].[DatabaseBackup]
    @Databases = N''USER_DATABASES'',
    @Directory = N''C:\SQLBackup'',
    @DirectoryStructure = N''{DatabaseName}'',
    @FileName = ''{DatabaseName}_{Description}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}'',
    @BackupType = ''FULL'',
    @Description = N''Full_Backup'',
    @CheckSum = ''Y'',
    @Verify = ''Y'',
    @Compress = ''Y'',
    @CleanupTime = ''0'',
    @CleanupMode = ''AFTER_BACKUP'',
    @Encrypt = ''Y'',
    @EncryptionAlgorithm = ''AES_256'',
    @ServerCertificate = '''+ @SQLCertificate +''',
    @LogToTable = ''Y'''',
        @database_name = N''DBA.Maintenance''

    EXECUTE sp_add_jobschedule
        @job_name = N''Backups_Encrypted_FULL'',
        @name = N''Saturday Night'',
        @enabled = 1,
        @freq_type = 8,
        @freq_interval = 64,
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_end_date=99991231, 
        @active_start_time=220000, 
        @active_end_time=235959

    EXECUTE sp_add_jobserver
        @job_name = N''Backups_Encrypted_FULL'',
        @server_name = N''(Local)''
END ')

GO

但是，运行它会出现以下错误；

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'USER_DATABASES'.

有人对此有任何想法吗？

寻求有关如何处理 SQL 帐户的一些建议，这将在未来创建额外的 SQL 帐户...

设想：

我们的 Admin 生产应用程序使用名为Admin_Main的帐户连接到 SQL ，如连接字符串中所述。当有人在管理应用程序中创建新用户并为其分配报告许可证时，会在实例上为新用户创建一个新的 SQL 帐户。Admin_Main帐户用于创建此帐户。

当然，我不想为Admin_Main帐户添加比需要更多的权限，所以我的问题是；

我应该向此帐户添加哪些权限，以便它能够创建新的 SQL Server 帐户，但对实例没有管理员权限？

提前致谢！

我正在测试我用 T-SQL 编写的报告，但返回的结果存在一些问题。这是我遇到问题的代码部分；

CREATE TABLE #Tables
    (   ID      INT IDENTITY (1,1),
        TenantId        nvarchar(10),
        TaskTable   nvarchar(50),
        RecordTable nvarchar(50),   
        RecordColumn    nvarchar(50)    )

SELECT @TSQL AS SQLCommand
    INTO #Commands

INSERT INTO #Tables
    select schema_name(t.schema_id) as TenantId, 
                t.[name] TaskTable,
                t2.[name] RecordTable,
                c.[name] RecordColumn
    from sys.tables t, sys.tables t2
    join sys.columns c on t2.object_id = c.object_id
    where t.[name] in ('Task', 'Tasks')
    and t2.[name] in ('TimeRecord', 'TimeRecords')
    and schema_name(t.schema_id) >= 50
    and schema_name(t.schema_id) <> 275     -- Exclude this because it's missing tables
    and c.[name] like 'FK_Task%'
    group by (schema_name(t.schema_id)), t.[name], t2.[name], c.[name]

        union 

    select schema_name(t.schema_id) as TenantId, 
                t.[name] TaskTable,
                t2.[name] RecordTable,
                c.[name] RecordColumn
    from sys.tables t, sys.tables t2
    join sys.columns c on t2.object_id = c.object_id
    where t.[name] in ('Tasks_NEW', 'Task_NEW')
    and t2.[name] in ('TimeRecord_NEW', 'TimeRecords_NEW')
    and schema_name(t.schema_id) >= 50
    and c.[name] like 'FK_Task%'
    group by (schema_name(t.schema_id)), t.[name], t2.[name], c.[name]

        union

    select schema_name(t.schema_id) as TenantId, 
                t.[name] TaskTable,
                t2.[name] RecordTable,
                c.[name] RecordColumn
    from sys.tables t, sys.tables t2
    join sys.columns c on t2.object_id = c.object_id
    where t.[name] in ('Tasks_WOM', 'Task_WOM')
    and t2.[name] in ('TimeRecord_WOM', 'TimeRecords_WOM')
    and schema_name(t.schema_id) >= 50
    and c.[name] like 'FK_Task%'
    group by (schema_name(t.schema_id)), t.[name], t2.[name], c.[name]


SET @MaxRownum = (SELECT MAX(id) FROM #Tables)
SET @Iter = (SELECT MIN(id) FROM #Tables)

WHILE @Iter <= @MaxRownum
BEGIN
     SELECT @TenantId = TenantId, @TaskTable = TaskTable, @RecordTable = RecordTable, @RecordColumn = RecordColumn
        FROM #Tables
             WHERE id = @Iter

SET @TSQL =     N'SELECT '+@TenantId+' AS TenantId, COUNT(*) AS IntegrationDates, (SELECT COUNT(*) FROM [Dynamic].['+@TenantId+'].['+@TaskTable+']  WHERE SystemId NOT IN (SELECT '+@RecordColumn+' FROM [Dynamic].['+@TenantId+'].['+@RecordTable+'])) AS MissingTimeRecords FROM [Dynamic].['+@TenantId+'].['+@TaskTable+']  WHERE IntegrationDate IS NULL'

INSERT INTO #Commands
SELECT @TSQL AS SQLCommand  

SET @Iter = @Iter + 1

END

SELECT @TSQLFinal = IsNull(@TSQLFinal + '
UNION 
', '' ) + SQLCommand
FROM #Commands

CREATE TABLE #Results   (   ApplicationId INT,
                                                IntegrationDates INT,
                                                MissingTimeRecords INT)
INSERT INTO #results
EXEC (@TSQLFinal)

如果我SELECT * FROM #Commands看到我的动态语句构建的所有命令，在本例中为 275 行。

这是完美的，但如果我SELECT * FROM #results没有得到 275 行。我有时会得到 30 行，有时会得到 100 行，但它从来都不一致。

我在这里期待的是#Commands 临时表中的所有命令都得到执行，并且这些命令的结果被放入#results 表（因此是 UNION）

有什么能发现这里可能出了什么问题吗？

提前致谢！

汤姆。

我目前正在尝试整理一些可以告诉我每个客户使用的两个重要表的名称的东西。遗憾的是，这些表的名称并不总是相同，所以我目前想出了以下 T-SQL；

create table #test  (   [Database] sysname,
                                    [TenantId] sysname,
                                    [TasksTable] sysname,
                                    [TimeRecordTable] sysname   )

insert into #test
select      db_name() as [Database], 
                SCHEMA_NAME(tbl.schema_id) as [TenantId], 
                tbl.name AS [TasksTable],
                tbl2.name AS [TimeRecordTable]
from    sys.tables tbl, 
            sys.tables tbl2
where tbl.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW') 
and     tbl2.name IN ('TaskTimeRecord','TaskTimeRecord_NEW')
and     SCHEMA_NAME(tbl.schema_id) = '2'

select * from #test
drop table #test

运行此程序时，我得到了 200 多行重复数据，而不是包含我正在寻找的结果的单行。

有人对如何使它工作有任何建议吗？

我正在尝试编写一个存储过程，在我们的客户尝试升级之前检查孤立数据，因为孤立记录可能会导致问题。这是我到目前为止所拥有的；

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'DetectOrphanDataBeforeUpgrade')
DROP PROCEDURE [dbo].[DetectOrphanDataBeforeUpgrade]
GO

CREATE PROCEDURE [dbo].[DetectOrphanDataBeforeUpgrade]

@TenantId   INT = NULL

AS
BEGIN

DECLARE @OrphanAuditItems NVARCHAR(MAX)
DECLARE @OrphanAuditAnswers NVARCHAR(MAX) 
DECLARE @OrphanAuditQuestion NVARCHAR(MAX)

------------------------------------------------------------------------------------------------
/* Throw an error if the TenantId is NULL or Invalid.               */
------------------------------------------------------------------------------------------------
IF @TenantId IS NULL OR @TenantId NOT IN (SELECT Tenants FROM [Application].[dbo].[TenantIdNumber])

BEGIN
    THROW 51000, '@TenantId is invalid because it is NULL or does not exist.', 1;
END


------------------------------------------------------------------------------------------------
/* Checks for Orphan records related to the Audits table                */
------------------------------------------------------------------------------------------------
SET @OrphanAuditItems = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems] 
                                                    WHERE FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

SET @OrphanAuditAnswers = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers] 
                                                        WHERE AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])'

SET @OrphanAuditQuestion = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion] 
                                                        WHERE AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC (@OrphanAuditItems)
EXEC(@OrphanAuditAnswers)
EXEC(@OrphanAuditQuestion)

END;

目前，这个脚本正在运行，但是输出是；

我想在一行中返回结果，并让每一列都有自己的名称，例如：@OrphanAuditItems 结果显示为列名：OrphanAuditItems，等等。

实现这一目标的最佳方法是什么？

谢谢，汤姆

我正在转换一个过程以使用链接服务器，因为它引用的数据库现在已迁移到单独的集群。我几乎完成了这项工作，但我并没有停留在以下部分；

declare @CustomerId int

set @CustomerId = 2

exec (N'
USE [DatabaseName]

SELECT  @DiskUsageDataObjectsKB = SUM(alloUni.used_pages) * 8
FROM        sys.tables sysTab
            INNER JOIN sys.indexes ind 
            ON sysTab.OBJECT_ID = ind.OBJECT_ID  and ind.Index_ID<=1
            INNER JOIN sys.partitions parti 
            ON ind.OBJECT_ID = parti.OBJECT_ID AND ind.index_id = parti.index_id
            INNER JOIN sys.allocation_units alloUni 
            ON parti.partition_id = alloUni.container_id
WHERE       SCHEMA_NAME(sysTab.SCHEMA_ID) = CAST('+ @CustomerId +' AS NVARCHAR(MAX))') AT [LinkedServer]

select @DiskUsageDataObjectsKB 

如您所见，我正在使用动态 SQL 将 CustomerId 变量传递给此变量，但我不确定如何将 @DiskUsageDataObjectsKB 值返回给过程，以便将其添加到我们的报告数据库中。

请问有人对如何做到这一点有什么建议吗？

非常感谢！汤姆

设想

我有一个包含我们所有客户数据的数据库，这些数据由模式分隔。例如：

Customer1 = Schema1, 
Customer2 = Schema2, 
Customer3 = Schema3, 
etc...

有时我们会遇到表中的 Date 值有时为 NULL 的问题。该表包含在每个 Schema 中，但有时可以称为“Tasks”、“Tasks_NEW”或“Task_NEW”（喜欢一致性）。

我正在寻找一种可以动态运行 SELECT 语句的方法，该语句将返回所有表和所有模式的所有 NULL 日期值的计数。

到目前为止，我这样做是为了将 Schema，加上 Table 放入一个 TempTable；

CREATE TABLE #TASK_TABLES
(   ID      INT IDENTITY(1,1),
    TenantID        INT,
    TableName       NVARCHAR(255)   )

INSERT INTO #TASK_TABLES
SELECT TABLE_SCHEMA, TABLE_NAME
FROM [Dynamic].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME IN ('Tasks', 'Task_NEW', 'Tasks_NEW')
ORDER BY TABLE_SCHEMA

现在我有了这些信息，我想用这样的 SELECT 循环遍历它；

SELECT Schema, COUNT(Date)
FROM [Dynamic].[Schema].[Table] (NOLOCK)
WHERE DATE IS NULL

可悲的是，我还不完全知道如何编写动态 SQL，因为我仍在学习，但我希望有人能够指出我正确的方向？

我们的数据库正在接收以下请求：

select * from [schema].[table] (nolock) where (Date_A > Date_B and Archive = 0)

这是我们的应用程序发出的正常请求。平均而言，请求需要 200 毫秒到 400 毫秒才能完成，CPU 大约需要 100 毫秒到 150 毫秒。读取通常在 48k 左右。这至少是该脚本如何执行的一个示例。

加快此类请求的最佳方式（如果有的话）是什么？

该表有 240,932 行，查询返回 0 条记录。