When was my database last accessed in SQL Server ?

To determin the last access date of a particular database the following can be used:

SQL Server 2005/2008

For SQL Server 2005/2008 it is relatively easy to extract the last access dates for a database as it is stored in the sys.dm_db_index_usage_stats table.

SQL Server 2005/2008 DB Access older than 30 days

SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate FROM (SELECT    DB_NAME(database_id) DatabaseName,     last_user_seek,        last_user_scan,        last_user_lookup,        last_user_update      FROM sys.dm_db_index_usage_stats) AS PivotTable UNPIVOT     (LastAccessDate FOR last_user_access IN       (last_user_seek,        last_user_scan,        last_user_lookup,            last_user_update)     ) AS UnpivotTable WHERE DATEDIFF(day,getdate(),LastAccessDate) < 30 GROUP BY DatabaseName HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb') ORDER BY 2

SQL Server 2000

For SQL Server 2000 it is a bit more difficult; the only work around I have come up with so far is a little clunky.  I haven't found any obvious way to extract last accessed dates from the system tables in a SQL server 2000 database (yet) so I have resorted to using the 'last updated' date and time for the data files as follows:

SQL Server 2000 returning last access date/time

Declare @DBFile nvarchar(260) Declare @DBName nvarchar (260) declare @i int Declare @k int   Set @DBName = 'yourdbnamehere'   CREATE  TABLE #ATTRIBS (  alternate_name  VARCHAR(128),  [size]   INT,  creation_date  INT,  creation_time  INT,  last_written_date INT,  last_written_time INT,  last_accessed_date INT,  last_accessed_time INT,  attributes  INT)   Declare @FName Table ( FID smallint IDENTITY(1,1), DBFile nvarchar(260))   set nocount on   Insert  @FName (DBFile) Select filename  from master.dbo.sysaltfiles where dbid = (select  dbid from master.dbo.sysdatabases where name = @DBName)   Set @k = @@IDENTITY set @DBFile = '' set @i = 1   While  @k +1 > @i    Begin       Select @DBFile = RTrim(DBFile) from @FName where FID = @i         INSERT INTO #ATTRIBS EXEC master.dbo.xp_getfiledetails @DBFile         Set @i = @i + 1    End   Select @DBName + ' last time used on ' +  cast(cast(cast(max(last_accessed_date) as varchar(20)) as datetime) as varchar(12)) from #ATTRIBS