Find database last access date via powershell

Bit of misnomer, it’s still using SQL executed via the dm_db_index_usage_stats dmv to get the last user seek\scan\lookup\update column to determine the last access time, but still a good script to get a list of the last usage dates for all your databases on all your servers.  Also includes the last server restart time, as the dm_db_index_usage_stats resets upon server restart.  Use at your own risk.


$servers = Get-Content c:\Servers.txt

$outputs = @();

$lastAccessSQL = "
	DB_NAME(database_id) AS DatabaseName,
		'1/1/1900') AS LastAccessDate,
		(SELECT create_date FROM sys.databases WHERE name = 'tempdb') AS LastServerRestart
WHERE DB_NAME(database_id) NOT IN('tempdb', 'master', 'msdb')
GROUP BY database_id
ORDER BY DatabaseName

$servers | %{
	$srvName = $_
	$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	$srvConn.ServerInstance = $srvName

	$dr = $srvConn.ExecuteReader($lastAccessSQL)

		$output = New-Object -TypeName PSObject -Property @{
		    ServerName = $srvName
		    DatabaseName = $dr.GetString(0)
			LastAccessDate = $dr.GetDateTime(1)
			ServerLastRestart = $dr.GetDateTime(2)
		$outputs += $output
$outputs | SELECT ServerName, DatabaseName, LastAccessDate, ServerLastRestart | Out-GridView

2 thoughts on “Find database last access date via powershell

  1. Hello Scott, thanks for posting this! It was a big help. I noticed when I ran it that I was not receiving results for all of my SQL DBs. I spoke with my SQL guy and he modified your SQL query just a little bit and I simply dumped that into your overall script and it worked like a champ. I thought that I would share.

    sys.dm_db_index_usage_stats not use all database_id
    I propose to use sys.databases and left join sys.dm_db_index_usage_stats

    DB_NAME(sd.database_id) AS DatabaseName,
    ‘1/1/1900’) AS LastAccessDate,
    (SELECT create_date FROM sys.databases WHERE name = ‘tempdb’) AS LastServerRestart
    FROM sys.databases sd
    left join sys.dm_db_index_usage_stats ius on ius.database_id = sd.database_id
    WHERE DB_NAME(sd.database_id) NOT IN(‘tempdb’, ‘master’, ‘msdb’)
    GROUP BY sd.database_id
    ORDER BY DatabaseName

    Again, thank you for putting this out there!!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.