meta données pour cette page
  •  

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

Prochaine révision		Révision précédente
sql_server:database:datafiles [10/12/2023 20:09] – créée mdlsql_server:database:datafiles [05/11/2025 09:26] (Version actuelle) – mdl
Ligne 1: Ligne 1:
-====== Server Disks ======+====== Gérer les DATAFILES ======
  
 +== Server Disks ==
 <code TSQL>  <code TSQL>
 SELECT  DISTINCT @@SERVERNAME AS 'Server Name', SELECT  DISTINCT @@SERVERNAME AS 'Server Name',
Ligne 12: Ligne 13:
 GO GO
 </code> </code>
 +\\ 
 +== TOTAL DB et TLOG (vue OS) ==
  
-TOTAL DB et TLOG (vue OS) 
 <code TSQL> <code TSQL>
 with fs with fs
Ligne 27: Ligne 29:
 from sys.databases db from sys.databases db
 </code> </code>
- +\\  
- +== DB and TLOG per File ==
-====== DB and TLOG per File ======+
 <code TSQL> <code TSQL>
 SELECT DB_NAME() AS DbName,  SELECT DB_NAME() AS DbName, 
-file_id, name AS logical_name, physical_name,+file_id, name AS logical_name, physical_name,type_desc,
 size*8.0/1024/1024 AS CurrentSizeGB,  size*8.0/1024/1024 AS CurrentSizeGB, 
 size*8.0/1024/1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)*8.2/1024/1024 AS FreeSpaceGB size*8.0/1024/1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)*8.2/1024/1024 AS FreeSpaceGB
 FROM sys.database_files FROM sys.database_files
 ORDER BY DbName,logical_name ASC 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> </code>