Tom_W's questions

Estou trabalhando em um projeto que requer uma redução de banco de dados. Após o encolhimento, verifiquei sys.dm_db_index_physical_statse posso ver que muitos dos meus índices estão mais de 99% fragmentados ( avg_fragmentation_in_percent).

Em resposta a isso, IndexOptimizeexecutei por Ola Hallengren, com o seguinte código:

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'

Isso foi concluído com sucesso, mas quando eu verifico sys.dm_db_index_physical_statsnovamente, os avg_fragmentation_in_percentvalores não foram alterados e afirmo que meus índices ainda estão fragmentados em mais de 99%.

Estou faltando alguma coisa aqui?

Tenho um banco de dados com três esquemas principais;

1. Global
2. Permissões
3. Nome do esquema exclusivo com base no número de identificação do cliente (1, 2, 3, 4, ect...)

Cada cliente tem objetos em todos os três esquemas, com os nomes dos objetos do esquema Global e Permissions, contendo o número de ID do cliente seguido por um _

Exemplo;

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

Até agora, escrevi o script abaixo que faz isso, mas mantém apenas os dados de um cliente, não vários;

    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)

Como posso converter isso para que leve em consideração vários IDs, exceto apenas um? Sinto que preciso usar um cursor aqui, mas não tenho certeza de como isso seria estruturado.

Desde já, obrigado.

Estou usando VMs do Azure com um grupo de disponibilidade do SQL Server e scripts de Ola Hallengren. Os backups estão funcionando sem problemas, porém notei que os arquivos de backup antigos não estão sendo removidos do servidor.

Para remover os arquivos, estou executando este comando;

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;

Nota : A função obtém o local de backup padrão para a Instância SQL.

Sempre que executo este comando, ele é concluído com êxito, mas nenhum arquivo é removido. O processo de limpeza incluído no script Ola Hallengren também não consegue remover arquivos.

Em outros ambientes, que não são AGs, o comando acima, mais o script Cleanup in Ola Hallengren, funcionam sem problemas.

Isso me leva a pensar que o problema está relacionado ao ambiente ou tem algo a ver com o fato de os backups terem sido executados em bancos de dados em um AG.

Alguém já encontrou algo assim antes e pode ser capaz de oferecer algumas dicas?

Os backups são armazenados na unidade F:\, que é um disco local no servidor. Também confirmei que as permissões corretas foram definidas. No momento, defini o controle total para Todos. (O ambiente ainda não está ativo).

Tentei modificar a função para adicionar o \no final do caminho de backup, mas isso não resolveu o problema. Confirmei que também existem backups que atendem ao requisito de data.

EDIT 1: Aqui está o comando de backup que está sendo executado, que inclui o parâmetro @Cleanup, que não remove os arquivos;

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'

Atualmente estou escrevendo uma série de triggers " AFTER INSERT, DELETE " que serão usados ​​para sincronizar dados entre duas tabelas idênticas, em instâncias diferentes. O problema que estou tendo agora é fazer a parte DELETE funcionar, devido à forma como a tabela foi projetada.

A tabela é chamada de " MobileUsageReportPackageLog " e o PK é composto pelas seguintes colunas, UserId , DeviceId , PackageNumber .

Agora não tenho certeza de como vincular essa tabela à tabela " excluída ", para garantir que, se um registro for excluído, o gatilho também excluirá o mesmo registro na outra tabela.

Tenho a sensação de que está faltando algo simples, mas se alguém tiver alguma sugestão ou recomendação para uma situação como essa, adoraria ouvi-los.

Desde já, obrigado!

EDIT: Devo mencionar que, como uma combinação, todas as três colunas serão únicas. No entanto, como colunas individuais, elas conterão dados duplicados.

Código a partir de agora:

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]

Atualmente, estou montando trabalhos de agente para backups de banco de dados, usando os scripts Ola Hallengren. Montei um script que cria um Agent Job para realizar backups COMPLETOS e está funcionando perfeitamente.

Agora, quero tornar o script Dinâmico. Por quê? Cada ambiente terá um Certificado diferente para criptografar os backups e os tempos de limpeza podem variar.

Isto é o que tenho até agora;

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

Executá-lo no entanto, dá o seguinte erro;

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

Alguém tem alguma opinião sobre isso?

Buscando algumas recomendações sobre como lidar com uma conta SQL, que criará contas SQL adicionais no futuro...

Cenário:

