4

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.

  • 3
    If in doubt, just take the database offline, and if no one complains about it for a week - nobody is using it .... – marc_s Dec 19 '14 at 14:24
7

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;
  • I have used this from Jonathan Kehayias that utilizes Extended Events: Tracking SQL Server Database Usage. It works fairly well as a guide. – user507 Dec 19 '14 at 20:07
  • @Shawn yeah, that is another way. The disclaimer that system processes probably acquire resource locks from various tasks - even while masquerading as a user process - should probably be in 80pt. – Aaron Bertrand Dec 19 '14 at 20:10
  • Yeah, things like SSMS refreshing the drop list for database names can make it increment the value for the Xevent session. – user507 Dec 19 '14 at 20:13
  • Shawn@ i think Jonathan article applies to sql2008 and above while my server would be as of now is sql2005 – KASQLDBA Dec 22 '14 at 12:26
  • @Kapil If you are not using a current version of SQL Server but rather one that is 10 years old and out of support, you should tag your question that way so people don't waste time working on solutions you can't use. Our time is valuable too. – Aaron Bertrand Dec 22 '14 at 13:06
2

First of all Thanks Aaron, You're solution worked!

Moreover just to add, i also used the tip from Kendra Little's Blog http://www.brentozar.com/archive/2014/05/4-lightweight-ways-tell-database-used/

The 4 tips including Aaron's made me figure out and come to a conclusion for the unused databases, took them offline and no alarms as of now.

0

This post really helped me out so I thought I would add how I applied Aaron's SQL and his concept of taking the database offline to do this automatically.

My scenario: we have development and staging servers where databases get rolled out but become inactive and sit there for years. Rather than beg folks to "go out and take down your inactive databases", I created a job to do this intermittently.

First, create this proc (perhaps in master, or elsewhere):

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

then call this, leveraging the undocumented sp_MSforeachdb proc, perhaps in a monthly SQL job:

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

My Caveat: this is a development environment so I can be ruthless and this works for me. I would not do this on production without a deeper dive into the dm_db_index_usage_stats DMV or a deeper look at Kendra's other ways of detecting inactive databases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.