Tenho um script para me mostrar os artigos de replicação de uma publicação:

declare @publication_name sysname = 'my_pub'
declare @database_name sysname = 'my_db'
 use [my_db]


select  ServerName = @@servername
       ,publication_name=p.name
       ,p.repl_freq
       ,p.status
       ,p.sync_method
       --,snapshot_job_name=j.name
       ,p.independent_agent
       ,p.immediate_sync
       ,p.allow_anonymous
       ,p.replicate_ddl
       ,article_name=OBJECT_SCHEMA_NAME(a.objid) + '.' + OBJECT_NAME(a.objid)
       ,a.pre_creation_cmd 
       ,pre_creation_cmd_desc  = CASE a.pre_creation_cmd 
         WHEN 0 THEN 'none      - Doesn''t use a command.'
         WHEN 1 THEN 'drop      - Drops the destination table.'
         WHEN 2 THEN 'delete    - Deletes the destination table.'
         WHEN 3 THEN 'truncate  - Truncates the destination table.'
         ELSE 'Not known - Radhe Radhe'
        END


            FROM dbo.syspublications P 
            INNER JOIN dbo.sysarticles A 
                    ON P.pubid = A.pubid

           left outer  JOIN msdb.dbo.sysjobs j
                    ON j.job_id = p.snapshot_jobid

 where p.name = @publication_name

Entretanto, nesta publicação específica em que estou trabalhando no momento, também tenho funções e esses objetos não são mostrados na minha consulta acima?

Como posso vê-los, já que sei que estão lá?

Recebi esta ótima consulta que mostra o backup mais recente para cada banco de dados existente .

SELECT
    M.name,
    [Recovery Model] = 
        M.recovery_model_desc,
    [State] = 
        M.state_desc,
    [Last Full Backup] = 
        FORMAT(ISNULL(M.D, '19000101'), 'dd-MM-yyyy hh:mm'),
    [Last Differential Backup] = 
        FORMAT(ISNULL(M.I, '19000101'), 'dd-MM-yyyy hh:mm'),
    [Last log Backup] = 
        FORMAT(ISNULL(M.L, '19000101'), 'dd-MM-yyyy hh:mm')
FROM
(
    SELECT
        db.name,
        db.state_desc,
        db.recovery_model_desc,
        a.type,
        a.backup_finish_date
    FROM master.sys.databases AS db
    LEFT OUTER JOIN msdb.dbo.backupset AS a
      ON a.database_name = db.name
) AS Sourcetable
PIVOT
(
    MAX(backup_finish_date)
    FOR type IN
    (
        D,
        I,
        L
    )
) AS M --ostRecentBackup
WHERE name NOT IN
(
    N'master',
    N'msdb',
    N'model',
    N'tempdb'
);

Notei que ele usa pivotamento. E se eu tivesse que adicionar o local também? Seriam várias colunas .

Seja dinamizando usando múltiplas colunas ou desdinamizando , como eu poderia adicionar a localização de cada tipo de backup à consulta acima?

basicamente onde está o último backup completo, onde está o último diff e o último log (se houver).

este é o script que uso para ver a localização do backup:

SELECT   
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.TYPE,
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupset.backup_size * 1.024 /1024/1024 as [Backup size in MB],
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
           ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
--WHERE msdb.dbo.backupset.database_name = 'PivCRM_Prod_Online_ED'
--AND TYPE = 'D'  -- L D and I
ORDER BY msdb.dbo.backupset.backup_start_date DESC

a questão é:

como eu adicionaria o local do backup (cada tipo) à primeira consulta?

Estou procurando o local da pasta de instantâneos para minha replicação de mesclagem

Eu tentei:

select top 5 * FROM sysmergepublications pub
select *       FROM dbo.sysmergeSubscriptions

mas isso não me mostra o nome da pasta e como precisa de permissões sobre ela

onde posso obter essas informações usando t-sql?

usando a estratégia de backup do Ola , há um trabalho

databaseBackup - bancos de dados do sistema cheios, como você pode ver na figura abaixo.

