none
Como saber se um banco de dados foi recentemente acessado ?

    Pertanyaan

  • Prezados,
    Tenho alguns servidores SQL SERVER 2005 e o pessoal de desenvolvimento gostaria de poder desativar alguns sistemas (em caso de desuso), mas antes gostaria de saber se a respectiva base de dados de cada sistema foi recentemente acessada pelo usuário da base.

    Daí a minha dúvida : existe alguma forma, no SQL SERVER 2005 (através de tabelas de sistemas, dmv etc), de se verificar se uma base de dados foi acessada (recentemente ou quando foi) pelo seu respectivo usuário  ?

    Agradeço antecipadamente qualquer colaboração.

    José Luiz

    15 Desember 2010 0:40

Semua Balasan

  • Boa noite José Luiz,

    Verifique os arquivos de trace default do SQL Server. Nele tem alguns dados interessantes como histórico de comandos DDL na instancia. O arquivo não diz claramente a data e hora que o usuário se conectou, porém tem dados de atualização de estatisticas, criaçao de objetos no tempdb e etc, que são constantes em uma base de produção.

    Os arquivos se encontram por padrão no diretorio "C:\Arquivos de programas\Microsoft SQL Server\MSSQL.1\MSSQL\LOG" e se chamam "LOG_??.trc" onde ?? é um contador que é reciclado a cada reinicio do serviço da instancia.

    Segue um exemplo da leitura de um arquivo trace com a funçao fn_trace_gettable:

    select

     

    *

    from

     

    fn_trace_gettable('C:\Arquivos de programas\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_60.trc',default)

    Caso não solucione seu problema, use o SQL Server Profiler para auditar eventos de logon de usuários no SQL Server.

    Atenciosamente,


    MCTS - SQL Server 2008, Implementation and Maintenance
    15 Desember 2010 1:58
  • José Luiz,

    No SQL 2005 as únicas opçoes que vejo para auditoria são o Server Trace Side ou alguma ferramenta de terceiro...

    O SQL Server 2008 possui o SQL Audit que faz o que vc necessita...
    http://www.msit.com.br/PublicacaoDetalhes.aspx?id=47&type=a

    Att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    15 Desember 2010 11:00
    Moderator
  • Talles e Marcelo,
    Muito obrigado pelas informações.

    Marcelo,
    O Server Trace Side seriam os traces default a que o Talles se referiu ?

    Agradeço a atenção em me responder. As informações já serão de grande valia para eu fazer uma pesquisa aqui.

    15 Desember 2010 12:26
  • José..

    Sim, ma o Server Trace Side é o modo de script, o que onera um pouco menos o servidor em relação ao Trace

    att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    15 Desember 2010 12:53
    Moderator
  • Talles e Marcelo,
    Muito obrigado, mais uma vez, pelas informações.
    15 Desember 2010 21:39
  • Talles,

    Corrija-me se eu estiver errado : testei a query que você me mandou e pude verificar que existe sim informação de data, na coluna "StartTime". Essa não seria a data e hora que o usuário (especificado na coluna "LoginName") teria acessado a sua respectiva base de dados ? Se for isso, é exatamente o que eu procuro. Quero saber se o usuário de uma base de dados acessou esta base.

    Ainda consegui ver nessa query também, que achei bem valiosa, a partir de qual computador foi feito o acesso (acho que isso está ilustrado na coluna "HostName").

    Será que estou avaliando erradamente a saída desta query que você me indicou ?

     

    16 Desember 2010 19:32
  • José Luiz,

    A coluna StartTime retorna a hora do inicio de uma atividade DDL no servidor. Porém não podemos dizer que é a hora que o usuário se logou no sistema. Ela pode ser o StartTime de uma atualização de estatisticas ou da criação de algum objeto no database TEMPDB ou em qualquer outro database. O que tem de ficar claro é que esse procedimento não irá te retornar exatamente o que você quer, que no caso é a hora que o usuário logou no SQL Server, sim alterações DDL na instancia que são frequentes em sistemas de produção.

    Quando aos demais dados da Trace, eles são sim muito ricos. A coluna HostName retorna a estação pela qual o usuário se conectou ao popular a trace.

    Esse link tem detalhes sobre as possiveis colunas em uma trace: http://msdn.microsoft.com/pt-br/library/ms190762.aspx

    Se o seu servidor tem o SQL Server Profiler instalado de uma verificada nele pois essa trace default do SQL Server (A Partir do 2005) é apenas uma amostra de muitos recursos de monitoramento com traces existentes no SQL Server. Voce pode criar sua propria trace para auditar os horarios de logon, comandos executados e uma infinidade de dados estatisticos.

    Atenciosamente,


    MCTS - SQL Server 2008, Implementation and Maintenance
    16 Desember 2010 23:29
  • Talles,

    Deixa eu esclarecer um ponto que talvez eu tenha explicado mal sobre minha real necessidade : na verdade, eu não preciso da data e hora exatas que uma determinada base de dados foi acessada. Na verdade, o que eu preciso saber é se a base de dados ainda continua sendo acessada pelo seu usuário, via o sistema (que no meu caso, são todos desenvolvidos em platafoma web e acessados via browser).

    Um exemplo : hoje estamos no dia 17/12/2010, então se eu descobrir, através de uma pesquisa no trace (com a query que você me passou), que uma determinada base de dados foi acessada ontem (dia 16/12/2010) então, isso quer dizer que a base continua em atividade. Entendeu ? A hora pra mim é só um detalhe, sendo até muito mais importante a data.

    Comentei sobre a coluna "HostName", porque isso me indica até mesmo se o acesso foi feito via o servidor Web de aplicações (o proprio sistema) ou através da estação cliente do desenvolvedor, via SSMS, entendeu ? No meu caso, isso é importante saber.

    Creio que só esse trace default já é bastante rico de informações pro meu caso. Mas uma pergunta : se eu criar meu próprio trace, eu terei que deixar ligado o Profiler, captando os dados, durante dias. Isso não degradaria a performance do servidor ?

    Além disso, pesquisando em outros sites e blogs, tive essa outra indicação de solução e que me parece bem interessante também. Trata-se de mudar o nível de auditoria do SQL SERVER 2005 que, por default, "loga" apenas os "Login failure". Mudando para "logar" os "Login succeeded" e os "Login failure". Depois é só fazer uma query nos logs, filtrando por "login succeeded" pra ver se um determinado usuário "logou" na sua base e quando...

    Como forma de enriquecer o nosso post, veja a solução abaixo (ela poderá ser útil pra outras pessoas) :

     

    • Alterar o nível de auditoria do log do SQL SERVER (pode ser também feito através do caminho "Management Studio -> Botao direito no servidor -> Propriedades -> Security -> Login auditing -> Both failed and successful logins --> OK".

    USE [master]
    GO
    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3
    GO

    -- TEM QUE REINICIAR O SERVIÇO DO SQL APÓS ISSO

    Após feito esta alteração você pode ler os arquivos do LOG e verificar quando foi o último acesso realizado com sucesso. Segue script:

    if object_id('tempdb.dbo.#SQLLog') is not null drop table #SQLLog
    create table #SQLLog
    (
    iden int identity(1,1),
    dataLog datetime,
    processInfo varchar(100),
    linha varchar(8000)
    )

    --Lê todas as linhas do log, passando filtro 'Login succeeded'.
    insert into #SQLLog
    exec sp_readerrorlog 0, 1, 'Login succeeded'

    insert into #SQLLog
    exec sp_readerrorlog 1, 1, 'Login succeeded'

    insert into #SQLLog
    exec sp_readerrorlog 2, 1, 'Login succeeded'

    insert into #SQLLog
    exec sp_readerrorlog 3, 1, 'Login succeeded'

    insert into #SQLLog
    exec sp_readerrorlog 4, 1, 'Login succeeded'

    insert into #SQLLog
    exec sp_readerrorlog 5, 1, 'Login succeeded'

    insert into #SQLLog
    exec sp_readerrorlog 6, 1, 'Login succeeded'

    select * from #SQLLog
    order by dataLog desc


    Bem, creio que essa seja uma boa solução também. Meu receio é só que os logs cresça absurdamente, pois com isso o SQL vai logar absolutamente todo e qualquer evento e,além disso, como capturará muitos eventos, temo que a performance do servidor de produção degrade.


    O que você acha dessa solução, em termos de performance, Talles ?

    Obrigado pela atenção.

    17 Desember 2010 13:33
  • José Luiz,

    Ambas as soluções são interessantes. Não há problemas de performance de utilizar uma das duas, tanto Profiler quanto o Audit Level tendo em vista que estamos trabalhando com bases que estão sendo monitoradas de acordo com a não conexão com as mesmas.

    Eu utilizaria o Profiler por ele ter mais opções de configuração, como programação de tempo para o fim da Trace, campos condicionais e também por não exigir a reinicialização do serviço entre outros.

    Assim que os dados forem capturados, termine a trace ou volte o Audit Level para seu valor padrão.

    Atenciosamente,


    MCTS - SQL Server 2008, Implementation and Maintenance
    18 Desember 2010 6:22