Início / dba / Perguntas / 62746
Accepted
Paul
Asked: 2014-04-09 02:57:42 +0800 CST

Encontrar login ou nome de usuário da última pessoa a usar um banco de dados

  • 772

O chefe pediu uma lista de nomes de banco de dados junto com o tamanho e o nome da última pessoa a usar esse banco de dados. Encontrei recursos para quando um banco de dados foi acessado pela última vez, mas por quem. Como eu resolveria isso?

SQL2008R2 sendo usado e até agora eu tenho isso:

exec sp_MSForEachDB '
use ?
select ''?'', (SUM(df.size)*8)/1024 as ''Size (MB)''
from sys.database_files as df
'

É aceitável que o aspecto da última pessoa da resposta seja impreciso ou vago. ou seja, desde que seja alguém que tenha se aproximado vagamente recentemente, está "OK".

sql-server sql-server-2008-r2

1 respostas

  1. Best Answer

    Se o seu servidor estiver ativo por um período de tempo confiável (por exemplo, pelo menos um ciclo de negócios), você poderá saber quando houve alguma leitura ou gravação em qualquer tabela em um banco de dados usando DMVs como sys.dm_db_index_usage_stats.

    Não há realmente uma maneira confiável de saber quem "usou" um banco de dados pela última vez. O SQL Server não registra essas informações, a menos que faça algo substancial (por exemplo, você pode ver que um usuário criou ou derrubou um objeto no rastreamento padrão , mas isso não lhe dirá de forma alguma que ele foi a última pessoa a acessar o banco de dados ou se foi a última vez que acessaram o banco de dados).

    Para os tamanhos de arquivo de banco de dados, você realmente não precisa usar sp_MSforeachdbnada; e você não deve usar esse método de qualquer maneira. Aqui está o porquê:

    Nesse caso, você não precisa percorrer todos os bancos de dados; esta informação é replicada na visão master.sys.master_files.

    SELECT 
  d.name, 
  [Size (MB)] = SUM(mf.size)*8/1024
FROM master.sys.databases AS d
INNER JOIN master.sys.master_files AS mf
ON d.database_id = mf.database_id
GROUP BY d.name;

    Se você tiver que fazer algo por meio de vários bancos de dados que não são suportados por algo no mestre, prefiro fazer esse tipo de técnica do que usar sp_MSforeachdb:

    DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'SELECT ''' + name + ''', (SUM(df.size)*8)/1024 as [Size (MB)]
  from ' + QUOTENAME(name) + '.sys.database_files as df;';
EXEC sp_executesql @sql;

    (Como um aparte, não use 'single quotes'para delimitar um alias, use [square brackets]. O primeiro é obsoleto em algumas formas - veja aqui e aqui - e faz seu alias parecer uma string literal de qualquer maneira.)

    Para a parte "quando", copiando da minha resposta aqui :

    O SQL Server realmente não rastreia o acesso ao banco de dados da maneira que você deseja, pelo menos indo para trás (você pode configurar coisas como rastreamento do lado do servidor, eventos estendidos, auditoria, etc. daqui para frente).

    Há uma coisa aproximada que você pode usar: DMVs que rastreiam o uso do índice e as estatísticas de procedimento/gatilho/consulta. Por exemplo:

    ;WITH d AS
(
  SELECT d = database_id, name FROM sys.databases
  WHERE state = 0 AND database_id BETWEEN 5 AND 32766
),
index_usage(d,lsk,lsc,llk,lupd) AS
(
  SELECT database_id, MAX(last_user_seek), MAX(last_user_scan),
    MAX(last_user_lookup), MAX(last_user_update)
  FROM sys.dm_db_index_usage_stats
  WHERE database_id BETWEEN 5 AND 32766
  GROUP BY database_id
),
proc_stats(d,lproc) AS
(
  SELECT database_id, MAX(last_execution_time) 
    FROM sys.dm_exec_procedure_stats
    WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
),
trig_stats(d,ltrig) AS
(
  SELECT database_id, MAX(last_execution_time)
    FROM sys.dm_exec_trigger_stats
    WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
),
query_stats(d,lquery) AS
(
  SELECT t.[dbid], MAX(s.last_execution_time) 
    FROM sys.dm_exec_query_stats AS s
    CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
    WHERE t.[dbid] BETWEEN 5 AND 32766
    GROUP BY t.[dbid]
)
SELECT d.name,i.lsk,i.lsc,i.llk,i.lupd,p.lproc,t.ltrig,q.lquery
FROM d LEFT OUTER JOIN index_usage AS i ON d.d = i.d
LEFT OUTER JOIN proc_stats  AS p ON d.d = p.d
LEFT OUTER JOIN trig_stats  AS t ON d.d = t.d
LEFT OUTER JOIN query_stats AS q ON d.d = q.d;

    Observe que essas estatísticas não são totalmente confiáveis, pois você pode não ter nenhum procedimento armazenado e as consultas encontradas sys.dm_exec_query_statspodem fazer referência a mais de um banco de dados e podem nunca refletir aquele com o qual você está preocupado.

    Além disso, eles são redefinidos quando o SQL Server é reiniciado, ou um banco de dados é desanexado / anexado ou restaurado, ou quando um banco de dados é fechado automaticamente e também pode depender, em alguns casos, dos planos que ainda estão no cache (o que outro banco de dados poderia assumir completamente em poucos minutos). Portanto, se você está olhando para o passado, a menos que saiba que nenhuma dessas coisas aconteceu durante todo um ciclo de negócios, eu não confiaria apenas nesses números para determinar se um banco de dados é usado (também pode haver processos automatizados que estão fazendo um banco de dados parece atual, mesmo que você não se importe que esses processos automatizados falhem quando você remover o banco de dados).

    Outra observação é que determinados acessos ao índice podem não ser rastreados nas exibições de uso do índice; por exemplo, no SQL Server 2014, que adiciona tabelas com otimização de memória, a atividade nesses índices de hash não é capturada dessa maneira (e as exibições em que você acha que a atividade seria capturada, como sys.dm_db_xtp_hash_index_stats, não incluem nenhuma coluna de data/hora). Se você estiver usando SQL Server 2014 e OLTP na memória ("Hekaton"), convém adicionar alguma pesquisa para cobrir esses objetos (no caso de serem os únicos referenciados em um banco de dados).

    E mais uma observação é que as consultas capturadas por sys.dm_exec_query_statspodem ser falsos positivos. Por exemplo, se seu banco de dados tiver filestream/ filetable, você verá essas consultas sendo executadas pelo sistema ocasionalmente:

    select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
from [database].[sys].[filetable_updates_<some_id>] with (readpast) order by table_id

    Portanto, você pode querer adicionar filtragem adicional à consulta acima para filtrá-la (desde que o filtro não filtre acidentalmente as consultas com as quais você se importa). Esta é provavelmente uma adição segura a essa tabela derivada:

    AND t.[text] NOT LIKE N'%oplsn_fseqno%'

    No final, a coisa mais segura a fazer em um ambiente de desenvolvimento é colocar os bancos de dados sobre os quais você não tem certeza offline por uma semana. Se ninguém reclamar, apoie-os e abandone-os. Se demorar mais de uma semana para alguém perceber que está faltando, você sempre pode restaurá-lo (lá ou em outro lugar).

    Eu também escrevi um pouco sobre isso:

    • 4

relate perguntas