Find when a database was last used in SQL

Here is some nice SQL that will tell us when each database was last accessed.

SELECT @@servername , 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
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY 2

Get useful information on your database

Its sometimes useful to know a little more about your database, the below will give you information on Database_name, File_Name, Location, FileSizeMB, SpaceUsed, SpaceFree, database Owner, Recovery Model, Compatibility and Collation:-

set nocount on
create table #dbfileInfo(
Database_name varchar(300),
File_name varchar(300),
location varchar(300),
filesizeMB decimal(9,2),
spaceUsedMB decimal(9,2),
FreespaceMB decimal(9,2),
DB_Owner varchar(max),
Recovery_Model varchar(max),
Compatibility varchar(max),
Collation varchar(max))

declare @mySQL nvarchar(2000)
--DECLARE @dbName varchar(MAX)
DECLARE @cur_DBName CURSOR
DECLARE @DBO varchar(max)
DECLARE @Rec_Model varchar(max)
DECLARE @compt_lvl varchar(max)
Declare @collation varchar(max)


set nocount on
declare @SQLText nvarchar(4000), @DBName sysname


select @DBName = min(name) from sysdatabases
while @DBName is not null
begin



select @mySQL =
    'use ' + @dbname + '
        INSERT INTO #dbfileInfo
        select db_name(),
      sf.name
    , filename
    , convert(decimal(18,2),round(sf.size/128.000,2)) as FileSizeMB
    , convert(decimal(18,2),round(fileproperty
(sf.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
    , convert(decimal(18,2),round((sf.size-fileproperty
(sf.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
, SUSER_SNAME(owner_sid) as [DB_Owner]
    ,recovery_model_desc
    ,compatibility_level
    ,Collation_name
    from master.dbo.sysaltfiles sf
 INNER join master.sys.databases sd
 ON sf.dbid = sd.database_id where sd.name = db_name()
    '
    exec sp_executesql @mySQL
      -- select  @mySQL

--select @DBName
select @DBName = min(name) from sysdatabases where name > @DBName
end

 select * from #dbfileInfo
drop table #dbfileInfo

Get disk space info

EXEC master..xp_fixeddrives

Simple as that!