====== Gérer les DATAFILES ======

== Server Disks ==
<code TSQL>			
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
</code>
\\ 
== TOTAL DB et TLOG (vue OS) ==

<code TSQL>
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
</code>
\\ 
== DB and TLOG per File ==
<code TSQL>
SELECT DB_NAME() AS DbName, 
file_id, name AS logical_name, physical_name,type_desc,
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
</code>
\\ 
== Historique AutoGrow ==
 <code TSQL>
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;
</code>
\\  
== Shrinkfile ==

  * Statut d'un shrink en cours
 <code TSQL>
SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests
where command = 'DbccFilesCompact'
</code>

  * Shrink
<code TSQL>
USE [DBWI1Z81]
GO
DBCC SHRINKFILE (N'DBWI1Z81_3' , 71680)
GO
</code>