Nosso aplicativo de produção Admin se conecta ao SQL usando uma conta chamada: Admin_Main , conforme indicado na string de conexão. Quando alguém cria um novo usuário no aplicativo Admin e atribui a ele uma licença de relatórios, uma nova conta SQL é criada na instância para o novo usuário. A conta Admin_Main é usada para criar esta conta.

Eu, é claro, não quero adicionar mais permissões à conta Admin_Main do que o necessário, então minha pergunta é;

Quais são as permissões que devo adicionar a essa conta, para que ela possa criar novas contas do SQL Server, mas não tenha permissões de administrador na instância?

Desde já, obrigado!

Estou testando um relatório que escrevi em T-SQL e estou tendo alguns problemas com os resultados que estão sendo retornados. Aqui está a seção do código com a qual estou tendo problemas;

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)

Se eu SELECT * FROM #Commandsconseguir ver todos os comandos criados pela minha Declaração Dinâmica, que são 275 linhas neste caso.

Isso é perfeito, mas se eu SELECT * FROM #resultsnão recebo 275 linhas. Às vezes recebo 30 linhas, às vezes 100, mas nunca é consistente.

O que estou esperando aqui é que todos os meus comandos na tabela temporária #Commands sejam executados e os resultados desses comandos sejam colocados na tabela #results (daí o UNION)

Alguma coisa pode detectar o que pode estar acontecendo de errado aqui?

Desde já, obrigado!

Tom.

No momento, estou tentando montar algo que me diga o nome de duas tabelas importantes que cada um de nossos clientes usa. Infelizmente, os nomes dessas tabelas nem sempre são os mesmos, então, atualmente, criei o seguinte 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

Ao executar isso, estou obtendo mais de 200 linhas de dados duplicados, em vez de uma única linha que inclui os resultados que estou procurando.

Alguém tem alguma sugestão de como fazer isso funcionar?

Estou tentando escrever um procedimento armazenado que verificará dados órfãos antes que nossos clientes tentem uma atualização, pois registros órfãos podem causar problemas. Isto é o que tenho até agora;

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;

No momento, este script está funcionando, porém a saída está;

Quero retornar os resultados em uma única linha e ter cada coluna com seu próprio nome, por exemplo: @OrphanAuditItems resultado mostra como nome da coluna: OrphanAuditItems e assim por diante.

Como é a melhor maneira de conseguir isso?

Obrigado, Tom

Estou convertendo um procedimento para usar um Linked Server, pois os bancos de dados aos quais ele faz referência já foram migrados para Clusters separados. Estou quase terminando este trabalho, mas não estou preso na parte seguinte;

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 

Como você pode ver, estou passando a variável CustomerId para isso usando SQL dinâmico, mas não tenho certeza de como obter o valor @DiskUsageDataObjectsKB de volta ao procedimento, para que ele possa ser adicionado ao nosso banco de dados de relatórios.

Alguém teria alguma sugestão de como isso pode ser feito, por favor?

Muito Obrigado! Tom

Cenário

Eu tenho um banco de dados que contém todos os dados de nossos clientes, que são separados por Esquemas. Por exemplo:

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

Às vezes, temos um problema em que um valor de data em uma tabela às vezes é NULL. Esta tabela está contida em cada Schema, mas às vezes pode ser chamada de 'Tasks', 'Tasks_NEW' ou 'Task_NEW' (tem que amar consistência).

Estou procurando uma maneira de executar dinamicamente uma instrução SELECT que retornará uma contagem de todos os valores de data NULL, em todas as tabelas e todos os esquemas.

Até agora, fiz isso para colocar o Schema, mais a Tabela em uma 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

Agora que tenho essas informações, quero percorrê-las com um SELECT como este;

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

Infelizmente, não sei como escrever SQL dinâmico, pois ainda estou aprendendo, mas esperava que alguém pudesse me indicar a direção certa?

Nosso banco de dados está recebendo as seguintes solicitações:

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

Esta é uma solicitação normal que é feita pelo nosso aplicativo. Em média, o pedido está demorando entre 200ms - 400ms para ser concluído, com cerca de 100ms - 150ms na CPU. As leituras normalmente chegam a cerca de 48k. Este é pelo menos um exemplo de como esse script funciona.

Qual seria a melhor forma (se houver) de agilizar esse tipo de solicitação?

A tabela tem 240.932 linhas e as consultas retornam 0 registros.