esse trabalho está programado para ser executado uma vez por semana, no domingo de manhã.

está tudo bem, já está funcionando há algum tempo.

Agora preciso alterá-lo para funcionar todos os dias.

isso funciona bem usando ssms, criando scripts para a mudança e aplicando-a.

porém, como preciso fazer isso em dezenas de servidores, precisarei de um script que descubra qual é o agendamento, altere-o e reaplique-o no trabalho

Não consegui, mas me pergunto se alguém já fez isso.

com base na imagem abaixo você pode ver o resultado de select * from sys.dm_exec_connections

bom DMV para mostrar as conexões atuais.

agora, se você observar os detalhes, as conexões de espelhamento do banco de dados não são criptografadas.

pergunta é:

deveria ser motivo de preocupação?

Tenho trabalhado com mapeamento de unidades dentro do sql server .

Mapeei algo semelhante a isto:

EXEC XP_CMDSHELL 'net use y: "\\my_server\Initialed Folders\MM" @@__password__@@ /User:mycompany\m_miorelli /persistent:NO'

e isso foi há mais de 3 meses.

agora eu mudei minha senha.

então quando eu for para o mesmo servidor, vamos chamá-lo de my_server, e eu executo isso

EXEC XP_CMDSHELL 'net use'

Eu obtenho este resultado:

você vê que há az: drive ainda pendurado aí, não sei exatamente como, talvez eu tenha mapeado ...persisted

Quando tento qualquer operação que envolva o servidor onde residem os backups, recebo esta mensagem de erro:

restore filelistonly from disk=N'\\backup_server\ Initialed Folders\MM\backup_of_a_database.bak'

Msg 3201, Level 16, State 2, Line 20
Cannot open backup device '\\backup_server\ Initialed Folders\MM\backup_of_a_database.bak'. Operating system error 1326(The user name or password is incorrect.).
Msg 3013, Level 16, State 1, Line 20
RESTORE FILELIST is terminating abnormally.

Eu tentei fazer isso :

exec xp_cmdshell 'net use  \\ip\xxx pass /user:xxx /persistent:no'

questões):

como posso me livrar dessa unidade mapeada? ou objetivo muito específico: como posso me livrar dessa coisa de senha errada?

isso não está acontecendo em nenhum outro servidor.

Atualizar

Seguindo o conselho de Craig nos comentários, obrigado Craig , removi minha unidade mapeada

EXEC XP_CMDSHELL 'NET USE Z: /d'

Isso me deixou feliz e agradecido, porém não resolveu o problema.

Este gatilho ações na criação do banco de dados - mostra um exemplo de um gatilho do lado do servidor que é acionado quando alguém cria um novo banco de dados.

Esta outra pergunta - Gatilho para alterar o agrupamento do banco de dados na criação - também mostra um exemplo de gatilho do lado do servidor que é acionado quando alguém cria um novo banco de dados.

Tenho aqui um exemplo de gatilho do lado do servidor que evita que os usuários descartem logins:

USE [master]
GO

--======================================================================
-- example of a server trigger - does not allow a login to be dropped
--======================================================================
create trigger [no_dropped_logins]   on all server   
for drop_login   
as     
insert into ##LOGIN_WATCH       
select r.*, s.login_name, s.host_name,        
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS 'CmdText'       
from sys.dm_exec_requests r          
inner join sys.dm_exec_sessions s          
on r.session_id = s.session_id          
where r.session_id = @@SPID

Minha pergunta é:

como posso evitar a criação de um servidor vinculado no sql server usando gatilhos do lado do servidor?

ao tentar impedir que o crescimento automático aconteça, recebi este script legal aqui .

entretanto, se isso acontecer, preciso saber como descobrir. isso posso fazer usando este script muito bom de Max Vernon .

                        print @@servername + ' - ' + SUBSTRING(@@version,1,COALESCE(CHARINDEX('Copyright',@@version,0)-1,108))
                        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
                        SET NOCOUNT OFF


