Como identificar bancos de dados não utilizados no meu servidor sql

Desculpe, o SQL Server não rastreia o uso do banco de dados. As pessoas têm ideias não tão inteligentes como um gatilho de logon que verifica o banco de dados padrão do login e/ou ORIGINAL_DB_NAME(). Mas isso perde todos os tipos de coisas, como usuários contidos, pessoas que posteriormente emitem um comando USE, consultas entre bancos de dados, etc.

Minha recomendação é o SQL Server Audit se você tiver uma edição apropriada (sua pergunta é leve em detalhes). Roubando do meu próprio post sobre este tópico :

USE master;
GO
CREATE SERVER AUDIT Test_Server_Audit
    TO FILE ( FILEPATH = 'C:\Audits\' );
GO
ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = ON);
GO

USE AdventureWorks;
GO
CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit
    FOR SERVER AUDIT Test_Server_Audit
    ADD (SELECT ON Person.Address BY PUBLIC)
    WITH (STATE = ON);
GO

SELECT *
    FROM Person.Address;
GO

SELECT * 
    FROM fn_get_audit_file('C:\Audits\*', NULL, NULL);
GO

USE AdventureWorks;
GO
ALTER DATABASE AUDIT SPECIFICATION Test_Database_Audit
    WITH (STATE = OFF);
GO
DROP DATABASE AUDIT SPECIFICATION Test_Database_Audit;
GO
USE master;
GO
ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = OFF);
GO
DROP SERVER AUDIT Test_Server_Audit;
GO

Se você não tiver o Enterprise Edition, há uma maneira um pouco menos confiável de fazer isso, que é verificar as estatísticas de uso do índice DMV. Você precisará verificar isso com frequência e potencialmente por um longo tempo para ter certeza de que não está perdendo nada crucial, pois ele contém apenas dados desde a última reinicialização do SQL Server.

SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO

;WITH agg AS
(
    SELECT 
        last_user_seek,
        last_user_scan,
        last_user_lookup,
        last_user_update
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        database_id = DB_ID()
)
SELECT
    last_read = MAX(last_read),
    last_write = MAX(last_write)
FROM
(
    SELECT last_user_seek, NULL FROM agg
    UNION ALL
    SELECT last_user_scan, NULL FROM agg
    UNION ALL
    SELECT last_user_lookup, NULL FROM agg
    UNION ALL
    SELECT NULL, last_user_update FROM agg
) AS x (last_read, last_write);

(Existem maneiras um pouco mais limpas de escrever essa sintaxe, mas sou preguiçoso e peguei emprestado do código que escrevi há mais de seis anos.)

Caveat Emptor

Você pode usar esses métodos para determinar possíveis candidatos dessa maneira, mas ainda não pode ter certeza de que alguém não tentará usar o banco de dados amanhã, no final do próximo ciclo de negócios etc. descartar bancos de dados que não aparecem na auditoria ou no DMV por alguns dias - isso realmente não é um bom indicador de que o banco de dados não está mais sendo usado. Quando você achar que encontrou candidatos para abandonar, por favor, não os abandone. Faça um backup adequado e completo; armazená-lo em outra máquina; em seguida, defina esses bancos de dados para OFFLINE:

ALTER DATABASE whatever SET OFFLINE;

Agora, se o aplicativo de alguém quebrar repentinamente, ou um desenvolvedor perder seu trabalho, ou o CFO não puder executar seu relatório, você pode corrigi-lo com uma única instrução em vez de restaurar um backup ou simplesmente dizer a eles "azar" porque você nem mesmo tem um backup:

ALTER DATABASE whatever SET ONLINE;

Em primeiro lugar, obrigado Aaron, sua solução funcionou!

Além disso, só para acrescentar, também usei a dica do Blog da Kendra Little http://www.brentozar.com/archive/2014/05/4-lightweight-ways-tell-database-used/

As 4 dicas, incluindo a de Aaron, me fizeram descobrir e chegar a uma conclusão sobre os bancos de dados não utilizados, colocaram-nos offline e sem alarmes a partir de agora.

Este post realmente me ajudou, então pensei em acrescentar como apliquei o SQL de Aaron e seu conceito de colocar o banco de dados offline para fazer isso automaticamente.

Meu cenário: temos servidores de desenvolvimento e preparação onde os bancos de dados são implantados, mas se tornam inativos e ficam lá por anos. Em vez de implorar às pessoas para "saírem e derrubarem seus bancos de dados inativos", criei um trabalho para fazer isso de forma intermitente.

Primeiro, crie este proc (talvez no master ou em outro lugar):

CREATE PROCEDURE [dbo].[sp_TakeInactiveDbOffline] 
    @dbname varchar(100)
AS
BEGIN

    SET NOCOUNT ON;

    -- SQL credit: https://dba.stackexchange.com/a/86619/106936
    --SELECT last_user_seek, last_user_scan, last_user_lookup, last_user_update FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID(@dbname)
    if @dbname not in ('model', 'master', 'tempdb', 'msdb') and not exists (SELECT last_user_seek, last_user_scan, last_user_lookup, last_user_update FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID(@dbname)) 
    BEGIN 
    exec ('ALTER DATABASE ' + @dbname + ' SET OFFLINE');
    END

END

em seguida, chame isso, aproveitando o sp_MSforeachdb proc não documentado, talvez em um trabalho SQL mensal:

DECLARE @command varchar(1000) 
SELECT @command = 'exec sp_TakeInactiveDbOffline ?'
EXEC sp_MSforeachdb @command

Minha advertência: este é um ambiente de desenvolvimento para que eu possa ser implacável e isso funciona para mim. Eu não faria isso na produção sem um mergulho mais profundo no dm_db_index_usage_stats DMV ou uma análise mais profunda das outras formas de Kendra de detectar bancos de dados inativos.

Tente isto: se a last_accesscoluna for nula, nenhuma leitura ou gravação ocorreu:

WITH cte AS (
SELECT database_id, dt, op 
FROM sys.dm_db_index_usage_stats
    UNPIVOT (dt for op in (last_user_lookup, last_user_scan, last_user_seek, last_user_update)) b)
SELECT d.name DB, MAX(c.dt) last_access, MAX(i.sqlserver_start_time) sqlserver_start_time, GETUTCDATE() captured
FROM sys.databases d 
LEFT JOIN cte c ON d.database_id=c.database_id
CROSS JOIN sys.dm_os_sys_info i
WHERE d.database_id>4
GROUP BY d.name 
ORDER BY d.name;