Find Orphaned databases in SQL Server

Do you ever wonder if there are any databases in your environment that may just be there but not being used?

I needed to make a comprehensive list of such databases in order to clean up old databases and prep old/existing environment and migrate and consolidate them into a new sql server infrastructure.

So I wrote this DMV query to obtain when a database was last accessed for a user query as well as those that were left OFFLINE but never removed.

I executed this against CMS / Multi Server query an whoa within few seconds I had the complete list or rather a preliminary list for my intended purpose.

Couple things to note first:

1. I only tested and ran this against SQL versions 2008 and up.

2. It uses a global temp table and removes when done

Hope you find this use and please feel free to leave comments/feedback/suggestions for further improvement.

/*

By an orphan database, I mean a database that no one
is using anymore, essentially the users and apps

using this database either migrated to a different, 

possibly upgraded copy of this database or 

simply retired. 

Such databases can be a good 
candidate for taking them offline and
decommission.

*/


SET nocount ON
SET TRANSACTION isolation level READ uncommitted

USE master

go

IF Object_id('tempdb..##t_dba_db_last_access_stats') IS NOT NULL
  DROP TABLE ##t_dba_db_last_access_stats

go

DECLARE @db_activity_since INT
SET @db_activity_since = 90 -- days

IF Object_id('tempdb..##t_dba_db_last_access_stats') IS NULL
  CREATE TABLE ##t_dba_db_last_access_stats
    (
       db_name          NVARCHAR(256),
       db_status        NVARCHAR(256),
       last_user_seek   DATETIME,
       last_user_scan   DATETIME,
       last_user_lookup DATETIME,
       last_user_update DATETIME
    )

go

EXEC Sp_msforeachdb
  ' use [?] if db_id() > 4 begin insert into ##t_dba_db_last_access_stats SELECT   DB_NAME() db_name,   CAST(DATABASEPROPERTYEX(DB_NAME(), ''Updateability'') AS NVARCHAR(256)) db_status,   last_user_seek = MAX(last_user_seek),   last_user_scan = MAX(last_user_scan),   last_user_lookup = MAX(last_user_lookup),   last_user_update = MAX(last_user_update) FROM sys.dm_db_index_usage_stats AS i WHERE i.database_id = DB_ID() AND OBJECTPROPERTY(i.object_id, ''ismsshipped'') != 1 end '

SELECT Getdate()                [current_time],
       @@servername             sql_instance,
       (SELECT crdate FROM   sysdatabases WHERE  NAME = 'tempdb') sql_instance_up_since,
       db_name,
       db_status,
       Max(last_accessed_date)  last_accessed
FROM   ##t_dba_db_last_access_stats a
       UNPIVOT ( last_accessed_date
               FOR last_accessed_for IN (last_user_seek,
                                         last_user_scan,
                                         last_user_lookup,
                                         last_user_update ) ) AS last_accessed
GROUP  BY db_name, db_status
UNION ALL                       -- Lets also grab list of OFFLINE databases
SELECT Getdate()  [current_time],
       @@servername sql_instance,
       (SELECT crdate 
               FROM   sysdatabases 
               WHERE  NAME = 'tempdb') sql_instance_up_since,
       NAME  db_name,
       Cast(Databasepropertyex(NAME, 'status') AS VARCHAR(50)) db_status,
       NULL last_accessed

FROM   sysdatabases
WHERE  NAME NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
       AND Cast(Databasepropertyex(NAME, 'status') AS VARCHAR(50)) = 'OFFLINE'
ORDER  BY db_name

IF Object_id('##t_dba_db_last_access_stats') IS NOT NULL
  DROP TABLE ##t_dba_db_last_access_stats

go

Download this script from GitHub:

SQL Server Orphan Databases