"período de tempo limite de solicitação de bloqueio excedido" Erro ao tentar ver hierarquias de banco de dados

Estava sendo causado por uma reversão perpétua de uma transação. Tive que eventualmente reiniciar meu cluster de servidor

Excluindo a consideração de Harware, talvez você precise executar o script para verificar quais são as atividades retendo a Sessão SQL, um dos cenários comuns é não usar um Implicit transactions Optionno SQL Server Management Studio.

Eu tive esse problema quando iniciei uma transação explícita na qual criei uma tabela em tempdb a partir de um script em execução em outro banco de dados (não tempdb). Quando eu fiz o commit da transação, o commit não pareceu liberar o bloqueio na tabela que eu criei no tempdb.

Graças a esta página , eu USEd tempdb e executei DBCC OPENTRANe obtive o SPID da conexão com o tempdb que estava causando o bloqueio. Então eu KILL <SPID number>para matá-lo.

Não muito gracioso, e perdi todas as informações da tabela que criei no tempdb, mas no meu caso tudo bem.

Como muitos já haviam apontado, geralmente há uma transação de longa duração, principalmente causada pela falta de uso de SET IMPLICIT TRANSACTIONS ON, que não deve ser usado de forma alguma. Para ver por que verifique o artigo perspicaz de Brent Ozar

De qualquer forma, você pode obter uma lista de transações pendentes de longa duração usando a seguinte consulta.

SELECT
    [s_tst].[session_id],
    [s_es].[login_name] AS [Login Name],
    DB_NAME (s_tdt.database_id) AS [Database],
    [s_tdt].[database_transaction_begin_time] AS [Begin Time],
    [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
    [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
    [s_est].text AS [Last T-SQL Text],
    [s_eqp].[query_plan] AS [Last Plan]
FROM
    sys.dm_tran_database_transactions [s_tdt]
JOIN
    sys.dm_tran_session_transactions [s_tst]
ON
    [s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
    sys.[dm_exec_sessions] [s_es]
ON
    [s_es].[session_id] = [s_tst].[session_id]
JOIN
    sys.dm_exec_connections [s_ec]
ON
    [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
    sys.dm_exec_requests [s_er]
ON
    [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
    sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY
    sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
where [s_tdt].[database_transaction_begin_time] is not null
ORDER BY
    [Begin Time] ASC;

https://www.brentozar.com/archive/2018/02/set-implicit_transactions-one-hell-bad-idea/

"Quando tento exibir as árvores de hierarquia para tabelas, exibições ou procedimentos no SSMS Object Explorer, o período de tempo limite da solicitação de bloqueio é excedido."

Eu tive exatamente o mesmo problema. Fui até a janela de execução da consulta e; declaração digitada e executada ROLLBACK.

Parece que algumas das séries de instruções que eu estava executando antes disso mantinham transações abertas. Especificamente, porque alguns deles eram instruções DDL. Uma vez que eu emiti o rollback, as hierarquias de objetos começaram a funcionar.

No meu caso, era alguém executando uma transação com BEGIN TRANmas não ROLLBACKou COMMIT.

A transação estava bloqueando as tabelas do sistema.

Estes são os comandos que me ajudaram a depurar e resolver o problema:

-- Find the open transactoin
DBCC opentran()

-- Check the details of the query and the query plan
exec sp_who2 129
exec sp_lock 129
exec sp_WhoIsActive @get_plans = 1, @get_transaction_info = 1

-- Kill the process
Kill 129

Há tantas coisas que isso pode acontecer que tudo o que posso oferecer são algumas perguntas para ajudar a guiá-lo em direção a uma resposta.

1. O banco de dados em um servidor dedicado apenas à execução do SQL Server? Caso contrário, outros processos podem estar interferindo, roubando tempo precioso do processador.

2. O servidor de banco de dados está essencialmente sem memória? O SQL Server tentará alocar cada byte que puder, mas se estiver na capacidade máxima e suas consultas exigirem que mais dados sejam carregados, ele terá que recorrer ao uso de memória virtual, o que aumenta radicalmente a quantidade de tempo que até mesmo consultas simples podem levar.

3. A largura de banda da rede do servidor de banco de dados é pequena para lidar com a transferência de dados em tempo hábil?

No final do dia, parece que a máquina na qual você está hospedando o SQL Server está subdimensionada para o que você está tentando fazer. É perfeitamente possível que você finalmente tenha atingido os limites de hardware em que o desempenho está caindo radicalmente. Se esse for o caso (as perguntas acima ajudarão você a determinar isso), convém mover o banco de dados para um servidor que seja dimensionado adequadamente para a quantidade de dados (e consultas) que você está tentando processar.

Isso pode significar usar processadores mais rápidos, unidades mais rápidas ou apenas instalar mais RAM.

Eu tive esse erro pop-up após a conclusão de uma operação de redução de banco de dados (mas não liberei o espaço). Para fixar isso:

1. CHECKPOINT (demorou um pouco para ser executado)
2. Reduza o arquivo novamente com "Liberar espaço não utilizado".

Eu tive um problema em que, após atualizar o procedimento armazenado, a atualização não estava sendo salva e, ao tentar acessar novamente, estava recebendo essa mensagem de erro que o tempo limite da solicitação de bloqueio excedeu, simplesmente desmarquei a transação Set implícita e isso funcionou para mim