基於跟踪文件數據的 PROD DB 中的自動增長分析

我使用下面的查詢來檢查我們的生產數據庫自動增長事件，以便我們可以設置一個比目前值 200 mb 更現實的值，對於 mdf 文件是無限制的，對於 ldf 文件是 10 % Restricted to 2048 gb。我們數據庫的目前版本是 SQL Server 2012 RTM 標準版，我們通過 AWS RDS 管理它，總磁碟空間為 2TB。當我使用跟踪文件查詢時，它只顯示來自 08052018 的數據，如您在隨附的螢幕截圖中所見

誰能解釋為什麼我只看到 08052018 的數據而不是更早的數據？

您很可能正在研究稱為預設跟踪的東西，這是一種在 SQL Server 啟動時在後台執行的輕量級跟踪。查看是否啟用了預設跟踪：

SELECT value_in_use 
   FROM sys.configurations 
   WHERE configuration_id = 1568 /* 1 indicates the trace is enabled */

查看跟踪文件的位置：

SELECT  REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 256)) AS DefaultTraceLocation
FROM    sys.traces
WHERE   is_default = 1

您將在此處找到四個跟踪文件，每個文件大小約為 5 mb。當所有四個文件都已滿時，它會翻轉（從磁碟中刪除最舊的文件）並創建一個新的跟踪文件。因此，您可能無法跟踪舊數據，除非您將其轉移到某處的自定義表中。

您可以使用以下命令查看目前跟踪文件的自動增長設置：

/*Author - Feodor Georgiev*/
SELECT  TE.name AS [EventName] ,
       T.DatabaseName ,
       t.DatabaseID ,
       t.NTDomainName ,
       t.ApplicationName ,
       t.LoginName ,
       t.SPID ,
       t.Duration ,
       t.StartTime ,
       t.EndTime
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                             f.[value]
                                                     FROM    sys.fn_trace_getinfo(NULL) f
                                                     WHERE   f.property = 2
                                                   )), DEFAULT) T
       JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE   te.name = 'Data File Auto Grow'
ORDER BY t.StartTime

您還可以通過稍微修改先前的查詢來從先前的跟踪文件中提取數據。

SELECT  TE.name AS [EventName] ,
       T.DatabaseName ,
       t.DatabaseID ,
       t.NTDomainName ,
       t.ApplicationName ,
       t.LoginName ,
       t.SPID ,
       t.Duration ,
       t.StartTime ,
       t.EndTime
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),LEN(path)))+'log.trc' 
                                                       FROM sys.traces
                                                           WHERE path LIKE '%\MSSQL\Log\log%.trc'
                                                   )), DEFAULT) T
       JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE   te.name = 'Data File Auto Grow'
ORDER BY t.StartTime 

有關如何將此數據載入到表中的範例

CREATE TABLE [dbo].[DefaultTrace_History](
   [Id] [bigint] IDENTITY(1,1) NOT NULL,
   [instancename] [nvarchar](256) NOT NULL,
   [EventName] [nvarchar](128) NOT NULL,
   [DatabaseName] [nvarchar](256) NOT NULL,
   [DatabaseID] [int] NOT NULL,
   [NTDomainName] [nvarchar](256) NULL,
   [ApplicationName] [nvarchar](256) NOT NULL,
   [LoginName] [nvarchar](256) NOT NULL,
   [SPID] [int] NOT NULL,
   [Duration] [int] NOT NULL,
   [StartTime] [datetime] NOT NULL,
   [EndTime] [datetime] NOT NULL,
   [GrowthInMB] [decimal](5,2) NOT NULL,
   [FileName] [nvarchar](256) NOT NULL
) ON [PRIMARY]
GO

增量載入數據的過程：

CREATE OR ALTER PROCEDURE [dbo].[SP_Load_DefaultTrace_History] 
AS 

   DECLARE @maxdt DATETIME 
   DECLARE @sql NVARCHAR(max) 

   SELECT @maxdt = max(starttime) 
   FROM   [DefaultTrace_History] 

   SET @sql= 
   N'INSERT INTO [DefaultTrace_History] (InstanceName,EventName,DatabaseName,DatabaseID,NTDomainName,ApplicationName,LoginName,SPID,Duration,StartTime,EndTime,GrowthInMB,FileName)
   SELECT  @@servername,
       TE.name AS [EventName] ,
       T.DatabaseName ,
       t.DatabaseID ,
       t.NTDomainName ,
       t.ApplicationName ,
       t.LoginName ,
       t.SPID ,
       t.Duration ,
       t.StartTime ,
       t.EndTime,
       convert(decimal(19,2),t.IntegerData*8/1024) AS GrowthInMB,
       smf.physical_name AS [FileName]
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX(''\'',REVERSE(path)),LEN(path)))+''log.trc'' 
                                                       FROM sys.traces
                                                           WHERE path LIKE ''%\MSSQL\Log\log%.trc''
                                                   )), DEFAULT) T
       JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
       JOIN sys.master_files smf ON smf.database_id = t.DatabaseID
WHERE   te.name = ''Data File Auto Grow'''
   IF (SELECT count(1) 
       FROM  [DefaultTrace_History] 
       WHERE  instancename = @@servername) = 0 
     BEGIN 
         EXEC sp_executesql @sql 
     END 
   ELSE 
     BEGIN 
         SET @sql= @sql + ' AND @@servername = @@servername AND t.StartTime >' + quotename(convert(VARCHAR(25), @maxdt, 121),'''') + ' ORDER BY t.StartTime DESC' 
         EXEC sp_executesql @sql 
     END 

--Purge records > 365 days
DELETE FROM [DefaultTrace_History] WHERE [instancename] = @@servername AND [StartTime] < dateadd(dd,-365,getdate())
GO

然後，您可以創建一個作業以按計劃執行以載入數據。

引用自：https://dba.stackexchange.com/questions/206851