check db usage

 

 

 


Script to check DB last access

 

SELECT name, last_access =(select X1= max(LA.xx)
from ( select xx =
max(last_user_seek)
where max(last_user_seek)is not null
union all
select xx = max(last_user_scan)
where max(last_user_scan)is not null
union all
select xx = max(last_user_lookup)
where max(last_user_lookup) is not null
union all
select xx =max(last_user_update)
where max(last_user_update) is not null) LA)
FROM master.dbo.sysdatabases sd
left outer join sys.dm_db_index_usage_stats s
on sd.dbid= s.database_id
group by sd.name

 


Last reboot time

 

SELECT
create_date
FROM
sys.databases
WHERE name = 'tempdb'

 


Current DB connection

 

SELECT @@ServerName AS server
 ,NAME AS dbname
 ,COUNT(STATUS) AS number_of_connections
 ,GETDATE() AS timestamp
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME