Is a database being used?

Kinda tricky to find out sometimes, easiest is just to ask the application owner, but if that’s not an option below are a couple of ways to try to find out if it is being used.

Check with index stats

WITH LastActivity (ObjectID, LastAction) AS 
(
SELECT object_id AS TableName,
last_user_seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION 
SELECT object_id AS TableName,
last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,
last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id) AS TableName,
MAX(la.LastAction) as LastSelect
FROM sys.objects so
LEFT
JOIN LastActivity la
on so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)

 Processes or plans being updated?

Run each query below and see if it is clear whether a database is being used or not.

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

SELECT @@ServerName AS SERVER
,NAME
,login_time
,last_batch
,getdate() AS DATE
,STATUS
,hostname
,program_name
,nt_username
,loginame
FROM sys.databases d
LEFT JOIN sysprocesses sp ON d.database_id = sp.dbid
WHERE database_id NOT BETWEEN 0 AND 4 
AND loginame IS NOT NULL

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name like 'Transactions/sec%'
and instance_name like 'MSSQL%';


SELECT
SUBSTRING(tx.[text],
(qs.statement_start_offset / 2) + 1,
(CASE WHEN qs.statement_end_offset =-1 THEN DATALENGTH(tx.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)
/ 2 + 1) AS QueryText,
case when pl.query_plan LIKE '%<MissingIndexes>%' then 1 else 0 end as [Missing Indexes?],
qs.execution_count,
qs.total_worker_time/execution_count AS avg_cpu_time,
qs.total_worker_time AS total_cpu_time,
qs.total_logical_reads/execution_count AS avg_logical_reads,
qs.total_logical_reads,
qs.creation_time AS [plan creation time],
qs.last_execution_time [last execution time],
CAST(pl.query_plan AS XML) AS sqlplan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS pl
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS tx
WHERE pl.query_plan LIKE '%[DATABASENAME YOUR LOOKING FOR]%'
ORDER BY execution_count DESC OPTION (RECOMPILE);

Last login time per login

SELECT DB_NAME(r.database_id),max(login_time),login_name,max(last_request_end_time)
FROM sys.dm_exec_requests AS R JOIN sys.dm_exec_sessions S ON R.session_id=S.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS ST
WHERE plan_handle IS NOT NULL
GROUP BY DB_NAME(r.database_id),login_name

And if you still have no clue whether it is used or not just shut down the instance…. JUST KIDDING!!!!!! ­čÖé

 

Leave a Reply

Your email address will not be published. Required fields are marked *