Identify When your SQL Database Was Last Used

Database servers are used for storing vast amounts of data. It is essential to know when any user last used the database. This blog will cover a way to find out when users or applications last accessed it for executing their queries.

Suppose you have got many databases in a SQL instance? You are not sure about whether anyone uses the specific SQL databases? This blog will help you to identify the following.

• When was the last time SQL Server performed the table Scan on any tables of SQL Database?
• When was the last time SQL Server performed the table Seek on any tables of SQL Database?
• When was the last time SQL Server performed the insert, update, or delete on any tables of SQL Database?

We can use the DMV sys.dm_db_index_usage_stats to give different index operations, and the time each operation was last performed. It works on Azure SQL Database, Azure SQL Managed Instance, and on-premise SQL Server.

Declare @last_boot datetime
Set @last_boot = (select [sqlserver_start_time] from sys.dm_os_sys_info)
 
select @@servername as [ServerName],'last_boot' = @last_boot, 'days_since_last_boot' = datediff(d, @last_boot, getdate())
 
if object_id('tempdb..##Table_usage_data') is not null
drop table ##Table_usage_data
create table ##Table_usage_data (ID int identity (1,1), [database] varchar(255),[last_user_seek] datetime,[last_user_scan] datetime,  [last_update] datetime)
 
declare @get_last_user_activity_timestamp varchar(max)
set @get_last_user_activity_timestamp = ''
select @get_last_user_activity_timestamp = @get_last_user_activity_timestamp +
'select db_name([database_id]), max(last_user_seek), max(last_user_scan), max([last_user_update]) from sys.dm_db_index_usage_stats where db_name([database_id]) = ''' + [name] + ''' group by [database_id];' + char(10)
from sys.databases where [database_id] > 4 and [state_desc] = 'online'
 
insert into ##Table_usage_data ([database],[last_user_seek],[last_user_scan],[last_update])
exec (@get_last_user_activity_timestamp)
 
select
[database], last_user_scan,last_user_seek,last_update
from
##Table_usage_data

Note: This query returns data since the last SQL Server restart.

As shown below, there have been no user inserts, updates, or delete operations in my demo environment since the last SQL Server restart.

I connected to SQL Database and executed an Insert statement, and it updates the last_update column as shown below.

You can analyze databases based on the last user scan, seek, and update for cleanups.