Início / dba / Perguntas / 2050
Accepted
jsauni
Asked: 2011-04-04 18:27:23 +0800 CST

Como posso saber se um banco de dados SQL Server ainda está sendo usado?

  • 772

Estamos procurando encerrar uma instância do SQL Server que ainda possui alguns bancos de dados.

Como posso saber se eles ainda estão sendo usados ​​por usuários ou um aplicativo da web?

Encontrei um tópico do fórum que tinha uma consulta T-SQL que você pode executar para recuperar a última data da consulta. Parece funcionar, mas quero saber se essa informação é válida o suficiente para descartar bancos de dados. É isso?

Se você tiver métodos alternativos que ajudariam também.

sql-server t-sql

7 respostas

  1. Best Answer

    Você teria que se preocupar com itens que foram removidos do cache e que você perdeu, ou com bancos de dados que têm uso infrequente.

    Em vez de descartar os bancos de dados, coloque-os OFFLINE para impedir o acesso sem eliminá-los ou no modo RESTRICTED_USER para limitar o acesso. Fazendo isso você pode deixá-los nesse estado por um mês ou dois para verificar e ver se há uso ocasional.

    Você também pode usar uma filtragem de rastreamento do criador de perfil do lado do servidor nesse banco de dados.

    • 32

  2. Estes são os métodos que usei no passado:

    1. Colocar o banco de dados offline/Desanexar
    2. NEGAR acesso de usuário/login
    3. Rastreamento do criador de perfil

    O problema é este: quanto tempo você espera antes de ter certeza de que ninguém vai acessar os dados? Para dados financeiros, você tem alguns itens executados diariamente, semanalmente, mensalmente, trimestralmente, semestralmente e anualmente. Mas será que um ano é suficiente? Também vi pedidos para manter os dados disponíveis por pelo menos 7 anos e, em um caso, me disseram que os dados em um sistema precisavam estar lá para sempre, mesmo que ninguém os estivesse usando.

    O melhor conselho é este: faça o que fizer para desativar o acesso, certifique-se de que pode ativá-lo novamente imediatamente. Achei que o detatch funcionou melhor para isso. Eu simplesmente roteirizaria a reanexação e instruiria minha equipe "se alguém perguntar onde está, execute este script". Isso nos deu a melhor chance de colocar as coisas de volta o mais rápido possível.

    • 14

  3. Concordo com Nic com seu conselho. Se você precisar ter certeza, precisará usar o Profiler (rastreamento do lado do serviço) porque algumas das consultas SQL não serão armazenadas em cache ou, por qualquer motivo, o cache do procedimento poderá ser limpo.

    Eu normalmente verificaria as informações de estatísticas do arquivo virtual também para ver se há alguma leitura ou gravação acontecendo no nível do arquivo do sistema operacional. Mesmo que o banco de dados NÃO esteja ativo, você ainda verá pequenas leituras/gravações se estiver fazendo backups de log, backups completos etc... mas isso também lhe dará uma ideia da atividade de leitura/gravação nesse banco de dados.

    Antes de descartar qualquer banco de dados, certifique-se de ter pelo menos 2 ou 3 backups legíveis (teste-os) em locais separados. Você nunca sabe quando precisa deles.

    • 13

  4. A consulta a seguir mostra os bancos de dados que não tiveram uso desde a última reinicialização, sem depender de planos de consulta mantidos no cache, pois mostra a E/S do usuário em relação aos índices (e heaps). Isso é como o uso de estatísticas de arquivos virtuais, mas o DMV usado aqui exclui a atividade de E/S dos backups. Não há necessidade de manter um rastreamento do criador de perfil em execução, sem necessidade de gatilhos ou auditoria. Obviamente, se você reiniciar seu servidor SQL com frequência (ou anexar/desligar bancos de dados com frequência), esse pode não ser o caminho a seguir :-)

    Dito isso, ainda concordo que, mesmo que essa consulta pareça confirmar que um banco de dados pode ser descartado, definitivamente faça o OFFLINE / desanexar ou negar o acesso do usuário por algum tempo, além de qualquer diligência de perguntar antes de realmente cair!

    select [name] from sys.databases 
where database_id > 4
AND [name] NOT IN 
(select DB_NAME(database_id) 
from sys.dm_db_index_usage_stats
where coalesce(last_user_seek, last_user_scan, last_user_lookup,'1/1/1970') > 
(select login_time from sys.sysprocesses where spid = 1))
    • 9

  5. Trabalhei em um lugar que tinha um grande número de bancos de dados órfãos e semi-órfãos. Era difícil dizer se eles realmente ficaram órfãos, pois muitas tarefas eram sazonais ou anuais - de modo que o site funciona apenas por 3-4 meses por ano (como exemplo, os formulários W2 precisam ser arquivados eletronicamente em 1/31, então o processamento do site estes só funcionaram de meados de janeiro até o final de abril).

    O que foi feito foi uma combinação de:
    * perguntar a cada desenvolvedor se eles estavam usando algum banco de dados ou outro (esses emails saíam mensalmente ou sempre que os backups demoravam muito).
    * tire o banco de dados offline e veja quem reclama.
    * renomeie o servidor para ver quem reclama.

    Como o chefe de cabelos pontudos só estava disposto a permitir documentação "completa e completa", um wiki foi expressamente proibido, e as reduções de pessoal levaram a um declínio dramático na documentação que atendeu ao padrão.

    Se dependesse de mim, haveria uma página wiki por servidor com nomes de contato para cada banco de dados (e talvez uma breve descrição de para que serve o banco de dados). Qualquer banco de dados não documentado no wiki seria um jogo justo para exclusão.

    Tínhamos um grande cliente financeiro que ainda usava o SQL Server 2000 em 2009, então tivemos que manter uma instância do SQL Server 2000 em execução até que o cliente finalmente mudasse para o SQL Server 2005.

    • 3

  6. Outras duas alternativas são:

    1. Crie gatilhos no banco de dados que notificarão você (ou armazenarão em tabelas) sobre qualquer atividade.

    2. Habilite a auditoria nos bancos de dados.

      • Depende da versão do seu banco de dados.
    • 3

  7. A próxima solução mostra páginas totais, limpas e sujas temporárias em MB para bancos de dados específicos em sua instância (encontradas na Internet e modificadas um pouco):

    SELECT
    (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
    COUNT(*) *8/1024 AS [TotalPages in MB],
    SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) *8/1024 AS [CleanPages in MB],
    SUM(CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) *8/1024 AS [DirtyPages in MB]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY DB_NAME(database_id)

    ou

    select value [DBid],attribute, last_execution_time ,text
from
sys.dm_exec_query_stats
cross apply
sys.dm_exec_plan_attributes(plan_handle)
cross apply
sys.dm_exec_sql_text(plan_handle)
where  attribute = 'dbid' 
order by last_execution_time desc

    ou

    select value [DBid],attribute, last_execution_time ,text
from
sys.dm_exec_query_stats
cross apply
sys.dm_exec_plan_attributes(plan_handle)
cross apply
sys.dm_exec_sql_text(plan_handle)
--where dbid=8
where 
      text like '%idAdministrator%' and
      attribute = 'dbid' 
      and value>= 5 -- dbid >=5 for user databases but include resource database which
                     --you can exclude by its numer I don't remember at the moment
order by last_execution_time desc
    • 2

relate perguntas