Sql-server – How to identify unused databases on the sql server

sql server

I am looking for the best practices or analysis someone would have followed to identify the unused or inactive databases on my SQL Server.

I went through many articles, which say possibly to go for user connections and activating traces to find the DB usage, but implementing those created more confusion.

Kindly help me in understanding if there is a better and safer way I can take my databases offline once identified.

Best Answer

Sorry, SQL Server does not track database use. People have come up with not-so-clever ideas like a logon trigger that checks the default database of the login and/or ORIGINAL_DB_NAME(). But this misses all kinds of things like contained users, people who later issue a USE command, cross-database queries, etc.

My recommendation is SQL Server Audit if you have an appropriate edition (your question is light on details). Stealing from my own post on this topic:

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

If you don't have Enterprise Edition there is a slightly less reliable way to do this, and that is to check the index usage statistics DMV. You'll need to check this often and potentially for a long time to be sure you're not missing anything crucial, as it only contains data since the last restart of 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);

(There are slightly cleaner ways to write that syntax, but I'm lazy and borrowing from code I wrote more than six years ago.)

Caveat Emptor

You can use these methods to determine potential candidates this way, but you still can't be sure that someone won't try to use the database tomorrow, at the end of the next business cycle, etc. So please don't just rush to drop databases that don't show up in the audit or the DMV for a few days - this really isn't a good indicator that the database is no longer being used. When you think you've found candidates to drop, please don't drop them. Take a proper, full backup; store it on another machine; then set those databases to OFFLINE:

ALTER DATABASE whatever SET OFFLINE;

Now, if someone's application suddenly breaks, or a developer lost his work, or the CFO can't run her report, you can fix it with a single statement instead of having to restore a backup or simply telling them "tough luck" because you don't even have a backup:

ALTER DATABASE whatever SET ONLINE;
Related Question