DATAFILES

SELECT  DISTINCT @@SERVERNAME AS 'Server Name',
volume_mount_point as VolumeName,
dovs.logical_volume_name AS VolumeLabel,
total_bytes /1024/1024/1024 as VolumeCapacityGB, 
available_bytes /1024/1024/1024 AS VolumeFreeSpaceGB,
(select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as PercentageFreeSpace
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
GO

with fs
as
(
    select database_id, type, size * 8.0 / 1024 / 1024 size
    from sys.master_files
)
select 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeGB
	,(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeGB
from sys.databases db

SELECT DB_NAME() AS DbName, 
file_id, name AS logical_name, physical_name,
size*8.0/1024/1024 AS CurrentSizeGB, 
size*8.0/1024/1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)*8.2/1024/1024 AS FreeSpaceGB
FROM sys.database_files
ORDER BY DbName,logical_name ASC

DECLARE @current_tracefilename VARCHAR(500);
DECLARE @0_tracefilename       VARCHAR(500);
DECLARE @indx                  INT;
SELECT
       @current_tracefilename = path
FROM sys.traces
WHERE is_default = 1;
 
SET @current_tracefilename = REVERSE(@current_tracefilename);
SELECT @indx = PATINDEX('%\%', @current_tracefilename);
SET @current_tracefilename = REVERSE(@current_tracefilename);
SET @0_tracefilename       = LEFT(@current_tracefilename, LEN(@current_tracefilename) - @indx) + '\log.trc';
SELECT
           DatabaseName
         , te.name
         , Filename
         , CONVERT(DECIMAL(10, 3), Duration / 1000000e0) AS TimeTakenSeconds
         , StartTime
         , EndTime
         , (IntegerData * 8.0 / 1024) AS 'ChangeInSize MB'
         , ApplicationName
         , HostName
         , LoginName
FROM ::fn_trace_gettable(@0_tracefilename, DEFAULT) t
INNER JOIN sys.trace_events AS te ON t.EventClass = te.trace_event_id
WHERE
           (
                      trace_event_id     >= 92
                      AND trace_event_id <= 95
           )
ORDER BY t.StartTime DESC;