CODEWITHSUNDEEP
sql-server
Julius A
Julius A

Reputation: 39622

SQL Server 2000/5 - Script to get list of Active or Inactive databases (except system ones)

I need help to script all the databases on an SQL 2005 server and possibly view their activity (specifically the date of last activity). I need to drop all databases that are NOT active on the server.

Upvotes: 0

Views: 5354

Answers (4)

devio
devio

Reputation: 37215

The query on active databases can be refined as follows

SELECT  name
FROM    master..sysdatabases
WHERE   DATABASEPROPERTY(name, 'IsOffline') = 0 
AND     DATABASEPROPERTY(name, 'IsReadOnly') = 0
AND     DATABASEPROPERTY(name, 'IsDetached') = 0
AND     DATALENGTH(sid) > 1
ORDER BY name

Upvotes: 0

Brent Ozar
Brent Ozar

Reputation: 13274

Here's a trick: use the SQL Server dynamic management view to grab data about which indexes have been used.

This query will give you the most recently used index in each database. If the indexes aren't being used, then the data probably isn't being used either.

SELECT db.[name] AS DatabaseName
, (SELECT TOP 1 last_user_seek FROM sys.dm_db_index_usage_stats ix1 WHERE ix1.database_id = db.database_id ORDER BY last_user_seek DESC) AS last_user_seek
, (SELECT TOP 1 last_user_scan FROM sys.dm_db_index_usage_stats ix1 WHERE ix1.database_id = db.database_id ORDER BY last_user_scan DESC) AS last_user_scan
, (SELECT TOP 1 last_user_lookup FROM sys.dm_db_index_usage_stats ix1 WHERE ix1.database_id = db.database_id ORDER BY last_user_lookup DESC) AS last_user_lookup
, (SELECT TOP 1 last_user_update FROM sys.dm_db_index_usage_stats ix1 WHERE ix1.database_id = db.database_id ORDER BY last_user_update DESC) AS last_user_update
FROM sys.databases db
ORDER BY db.[name]

One warning: if you have maintenance plans set up to automatically reindex your databases regularly, or a third party defrag program, then these dates may be artificially recent.

Upvotes: 1

Mark Allison
Mark Allison

Reputation: 7228

The best way to do this is to run a very lightweight profiler trace on your server and log just "Audit Login" events. Leave this running for a week/month/whatever and then have a look at your trace log to see which databases are currently being used. If you run a query, then you will only see the current databases being used, not what has been used in the last week/month.

Upvotes: 2

cmsjr
cmsjr

Reputation: 59185

You could start with 

select  name from sysdatabases
where sid <> 0x01

and then execute sp_helpdb for each database name returned

Upvotes: 0

Related Questions