CODEWITHSUNDEEP
sql-server-2005
Senior Systems Engineer
Senior Systems Engineer

Reputation: 1139

Finding unused SQL Server databases

Is there any way to find unused SQL Server 2005 databases ?

I'm in the process of upgrading and migrating my server into SQL Server 2008 x64 in new server instance from 2005 32 bit.

Upvotes: 2

Views: 9697

Answers (5)

nithin
nithin

Reputation: 11

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  'ServerName'= @@SERVERNAME,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 JOINsys.dm_os_sys_info i
WHERE d.database_id>4
GROUP BY   d.name 
ORDER BY d.name;

Upvotes: 1

Francesco Mantovani
Francesco Mantovani

Reputation: 12227

Try this: if the last_access column is null then no reads or writes have occurred:

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;

Upvotes: 1

Vivek Jagga
Vivek Jagga

Reputation: 502

With SQL Server 2005, you can use the dynamic management view sys.dm_db_index_usage_stats. The name says "index" but that's a little misleading - every table has an entry in here, even if it doesn't have any indexes. Here's a useful query from SQL Magazine:

SELECT  
  t.name AS 'Table',  
  SUM(i.user_seeks + i.user_scans + i.user_lookups)  
    AS 'Total accesses', 
  SUM(i.user_seeks) AS 'Seeks', 
  SUM(i.user_scans) AS 'Scans', 
  SUM(i.user_lookups) AS 'Lookups' 
FROM  
  sys.dm_db_index_usage_stats i RIGHT OUTER JOIN  
    sys.tables t ON (t.object_id = i.object_id) 
GROUP BY  
  i.object_id,  
  t.name ORDER BY [Total accesses] DESC

Here's the original article:

http://www.sqlmag.com/Article/ArticleID/53878/sql_server_53878.html

Keep in mind that these usage statistics reset when SQL Server restarts.

Upvotes: 3

devio
devio

Reputation: 37215

Combine any of these methods to find out which databases are still in use

  • listing connections

    select * from sys.dm_exec_connections

  • listing active processes

    select * from sys.sysprocesses

  • listing execution statistics

    select DB_NAME(database_id),  
    max(isnull(isnull(last_user_scan, last_user_update), 
        isnull(last_user_seek, last_system_scan))) 
from sys.dm_db_index_usage_stats 
group by DB_NAME(database_id)

  • storing LOGON timestamps

create a LOGON TRIGGER and insert the EVENTDATA contents into a table for later querying, examples here, here

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453243

Not a fool proof way. A couple of things that spring to mind are.

See which databases have few pages in the buffer pool

select db.name, COUNT(*) As page_count
from sys.databases db LEFT JOIN sys.dm_os_buffer_descriptors bd ON db.database_id = bd.database_id
group by db.database_id, db.name
order by page_count

Or look at the index usage stats for each database

SELECT db.name, 
(SELECT MAX(T) AS last_access FROM (SELECT MAX(last_user_lookup) AS T UNION ALL SELECT MAX(last_user_seek) UNION ALL SELECT MAX(last_user_scan) UNION ALL SELECT MAX(last_user_update)) d) last_access
FROM sys.databases db 
LEFT JOIN sys.dm_db_index_usage_stats iu ON db.database_id = iu.database_id
GROUP BY db.database_id, db.name
ORDER BY last_access

You could also use logon triggers to log access ongoing for a certain period just to be sure that nothing seems to be accessing "dead" databases before switching them offline.

Upvotes: 4

Related Questions