sepupic's questions

Tenho tabela particionada com vários índices, todos estão alinhados e apenas um não. Isso ocorre porque ele controla a exclusividade por meio de mais de uma partição:

CREATE UNIQUE NONCLUSTERED INDEX [ix_uq_fltr_IdTransazioneRettificata_BM] ON [dbo].[Transazioni]
(
    [IdTransazioneRettificata] ASC
)
WHERE ([id]>(10000000000.) AND [StatusFatturazione]<>(15) AND [IdTransazioneRettificata] IS NOT NULL)
ON [PRIMARY]

O erro ocorre onde faço uma exclusão simples:

select id
into #t
from billing_prod2.dbo.Transazioni
where DataInserimento = '20240229' 
and Data = '20240201'
--(162.321 rows affected)

alter table #t add primary key(id)

delete t
from billing_prod2.dbo.Transazioni t
     join #t tt
        on t.id =  tt.id

A saída do SSMS:

Msg 3624, Nível 20, Estado 1, Linha 11 Uma verificação de declaração do sistema falhou. Verifique o log de erros do SQL Server para obter detalhes. Normalmente, uma falha de asserção é causada por um bug de software ou corrupção de dados. Para verificar se há corrupção no banco de dados, considere executar DBCC CHECKDB. Se você concordou em enviar dumps para a Microsoft durante a configuração, um mini dump será enviado à Microsoft. Uma atualização pode estar disponível na Microsoft no Service Pack mais recente ou em um Hotfix do Suporte Técnico. Msg 596, Nível 21, Estado 1, Linha 10 Não é possível continuar a execução porque a sessão está no estado kill. Msg 0, Nível 20, Estado 0, Linha 10 Ocorreu um erro grave no comando atual. Os resultados, se existirem, deveriam ser descartados.

A saída do log de erros:

Erro: 17065, Gravidade: 16, Estado: 1. Asserção do SQL Server: Arquivo: <"cxrowset.cpp">, linha = 2026 Asserção com falha = 'cstePrefix >= 1' O intervalo está sem prefixo na coluna de ID da partição. Este erro pode estar relacionado ao tempo. Se o erro persistir após a nova execução da instrução, use DBCC CHECKDB para verificar a integridade estrutural do banco de dados ou reinicie o servidor para garantir que as estruturas de dados na memória não estejam corrompidas. Erro: 3624, Gravidade: 20, Estado: 1. Falha na verificação de declaração do sistema. Verifique o log de erros do SQL Server para obter detalhes. Normalmente, uma falha de asserção é causada por um bug de software ou corrupção de dados. Para verificar se há corrupção no banco de dados, considere executar DBCC CHECKDB. Se você concordou em enviar dumps para a Microsoft durante a configuração, um mini dump será enviado à Microsoft.

Há também um arquivo de despejo que é longo o suficiente, mas se isso ajudar, posso colá-lo também.

Nada disso diz claramente qual índice tem problemas.

DBCC CHECKDBnão diz nada,

dbcc checktable('dbo.transazioni') with NO_INFOMSGS, ALL_ERRORMSGS

não diz nada.

A única coisa que eu poderia imaginar seria um índice que não é usado com frequência, caso contrário mais erros seriam relatados no log de erros, então comecei com este, não alinhado e menos usado.

Depois de descartá-lo, o erro desapareceu, então o problema realmente estava naquele índice não alinhado.

Alguém pode explicar isso?

O banco de dados tem TDE habilitado.

Microsoft SQL Server 2019 (RTM-CU22-GDR) (KB5029378) - 15.0.4326.1 (X64) 18 de agosto de 2023 14:05:15 Copyright (C) 2019 Microsoft Corporation Standard Edition (64 bits) no Windows Server 2016 Datacenter 10.0 ( Build 14393: ) (Hipervisor)

Tenho a tarefa de fazer cópias periódicas do banco de dados, duas vezes ao dia, e não devo quebrar nenhuma cadeia de backup nem acumular esses backups temporários. Então, tentei fazer um copy_onlybackup initpara substituir o único arquivo de backup.

Meu banco de dados é criptografado usando TDE .

Eu uso backup compressionno nível do servidor, "padrão de compactação de backup" é 1 no meu sys.configurations. Eu não uso a with compressionopção explícita no meu comando.

Aqui minha @@versão completa:

Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) 23 de setembro de 2020 16:03:08 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64 bits) no Windows Server 2016 Datacenter 10.0 (Build 14393) : ) (Hipervisor)

Meu problema é esse erro abaixo:

Msg 3241, Level 16, State 40, Line 8 A família de mídia no dispositivo 'W:\SQL_backup\copy_only\billing_prod2_test.bak' está formada incorretamente. O SQL Server não pode processar esta família de mídia. Msg 3013, Level 16, State 1, Line 8 RESTORE DATABASE está terminando de forma anormal.

Aqui está o código completo que executei:

backup database billing_prod2
to disk = 'W:\SQL_backup\copy_only\billing_prod2_test.bak'
with init, copy_only--, format;
go

restore database billing_1
from disk = 'W:\SQL_backup\copy_only\billing_prod2_test.bak'
with move 'billing_prod2' to 'E:\SQL_data\billing_1.mdf',
move 'billing_prod2_log' to 'E:\SQL_data\billing_1_log.ldf',
replace

A primeira vez que executa sem erros, o tamanho do backup completo é de cerca de 35Mb e restaura perfeitamente.

Na segunda vez, o backup é executado sem erros, mas estranhamente tem um tamanho de 260Mb. Esse é o tamanho que vejo no disco e o mesmo tamanho está escrito na saída do restore headeronlyque ainda executa sem erros. Mas quando eu faço restore databaseo erro mencionado acima aparece.

Tentei executar o mesmo código usando outro db e outro disco para salvar o backup mas o resultado é sempre o mesmo: na primeira vez funciona, na segunda vez o arquivo é sobrescrito mas tem tamanho diferente que não é apenas um tamanho duplo, é quase 10 vezes o tamanho inicial. restore headeronlynunca falha e sempre relata apenas um arquivo dentro do arquivo de backup, mas o tamanho é multiplicado e restore databasefalha.

Somente quando adicionei formata opção, o tamanho do backup tornou-se normal e a restauração foi bem-sucedida.

Minha pergunta é: por que devo usar o formato com meu backup neste caso para poder restaurar?

Este fato está documentado aqui:

ALTER AUTHORIZATION (Transact-SQL)

Se a entidade de destino não for um banco de dados e a entidade estiver sendo transferida para um novo proprietário, todas as permissões no destino serão descartadas.

Eu não tinha ideia sobre isso até encarar o fato.

Um dia houve a necessidade de dar EXECUTEpermissão em cada procedimento armazenado existente/futuro para algum usuário, este usuário é um membro da db_datareaderfunção de banco de dados apenas e ele não deve ser capaz de modificar nenhum dado, mas desde que a cadeia de propriedade em vigor uma vez que ele tenha uma EXECUTEpermissão, ele seria capaz de modificar os dados através dos procedimentos armazenados. Então tive que quebrar a cadeia de propriedade e gerar um script que alterava a autorização em todas as tabelas existentes para algum outro usuário.

Assim que a autorização foi alterada, todas as permissões em todas as tabelas desse banco de dados desapareceram. Verifiquei a documentação mencionada acima e descobri que esse comportamento está documentado. Mas é bastante inesperado (para mim, pelo menos), então eu me pergunto se alguém tem alguma explicação. Por que eles fizeram isso? Por que há a necessidade de descartar todas as permissões em alguma tabela se a autorização nela foi alterada?

Aqui temos duas consultas semelhantes usando grouping sets onde a SELECTcláusula contém algumas expressões calculadas em agregação:

