top of page

Identify When your SQL Database Was Last Used - SQL Server

Updated: Jan 28, 2022

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.


DMV sys.dm_db_index_usage_stats result

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

Updated SQL Database usage stats

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



16,005 views4 comments

4 Comments


This is an invaluable trick if there are indexes. One point to mention, technically the output of those DMVs could be reset even without a restart, if SQL faces memory pressure. But if you have multiple indexes in the DMV showing similar stats, it adds to the evidence.

Like

Unknown member
Feb 07, 2022

Hint: An easy (GUI) way to find out when the SQL Server service was started without using / writing a script (that you may forgot until you need it the next time), is to right click onto the tempdb and open it's properties. Since the tempdb will recreated each time the SQL server starts, it shows you the start time as creation date of the tempdb.

Like

Unknown member
Jan 31, 2022

Hi Volodymyr: There are many different approaches available to identify the last used state of an database. It might depends on the environment, and use case which one to use. Every approach has its pros and cos and the DMV method is simple to start the investigation.

Like

Unknown member
Jan 30, 2022

In most cases there is some scheduled job related to each db (in my case it's a lot of DataService jobs), which select/update data periodically. So this method will be almost useless in such cases. The better approach is to configure dedicated XE event to collect loging info from sql logs periodically and place it to dedicated table. And you always will know who and when accessed you databases.

Like
bottom of page