DB Usage and Disk Space Info

I have combined a few scripts into this long one that provides the following columns:

The scripts provides many uses:
1) In LAB Environment, you can review which DBs have not been accessed and plan to potentially remove them.
2) Are you running out of disk space? Review which DBs are taking up space. Are any of the Transaction logs out of order?

Code follows:

— Get Disk space
if object_id(‘tempdb..#output’) is not null drop table #output
declare @svrName varchar(255)
declare @sql varchar(400)
–by default it will take the current server name, we can the set the server name as well
set @svrName = @@SERVERNAME
set @sql = ‘powershell.exe -c “Get-WmiObject -ComputerName ‘ + QUOTENAME(@svrName,””) + ‘ -Class Win32_Volume -Filter ”DriveType = 3” | select name,capacity,freespace | foreach{$_.name+”|”+$_.capacity/1048576+”%”+$_.freespace/1048576+”*”}”‘
–creating a temporary table
CREATE TABLE #output
(line varchar(255))
–inserting disk name, total space and free space value in to temporary table
insert #output
EXEC xp_cmdshell @sql

/*
–script to retrieve the values in MB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(‘|’,line) -1))) as drivename
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘|’,line)+1,
(CHARINDEX(‘%’,line) -1)-CHARINDEX(‘|’,line)) )) as Float),0) as ‘capacity(MB)’
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘%’,line)+1,
(CHARINDEX(‘*’,line) -1)-CHARINDEX(‘%’,line)) )) as Float),0) as ‘freespace(MB)’
from #output
where line like ‘[A-Z][:]%’
order by drivename

--script to retrieve the values in GB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
   ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
   (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'
   ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
   (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
from #output
where line like '[A-Z][:]%'
order by drivename
--script to drop the temporary table
drop table #output

*/

— Display info
Select usg., s.Physical_Name, s.SizeMB, dsk.
from
(
select d.dbid, d.name, d.crdate ‘CreatedOn’,LastDateAccessed =
(select X1= max(bb.xx)
from (
select xx = max(last_user_seek)
where max(last_user_seek) is not null
union all
select xx = max(last_user_scan)
where max(last_user_scan) is not null
union all
select xx = max(last_user_lookup)
where max(last_user_lookup) is not null
union all
select xx = max(last_user_update)
where max(last_user_update) is not null) bb)

    FROM master.dbo.sysdatabases d 
    left outer join 
    sys.dm_db_index_usage_stats s 
    on d.dbid= s.database_id 
    group by  d.dbid,d.name, d.crdate
    ) usg
left join
    (
    SELECT database_id, DB_NAME(database_id) AS DatabaseName,
           Name AS Logical_Name,
           Physical_Name,
           (size * 8) / 1024 SizeMB
    FROM sys.master_files
    ) s
    on usg.dbid = s.database_id
left join
(
    select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
       ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
       (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'
       ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
       (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'

    from #output
    where line like '[A-Z][:]%'
) dsk on left(s.Physical_name,1) = left(dsk.drivename,1)
order by left(Physical_name,1), SizeMB desc

— DBs not accessed
Select usg., s.Physical_Name, s.SizeMB, dsk.
from
(
select d.dbid, d.name, d.crdate ‘CreatedOn’,LastDateAccessed =
(select X1= max(bb.xx)
from (
select xx = max(last_user_seek)
where max(last_user_seek) is not null
union all
select xx = max(last_user_scan)
where max(last_user_scan) is not null
union all
select xx = max(last_user_lookup)
where max(last_user_lookup) is not null
union all
select xx = max(last_user_update)
where max(last_user_update) is not null) bb)

    FROM master.dbo.sysdatabases d 
    left outer join 
    sys.dm_db_index_usage_stats s 
    on d.dbid= s.database_id 
    group by  d.dbid,d.name, d.crdate

    ) usg
left join
    (
    SELECT database_id, DB_NAME(database_id) AS DatabaseName,
           Name AS Logical_Name,
           Physical_Name,
           (size * 8) / 1024 SizeMB
    FROM sys.master_files
    ) s
    on usg.dbid = s.database_id
left join
(
    select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
       ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
       (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'
       ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
       (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'

    from #output
    where line like '[A-Z][:]%'
) dsk on left(s.Physical_name,1) = left(dsk.drivename,1)
where  LastDateAccessed is null
order by left(Physical_name,1), SizeMB desc