SELECT RN10, RN10 / 10, COUNT(*) FROM 
(
       SELECT RN, RN/10 AS RN10, RN/100 AS RN100 FROM 
       (
               SELECT RN = -1 + ROW_NUMBER() OVER (ORDER BY 1/0) 
               FROM master..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (RN10 / 10), ())
ORDER BY 1, 2

seu plano está aqui: primeiro plano de consulta

e

SELECT RN10, SUBSTRING(RN,3,99), COUNT(*) FROM 
(
       SELECT RN, SUBSTRING(RN,2,99) AS RN10 FROM 
       (
               SELECT RN = CAST(-1 + ROW_NUMBER() OVER (ORDER BY 1/0) AS VARCHAR(99)) 
               FROM master..spt_values
       ) A
) B
GROUP BY GROUPING SETS ((RN10), (SUBSTRING(RN,3,99)), ())
ORDER BY 1, 2

o plano correspondente está aqui: segundo plano de consulta

Ambas as consultas primeiro calculam alguma expressão para agregação, RN10 / 10no primeiro caso e SUBSTRING(RN,3,99)no segundo, então a mesma expressão é usada na SELECTcláusula, mas como o primeiro plano mostra ela é recalculada na primeira consulta e não na segunda.

Como resultado, temos NULLs no primeiro conjunto de resultados que é bastante inesperado:

Alguém pode explicar porque a primeira consulta faz o cálculo 2 vezes (uma na agregação e mais uma vez na final select) enquanto a segunda faz apenas uma vez?

Aqui está uma pequena reprodução:

create table dbo.t (id int primary key, v int);
insert into dbo.t values (1, 1), (2, 2);

create table dbo.s (id int primary key, v int);
insert into dbo.s values (1, 10);
go

create trigger dbo.tr_t__iou
on dbo.t
instead of update
as
begin
 set nocount on;

 exec sp_lock @@spid;
end;
go

update dbo.t set v = 10 where id = 1;

update t
 set
  v = 10
from
 dbo.s s join
 dbo.t t on t.id = s.id;

update t
 set
  v = 10
from
 (values (1, 10)) s(id, v) join
 dbo.t t on t.id = s.id;
go

drop table dbo.t, dbo.s;
go

sp_lockdentro dos triggerrelatórios U- key lockna linha afetada no primeiro e no último caso, mas no segundo caso não há lock, como isso pode ser explicado?

No exemplo abaixo o resultado de Index Spooljá está ordenado, por que temos Top N Sortaqui ao invés de simples Top?

use tempdb;
go

create table dbo.t1 (id int identity primary key, v int);
create table dbo.t2 (id int identity primary key, v int);

insert into dbo.t1
(v)
select top (1000)
row_number() over (order by 1/0)
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;

insert into dbo.t2
(v)
select top (10000)
row_number() over (order by 1/0) + 10000
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;

set statistics xml, io on;

select
sum(a.v + b.v)
from
dbo.t1 a outer apply
(select top (1) v from dbo.t2 where v >= a.v order by v) b;

set statistics xml,io off;
go

drop table dbo.t1, dbo.t2;
go

É reproduzível em todas as versões a partir de 2008 R2(só não tenho servidor com versões anteriores para testar)

Eu herdei um sistema onde o aplicativo está sendo executado em sysadminconta. Limitei essa conta a db_datareader+ db_datawriter+ EXECUTEem todo banco de dados e configurei extended eventsa sessão para pegar o permission errorsno servidor.
Foi muito surpreendente para mim ver algumas insertsfalhas, mesmo que o usuário seja membro db_datawritere nenhuma restrição granular nas tabelas seja aplicada.

Então observo que apenas as inserções que definiram IDENTITY_INSERTfalharam. O banco de dados em questão está cheio identitye há muito SET IDENTITY_INSERTem seu código. O código significa não apenas modulesarmazenado no servidor, mas também C#código.

Para poder definir identity_inserto usuário deve possuir a tabela ou ter ALTERpermissão na tabela. Mas o fato é que você NÃO PODE conceder ALTERapenas em tabelas , sou obrigado a conceder ALTERem um banco de dados inteiro para isso user (vou tornar esse usuário membro de db_ddladminrole que adicionará apenas 42 permissões(!!!) vs 51 permissões que poderiam ser adicionado às permissões do usuário, concedendo ALTERno banco de dados)

Não estou interessado em refatorar o banco de dados com sequence(a versão do servidor é 2014para que eu possa fazê-lo teoricamente) e não posso simplesmente adicionar execute as dboa todos os sp que uso identity_insertporque também há código de aplicativo para reescrever, só me pergunto por que a Microsoft faz algo tão estranho design de permissões que db_datawriternão pode definir identity_insert?

Existe outra maneira de fazer com que o usuário possa definir identity_insertque adicionará menos permissões do que db_ddladmin?

ATUALIZAR

Eu tentei a solução oferecida pelo LowlyDBA com synonyms:

create table dbo.t(id int identity);
go

alter schema sch transfer dbo.t;
go

create synonym dbo.t for sch.t;
go

set IDENTITY_INSERT dbo.t on;

Isso causa o erro

Msg 1088, Level 16, State 11, Line 1 Não é possível localizar o objeto "dbo.t" porque ele não existe ou você não tem permissões.

A solução de Antoine Hernandezconceder ao usuário ALTER apenas no esquema parece ser o menor dos males, então eu aceito

Aqui está meu repro que foi testado em SQL Server 2008 R2, 2012, 2016.

A segunda e a terceira consultas usam RowCount Spool, por que a primeira não?

create table dbo.t (id int identity primary key, v int);
--create statistics ST_t__v on dbo.t(v) with norecompute;

insert into dbo.t (v)
select top (10000) rand(checksum(newid())) * 5
from master.dbo.spt_values a cross join
     master.dbo.spt_values b;
go

declare @v int;

set statistics xml, io on;
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4);
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4 having count(*) > 0);
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 40);
set statistics xml, io off;
go

drop table dbo.t;
go

Como resultado, as estatísticas de IO são:

Table 't'. Scan count 2, ***logical reads 20024***, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table 't'. Scan count 2, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Table 't'. Scan count 2, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.