inactive databases

On our company's SQL server, there are a bunch of 开发者_Python百科databases that don't appear to be used. Is there a way to determine the last time someone used a particular db, connected to it, or ran a query against it?

---

I don't think there's a per-database statistic for last used date.

What you can do is attach SQL Server profiler to the database, with a filter on database name. You can leave this running for a few weeks and see if there's any activity.

Another option is to check Database Properties -> Reports -> Standard Reports -> Index Usage Statistics. If the last use of any index is very old, that's a good indication the database is not being used.

Alternatively, have a look at SQL Server Auditing. I haven't used it myself, but it looks like it might suit your needs.

---

Here is actually a query I use. It tells you what DB was used last since its last restart. It gives a date it was last used and days since it was last used. This should be what you need. It is dynamic and will work on any server because is uses system views. Run this on the master DB.

DECLARE @RESTART DATETIME

SELECT @RESTART = login_time 
FROM sysprocesses 
WHERE spid = 1 

SELECT @@SERVERNAME AS SERVER_NAME
,DB_NAME(db.database_id) AS 'DATABASE'
, db.state_desc AS 'State'
, @RESTART AS 'SYSTEM_RESTART'
, MAX(coalesce(us.last_user_seek, us.last_user_scan,     us.last_user_lookup,@RESTART)) AS 'LAST_USE'
,CASE 
WHEN DATEDIFF(DAY,@RESTART, MAX(coalesce(us.last_user_seek, us.last_user_scan, us.last_user_lookup,@RESTART))) = 0 
THEN 'Prior to restart ' + CONVERT(VARCHAR(5),DATEDIFF(DAY,MAX(coalesce(us.last_user_seek, us.last_user_scan, us.last_user_lookup,@RESTART)), CURRENT_TIMESTAMP)) +' days ago'
ELSE CONVERT(VARCHAR(5),DATEDIFF(DAY,MAX(coalesce(us.last_user_seek, us.last_user_scan, us.last_user_lookup,@RESTART)), CURRENT_TIMESTAMP))
END AS 'DAYS_SINCE_LAST_USE'
FROM sys.databases db
LEFT OUTER JOIN sys.dm_db_index_usage_stats us ON db.database_id = us.database_id
/* IF you want to exclude offlinbe Dbs in your output
   then remove the comment '--AND STATE != 6' below. 
*/
WHERE db.database_id > 4 --AND STATE != 6
GROUP BY DB_NAME(db.database_id), db.state_desc 
ORDER BY DB_NAME(db.database_id)