Find unused databases

Question

Hi Experts,

I know this is a question asked before. We need to figure out the databases in our servers that are not being used. We could check from audit logs, profiler trace and some DMV's. But is there any useful tool or any easy method by which we could get it for all the databases (100s of them) for all servers. Please provide your suggestions ?

---

MM

Answer 1

Here's a great blog entry from Aaron Bertrand showing when the database was last accessed. 

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx

Also, a good past thread on the topic:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7a2e1b2c-9722-4e7b-90ba-608b5ac73d16/how-to-track-unused-databases

Thanks,
Sam Lester (MSFT)

---

http://blogs.msdn.com/b/samlester

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 2

Adding to Samuel Lester's reply,

If you are interested you may vote for the below connect:

https://connect.microsoft.com/SQLServer/feedback/details/659846/database-last-accessed-time

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 3

Also an extra addition to both Samuel's and Latheesh's replies...

With a slight amendment to Aaron's scripts you can easily run this for all databases on your server and get the results in one table as follows:

declare @results table
(
	dbName varchar(1000),
	lastRead datetime,
	lastWrite datetime
);
insert into @results
exec sp_MSforeachdb '
use ?;

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 ''?'' dbName,
    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);'

select dbName, lastRead, lastWrite
from @results

And if you wish to run this over multiple servers then the best way to achieve that would be using Registered Servers which is very easy to set up.

There's a good article showing you how this is done here:

http://www.mssqltips.com/sqlservertip/1767/execute-sql-server-query-on-multiple-servers-at-the-same-time/

Answer 4

I don't think there is any 100% false proof way of identifying the unused database.  first link posted by Samule will work only if you are using Enterprise version of SQL Server . 

If the database is in read only mode then relaying on index_usage_stats will not help either 

If it me, i would create a server login trigger and monitor for an extended period before decommissioning the DB

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 5

I agree that there's no fail-proof way to determine this especially considering that legacy DBs may go months or years without needing to be accessed, but then may be needed for some particular business need. 

In our software testing labs, we'd try to tackle this same problem with unused machines/hardware.  The best way to accomplish it there was to power them all off and see who came yelling.  This strategy likely doesn't apply to production DB systems.  :)

Thanks,
Sam Lester (MSFT)

---

http://blogs.msdn.com/b/samlester

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 6

Agreed on this not being a fool-proof option...  however, when having 100s of databases on multiple servers it would more than help to use the answers above to remove all the active databases and therefore leave a MUCH smaller subset to investigate by other means (triggers etc).

Also, I took my code from Aaron's website and it certainly isn't Enterprise only as I tested my edited version on my SQL Servers here, none of which are Enterprise.

Answer 7

Agreed on this not being a fool-proof option...  however, when having 100s of databases on multiple servers it would more than help to use the answers above to remove all the active databases and therefore leave a MUCH smaller subset to investigate by other means (triggers etc).

Also, I took my code from Aaron's website and it certainly isn't Enterprise only as I tested my edited version on my SQL Servers here, none of which are Enterprise.

Kevin

First method, which is server audit.. its an enterprise edition only feature.. please read the link bellow( The very first line)

http://technet.microsoft.com/en-us/library/cc280386(v=sql.100).aspx 

If you are using developer edition on you local pc then , yes , it will work. but developer edition is not intended to use on  production environment 

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 8

After running the above query, some databases returned NULL values under LastRead and LastWrite columns. Does it imply something? Other databases on the same instance returns proper values.

Could you please share your comments.

---

MM