/*
    Description:    display growth events for all databases on the instance
    by:             Max Vernon
    date:           2014-10-01
*/
DECLARE @Version NVARCHAR(255);
DECLARE @VersionINT INT;
SET @Version = CONVERT(NVARCHAR(255),SERVERPROPERTY('ProductVersion'));
SET @VersionINT = CONVERT(INT, SUBSTRING(@Version,1 ,CHARINDEX('.',@Version)-1));
DECLARE @cmd NVARCHAR(2000);
SET @cmd = '';
IF @VersionINT >= 9
BEGIN
    SET @cmd = 
'
DECLARE @trcfilename VARCHAR(1000);

SELECT @trcfilename = path 
FROM sys.traces WITH(NOLOCK)
WHERE is_default = 1;

IF COALESCE(@trcfilename,'''') <> ''''
BEGIN
SELECT [Radhe]=
    '''''''' + @@SERVERNAME + '''''','''''' +
     DB_NAME(mf.database_id) + '''''','''''' +
     mf.name + '''''','' +
     CONVERT(VARCHAR(255), a.NumberOfGrowths) + '','' +
     CONVERT(VARCHAR(255), CAST(a.DurationOfGrowthsInSeconds AS decimal(38, 20)))
    FROM
    (
        SELECT
            tt.DatabaseID AS database_id,
            tt.FileName AS LogicalFileName,
            COUNT(*) AS NumberOfGrowths,
            SUM(tt.Duration / (1000 * 1000.0)) AS DurationOfGrowthsInSeconds
            FROM sys.fn_trace_gettable(@trcfilename, default) tt
            WHERE (EventClass IN (92, 93))
            GROUP BY
                tt.DatabaseID,
                tt.FileName
    ) a
    INNER JOIN sys.master_files mf ON
        (mf.database_id = a.database_id) AND
        (mf.name = a.LogicalFileName);
END
ELSE
BEGIN
    SELECT @@SERVERNAME, ''NO TRACE FILE'';
END
';
EXEC sp_executesql @cmd;
END
ELSE
BEGIN
    SELECT [SERVER NAME]=@@SERVERNAME, [Product Version]=SERVERPROPERTY('ProductVersion');
END

o que está faltando e eu gostaria de acrescentar é: quando aconteceu o crescimento automático?

como posso descobrir isso?

quando executo o seguinte script:

-- ——— SCRIPT GRANTS for Object Privileges—————

IF OBJECT_ID('[sys].[sysrowsets]') IS NOT NULL   GRANT CONTROL on [sys].[sysrowsets] to [db_myrole_BA]

Recebo esta mensagem de erro:

Msg 15151, Level 16, State 1, Line 271
Cannot find the object 'sysrowsets', because it does not exist or you do not have permission.

No entanto:

quando executo isso:

select radhe=OBJECT_ID('[sys].[sysrowsets]')

Meu script original vem daqui:

-- SCRIPT GRANTS for Objects Level Privilegs
PRINT '-- ——— SCRIPT GRANTS for Object Privileges—————'
SELECT
[GRANTS for Object Privileges]='IF OBJECT_ID(''['+ sys.schemas.name + '].[' + sys.objects.name + ']''' + ') IS NOT NULL ' + CHAR(13) + SPACE(1) +
state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE SQL_Latin1_General_CP1_CI_AS
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id = 
sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id = 
sys.database_principals.principal_id
where sys.database_principals.name not in ( 'public', 'guest')
--order by 1, 2, 3, 5

Como é possível que eu esteja recebendo esse erro?

Quero salvar todas essas permissões, para que, quando restaurar o banco de dados, possa reaplicar essas permissões.

hoje tive que verificar se a criptografia em trânsito estava instalada e funcionando em um determinado servidor (sql server) e se não, configurá-la.

a maneira como isso é feito é:

No SQL Server Configuration Manager, expanda Configuração de rede do SQL Server, clique com o botão direito em Protocolos para e selecione Propriedades. Na guia Sinalizadores, na caixa ForceEncryption, selecione Sim e selecione OK para fechar a caixa de diálogo. Reinicie o serviço SQL Server.

a imagem abaixo é depois de reiniciar o serviço do sql server .

agora, sou um DBA que gosta do paraíso do DBA e de toda automação e coisas feitas por meio de scripts.

Como eu poderia fazer isso por meio de scripts? soluções parciais também são válidas. e pode ser PowerShell, T-SQL ou qualquer outra linguagem de script.

Eu tenho uma consulta que me mostra todas as instâncias do SQL Server em um servidor:

DECLARE @GetInstances TABLE
( Value nvarchar(100),
 InstanceNames nvarchar(100),
 Data nvarchar(100))

Insert into @GetInstances
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'

Select [ServerName] = @@servername,InstanceNames from @GetInstances 

e também tenho uma consulta que mostra as configurações de memória e o uso de uma instância específica:

SELECT [instance] = @@servername, p.* 
  FROM (SELECT name, [value_in_use] 
          FROM sys.configurations) t 
          PIVOT (MAX([value_in_use]) FOR name IN ([min server memory (MB)], 
                                                  [min memory per query (KB)], 
                                                  [max server memory (MB)], 
                                                  [optimize for ad hoc workloads])) p

existe uma maneira de ter o melhor desses dois mundos - ter todas as informações de todas as minhas instâncias do SQL Server, em uma consulta?

Gostaria de evitar a necessidade de criar um servidor vinculado.

Eu não me importaria de usar o PowerShell ou qualquer outra ferramenta, desde que não precise executá-lo como administrador.

Estou tentando converter mensagens de trabalhos msdb para xml .

Estou usando rotinas de backup de Ola Hallengren .

quando executo a rotina a seguir, ela funciona de maneira incrível e converte tudo.

agora, se eu mudar o número 3333 para 3702 ele falha.

Não consigo identificar o que vem depois do caractere 3701 na mensagem, mas isso está me impedindo de converter a mensagem completa para xml

Se você usa o Ola, pode testar em seu próprio sistema. deixe-me saber se você descobrir como alterar minha função para que eu possa converter nvarchar(max) ou varchar(max) em xml com sucesso .

create or alter  FUNCTION dbo.fnCleanString (@InputString VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @CleanedString VARCHAR(MAX) = '';
    DECLARE @Char VARCHAR(1);
    DECLARE @Index INT = 1;
    DECLARE @Len INT = 3333-- LEN(@InputString);

    WHILE @Index <= @Len
    BEGIN
        SET @Char = SUBSTRING(@InputString, @Index, 1);
        IF (ASCII(@Char) NOT BETWEEN 0 AND 31 OR ASCII(@Char) IN (9, 10, 13))
        BEGIN
            SET @CleanedString += @Char;
        END
        SET @Index += 1;
    END

    RETURN @CleanedString;
END
go

SELECT TOP 50
    SysJobs.name,
    SysJobs.enabled,
    Job.message AS Message_Text,
    TRY_CAST(
        (SELECT dbo.fnCleanString(Job.message) AS [text()]
         FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') AS XML
    ) AS Message_XML
FROM msdb.dbo.sysjobhistory Job WITH (NOLOCK)
INNER JOIN msdb.dbo.sysjobs SysJobs WITH (NOLOCK)
    ON Job.job_id = SysJobs.job_id
WHERE SysJobs.name = 'DatabaseBackup - USER_DATABASES - FULL'
ORDER BY Job.instance_id DESC;

Estou restaurando bancos de dados em cada lado das minhas replicações transacionais e de mesclagem. Basicamente, a publicação e o assinante são inicialmente o mesmo banco de dados.

Ainda preciso executar o agente de snapshot para começar.

Como posso executar o snapshot sem executar os comandos bcp, ou seja, sem copiar os dados?

Estou bem com a estrutura de dados a ser copiada.

como posso encontrar a lista de bancos de dados aos quais posso realmente me conectar?

Estou usando sys.master_files para verificar o estado dos arquivos do meu banco de dados, porém, no secundário, o banco de dados está sincronizando e aparece como ONLINE

Eu não posso fazer isto:

use my_database

e não posso fazer isso:

select * from my_database.sys.master_files

Msg 976, Nível 14, Estado 1, Linha 364 O banco de dados de destino, 'my_database', está participando de um grupo de disponibilidade e atualmente não está acessível para consultas. A movimentação de dados está suspensa ou a réplica de disponibilidade não está habilitada para acesso de leitura. Para permitir acesso somente leitura a este e outros bancos de dados no grupo de disponibilidade, habilite o acesso de leitura a uma ou mais réplicas de disponibilidade secundárias no grupo. Para obter mais informações, consulte a instrução ALTER AVAILABILITY GROUP nos Manuais Online do SQL Server.

Estou usando o sqlwatch para monitorar alguns dos meus servidores e ele tem um trabalho que exclui em lote de uma tabela chamadadbo.sqlwatch_logger_snapshot_header

Tive muitos problemas de locking e bloqueio pois essa tabela é utilizada por diferentes processos, então desabilitei todos os outros jobs com exceção daquele que trata dessa tabela.

Usei o seguinte script para desabilitar todos os outros trabalhos do sqlwatch:

use distribution

set nocount on
set transaction isolation level read uncommitted

declare @Job_id uniqueidentifier
declare @job_name varchar(300)
declare @category_name varchar(300)
declare @body varchar(max)
declare @flag tinyint

declare @enabled bit = 0;  --0 - disable 1-enable
declare @subject varchar(300) = case when @enabled = 0 then 'Disabling  sqlwatch jobs on' + @@servername else 'Enabling sqlwatch jobs on' + @@servername end;
declare @job_enabled bit;

set @flag = 0
set @body = 'The following jobs are going to be ' +
          case when @enabled = 0 then 'Disabled' else 'Enabled' end
          + ' : '+char(10)+Char(13)


IF OBJECT_ID('tempdb.[dbo].[#LogReaderAgents]') IS NOT NULL 
DROP TABLE [dbo].[#LogReaderAgents] 

CREATE TABLE [dbo].[#LogReaderAgents] ( 
[job_id]         UNIQUEIDENTIFIER                 NOT NULL,
[job_name]       SYSNAME                          NOT NULL,
[category_name]  SYSNAME                          NOT NULL,
[enabled]        TINYINT                          NOT NULL)

INSERT INTO [dbo].[#LogReaderAgents] ([job_id],[job_name],[category_name],[enabled])
select job_id, job_name = sj.name, category_name=sc.name, sj.enabled
from  msdb.dbo.sysjobs sj
inner join msdb.dbo.syscategories sc
        on sj.category_id = sc.category_id
where 1=1
  and sj.name like 'SQLWATCH%'
 -- and sc.category_id in (10,13)
 and sj.name not in ('SQLWATCH-INTERNAL-RETENTION')

-- exec sp_gettabledef 'dbo.#LogReaderAgents'
-- exec sp_GetInsertList 'TEMPDB','DBO.#LogReaderAgents'


DECLARE c1 CURSOR FOR
       SELECT Job_id,job_name, category_name, [enabled]
       FROM #LogReaderAgents
OPEN c1
FETCH NEXT FROM c1
       INTO @Job_id,@job_name,@category_name,@job_enabled
       WHILE @@FETCH_STATUS = 0
       begin

              if (select top (1) stop_execution_date from msdb.dbo.sysjobactivity ja
                     where Job_ID = @Job_id
                           and
                     ja.start_execution_date IS NOT NULL
                     order by Start_execution_date desc) is not NULL
              begin
                     set @flag = 1
                     Print @job_name +' is ' + case when @job_enabled=1 then 'Enabled' else 'disabled' end 
                     exec msdb..sp_update_job @job_id = @job_id, @enabled = @enabled
                     set @Body = @Body + char(10)+char(13) + @job_name + ' -- ' + @category_name +' is ' + case when @job_enabled=1 then 'Enabled' else 'disabled' end 
              end
              
              FETCH NEXT FROM c1
       INTO @Job_id,@job_name,@category_name,@job_enabled
       end
CLOSE c1
DEALLOCATE c1

entretanto, depois disso o trabalho ainda está em execução, mas não vejo nenhuma alteração no número de registros que estão nesta tabela.

aqui está o trabalho executando um procedimento e fazendo uma exclusão em lote:

o progresso tem sido lento, mas constante, havia 37,2 milhões de linhas

agora existem 36,9 milhões

então basicamente está funcionando, então tudo bem.

Agora minha pergunta:

Suponha que eu tivesse que interromper este trabalho por qualquer motivo,

é uma exclusão em lote, se eu tivesse que interromper este trabalho agora, quantas linhas foram afetadas, qual seria o custo de reverter o que ainda não foi confirmado?

Normalmente quando o banco de dados está online eu uso a seguinte consulta e obtenho quanto espaço há livre dentro de cada arquivo:

            SELECT CONVERT (decimal(12,2)  , 
                           (CONVERT(decimal(12,2), size)/128)/1024.00) 
                          - CONVERT(decimal(12,2),
                            ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)/1024.00)  as [FREESPACE GB],
                   * 
              FROM sys.database_files a WITH(NOLOCK)

você precisa estar no banco de dados que deseja consultar.

No entanto, como você pode fazer isso quando o banco de dados está offline?

Eu não encontrei uma maneira.

Eu estava tentando isso, mas não deu certo:

select object_name,counter_name,instance_name,cntr_value,
case cntr_type 
    when 65792 then 'Absolute Meaning' 
    when 65536 then 'Absolute Meaning' 
    when 272696576 then 'Per Second counter and is Cumulative in Nature'
    when 1073874176 then 'Bulk Counter. To get correct value, this value needs to be divided by Base Counter value'
    when 537003264 then 'Bulk Counter. To get correct value, this value needs to be divided by Base Counter value' 
    else 'I don"t know'   
end as counter_comments
from sys.dm_os_performance_counters with(nolock)
where cntr_type not in (1073939712)
and counter_name in ('Data File(s) Size (KB)                                                                                                          ',
                     'Database pages                                                                                                                  ')

Existe uma maneira de descobrir espaço livre dentro de arquivos de bancos de dados offline?

Eu tenho uma consulta que me mostra quanta memória o sql server está usando, as configurações como memória máxima e memória mínima, CPU, etc.

aqui está a consulta:

--============================================================
--checking current memory settings and usage
--marcelo miorelli
--16-feb-2014
--============================================================
               
SELECT R.[instance],
       R.[Logical CPU Count],
       R. [Hyperthread Ratio],
       R.[Physical CPU Count],
       R.[Physical Memory (GB)],
       k.[Max server memory (GB)],
     FOO.Memory_usedby_Sqlserver_MB,
     FOO.Locked_pages_used_Sqlserver_MB,
       k.[Min server memory (GB)],
       k.[optimize for ad hoc workloads],
       R.affinity_type_desc,
    FOO.process_physical_memory_low,
    FOO.process_virtual_memory_low,
       R.virtual_machine_type_desc,
       R.sqlserver_start_time
FROM (

                SELECT [instance]=@@servername,
                       cpu_count AS [Logical CPU Count], 
                       hyperthread_ratio AS [Hyperthread Ratio],
                cpu_count/hyperthread_ratio AS [Physical CPU Count], 
                CONVERT(decimal(12,2),physical_memory_kb/1024.00/1024.00) AS [Physical Memory (GB)], 
                affinity_type_desc, 
                virtual_machine_type_desc, 
                sqlserver_start_time
                FROM sys.dm_os_sys_info WITH (NOLOCK) 
                

) R 
INNER JOIN (


                SELECT [instance] = @@servername,
                       [Max server memory (GB)]   = CONVERT(decimal(12,2),CAST(p.[max server memory (MB)] AS DECIMAL(12,2))/1024.00),
                       [Min server memory (GB)]   = CONVERT(decimal(12,2),CAST(P.[min server memory (MB)] AS DECIMAL(12,2))/1024.00),
                       [min memory per query (MB)]= CONVERT(decimal(12,2),CAST(P.[min memory per query (KB)] AS DECIMAL(12,2))/1024.00),
                       p.[optimize for ad hoc workloads]

                FROM ( SELECT name, [value_in_use] 
                         FROM sys.configurations) t 
                        PIVOT (MAX([value_in_use]) 
                               FOR name IN (
                                             [min server memory (MB)], 
                                             [min memory per query (KB)], 
                                             [max server memory (MB)], 
                                             [optimize for ad hoc workloads]
                                            )) p


            ) K
ON R.instance = K.instance

INNER JOIN (

                            SELECT [instance] = @@servername,
                            CONVERT(decimal(12,2),CAST(physical_memory_in_use_kb AS DECIMAL(12,2))/1024.00) AS Memory_usedby_Sqlserver_MB,
                            CONVERT(decimal(12,2),CAST(locked_page_allocations_kb AS DECIMAL(12,2))/1024.00) AS Locked_pages_used_Sqlserver_MB,
                            CONVERT(decimal(18,2),CAST(total_virtual_address_space_kb AS DECIMAL(18,2))/1024.00/1024.00) AS Total_VAS_in_GB,
                            process_physical_memory_low,
                            process_virtual_memory_low
                            FROM sys.dm_os_process_memory t
        

           ) FOO
ON R.instance = foo.instance
OPTION (RECOMPILE);

esta consulta me dá isso - atualmente em um servidor de desenvolvimento não ocupado:

Minha pergunta está relacionada physical_memory_in_use_kbao locked_page_allocations_kbDMV sys.dm_os_process_memory.

Como posso descobrir quais objetos ou qualquer servidor sql está mantendo essa memória?

Percebo que no meu caso há uma memória mínima definida para 2 Gb, mas a memória em uso é superior a 5 Gb.

Pode ser que eu realmente não precise saber, pois provavelmente não há pressão de memória no momento, mas ainda assim, existe uma maneira de descobrir o que o SQL Server está mantendo na memória?

Ao fazer uma restauração de um grande banco de dados de 1,8 TB em um servidor diferente, me deparei com esta mensagem de erro quando a restauração estava cerca de 90% concluída:

90 por cento processado.
Msg 3013, Nível 16, Estado 1, Linha 52
RESTORE DATABASE está sendo encerrado de forma anormal.

Msg 1204, Nível 19, Estado 4, Linha 52
A instância do Mecanismo de Banco de Dados SQL Server não pode obter um recurso LOCK neste momento. Execute novamente seu extrato quando houver menos usuários ativos. Peça ao administrador do banco de dados para verificar a configuração de bloqueio e memória desta instância ou para verificar transações de longa execução.

SELECT [instance]=@@servername,[setting name]=name, value, value_in_use, [description]
FROM sys.configurations
where name in ('min server memory (MB)',
               'min memory per query (KB)',
               'max server memory (MB)',
               'optimize for ad hoc workloads')

                SELECT [instance]=@@servername,
                       cpu_count AS [Logical CPU Count], 
                       hyperthread_ratio AS [Hyperthread Ratio],
                cpu_count/hyperthread_ratio AS [Physical CPU Count], 
                CONVERT(decimal(12,2),physical_memory_kb/1024.00/1024.00) AS [Physical Memory (GB)], 
                affinity_type_desc, 
                virtual_machine_type_desc, 
                sqlserver_start_time
                FROM sys.dm_os_sys_info WITH (NOLOCK) 
                

usando isso para verificar o log de erros do sql, não consegui encontrar nenhuma indicação clara da causa do problema acima.

EXEC xp_readerrorlog 1

isso é o que eu encontrei:

Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

o que fazer para permitir que minhas restaurações funcionem perfeitamente e não parem no meio?

Acabei de pedir à infraestrutura para adicionar memória a uma caixa do SQL Server, a memória total agora é de 16 Gb

Eu configurei a memória máxima no SQL Server para 12 GB.

quando executo a consulta de memória abaixo:

SELECT [ServerName]=@@servername,
       [Setting Name]=name, 
       value, 
       value_in_use, 
       [Value (GB)]=CONVERT(decimal(12,2),CONVERT(decimal(12,2), value_in_use)/1024.00),
       [description] 
FROM master.sys.configurations with(nolock)
WHERE name like '%server memory%'
ORDER BY name OPTION (RECOMPILE);

--Msg 260, Level 16, State 3, Line 12
--Disallowed implicit conversion from data type sql_variant to data type numeric, 
--table 'master.sys.configurations', column 'value_in_use'. 
--Use the CONVERT function to run this query.


SELECT 
    CONVERT(decimal(12,2),physical_memory_in_use_kb/1024.00/1024.00 )                     AS sql_physical_memory_in_use_GB, 
    CONVERT(decimal(12,2),large_page_allocations_kb/1024.00/1024.00)                      AS sql_large_page_allocations_GB, 
    CONVERT(decimal(12,2),locked_page_allocations_kb/1024.00/1024.00)                     AS sql_locked_page_allocations_GB,
    CONVERT(decimal(12,2),virtual_address_space_reserved_kb/1024.00/1024.00)              AS sql_VAS_reserved_GB, 
    CONVERT(decimal(12,2),virtual_address_space_committed_kb/1024.00/1024.00)             AS sql_VAS_committed_GB, 
    CONVERT(decimal(12,2),virtual_address_space_available_kb/1024.00/1024.00)             AS sql_VAS_available_GB,
    REPLACE(CONVERT(VARCHAR(50),CAST(page_fault_count  AS MONEY),1), '.00','')            AS page_fault_count,
    memory_utilization_percentage                                                         AS sql_memory_utilization_percentage, 
    CASE WHEN process_physical_memory_low = 1 THEN 'Yes' ELSE 'No' END                    AS sql_process_physical_memory_low,
    CASE WHEN process_virtual_memory_low = 1  THEN 'Yes' ELSE 'No' END                    AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;


SELECT CONVERT(decimal(12,2),committed_kb/1024.00/1024.00 )        AS [SQL Server Committed Memory in GB],
       CONVERT(decimal(12,2),committed_target_kb/1024.00/1024.00 ) AS [SQL Server Target Committed Memory in GB],
       CONVERT(decimal(12,2),physical_memory_kb/1024.00/1024.00 ) AS [Server Physical Memory in GB],
       sql_memory_model_desc
FROM sys.dm_os_sys_info;

Eu entendi isso:

Pergunta:

Por que a memória do SQL Server não é de 12 GB?

Acabei de restaurar um banco de dados, mas notei que create_date permanece anterior à restauração.

existe uma maneira de alterar create_date para ser o novo banco de dados (após a restauração)?

este é o roteiro:

USE [master]
RESTORE DATABASE [SUPT_S01_Radhe] 
FROM  DISK = N'\\krishna3.radhe.co.uk\support\MM_Backups\S01__JagannathaeCA_12122023.bak' 
WITH  FILE = 1,  
MOVE N'radhe_Data' TO N'Y:\DATA\SUPT_S01_radhe_shyam_Data.mdf',  
MOVE N'radhe_ChangeLog' TO N'W\:\DATA\SUPT_S01_radhe_shyam_ChangeLog.ndf',  
MOVE N'radhe_Docs' TO N'Z:\DATA\SUPT_S01_radhe_shyame_Docs.ndf',  
MOVE N'radhe_Log' TO N'P:\LOGS\SUPT_S01_radhe_shyam_Log.ldf',  
NOUNLOAD,  REPLACE,  STATS = 1

select name, database_id, create_date from sys.databases d
where d.database_id in (1,2,3,4,8)