如何精准知晓数据库/日志文件增长时间？能否配置实时告警？

Great question—tracking file growth is critical for avoiding unexpected disk space issues and keeping your database running smoothly. Let’s break this down step by step, focusing on SQL Server (since you mentioned .mdf/.ldf files).

1. Precise Identification of File Growth Timestamps #

To get exact timestamps when your data or log files grew, you have three reliable, low-overhead options:

• Default Trace: SQL Server runs a lightweight default trace automatically that captures file growth events out of the box.
• Extended Events: The modern, customizable way to track growths with granular detail, perfect for both historical and future tracking.
• SQL Server Error Logs: These log autogrow events with timestamps, though they’re less structured than trace/event data.

2. Viewing .mdf/.ldf Growth History & Logs #

Method 1: Query the Default Trace #

The default trace stores data in .trc files. Run this query to extract historical growth data:

DECLARE @default_trace_path NVARCHAR(500);
SELECT @default_trace_path = CONVERT(NVARCHAR(500), value)
FROM sys.configurations
WHERE name = 'default trace enabled';

-- Trim to get the base log path, then append the default trace file name
SET @default_trace_path = LEFT(@default_trace_path, LEN(@default_trace_path) - PATINDEX('%[_]%', REVERSE(@default_trace_path))) + 'log.trc';

SELECT 
    DatabaseName,
    FileName,
    StartTime AS GrowthTimestamp,
    Duration / 1000 AS GrowthDurationMs,
    (IntegerData * 8) / 1024 AS GrowthSizeMB,
    CASE EventClass
        WHEN 92 THEN 'Data File Autogrow'
        WHEN 93 THEN 'Log File Autogrow'
    END AS EventType
FROM fn_trace_gettable(@default_trace_path, DEFAULT)
WHERE EventClass IN (92, 93) -- Filter for autogrow events only
ORDER BY StartTime DESC;

Method 2: Check SQL Server Error Logs #

Autogrow events are logged here with timestamps. Use this stored procedure to search for them:

-- Search the current error log for autogrow events
EXEC xp_readerrorlog 0, 1, N'Autogrow of file';

-- To check older logs, replace 0 with 1, 2, etc. (0 = current, 1 = previous)
-- EXEC xp_readerrorlog 1, 1, N'Autogrow of file';

Method 3: Capture Future Growths with Extended Events #

If you want to track growths going forward (and retain more detail like session IDs or SQL text), create an extended events session:

-- Create the session
CREATE EVENT SESSION [FileGrowthTracking] ON SERVER 
ADD EVENT sqlserver.database_file_size_change(
    ACTION(sqlserver.database_name, sqlserver.session_id, sqlserver.sql_text)
    WHERE (file_type = 0 OR file_type = 1) -- 0 = data file, 1 = log file
)
ADD TARGET package0.event_file(
    SET filename=N'C:\SQLServer\XEvents\FileGrowthTracking.xel', -- Use your preferred path
    max_file_size=(5), -- 5 MB per file
    max_rollover_files=(4) -- Keep up to 4 rollover files
)
WITH (STARTUP_STATE=ON); -- Start automatically when SQL Server restarts
GO

-- Start the session
ALTER EVENT SESSION [FileGrowthTracking] ON SERVER STATE=START;
GO

-- Query the captured events later
SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS EventName,
    event_data.value('(event/data[@name="database_name"]/value)[1]', 'varchar(100)') AS DatabaseName,
    event_data.value('(event/data[@name="file_name"]/value)[1]', 'varchar(200)') AS FileName,
    CASE event_data.value('(event/data[@name="file_type"]/value)[1]', 'int')
        WHEN 0 THEN 'Data File'
        WHEN 1 THEN 'Log File'
    END AS FileType,
    (event_data.value('(event/data[@name="old_size"]/value)[1]', 'bigint') / 1024) AS OldSizeMB,
    (event_data.value('(event/data[@name="new_size"]/value)[1]', 'bigint') / 1024) AS NewSizeMB,
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS GrowthTimestamp
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\SQLServer\XEvents\FileGrowthTracking*.xel', NULL, NULL, NULL)
) AS x
ORDER BY GrowthTimestamp DESC;

3. Setting Up Alerts for Immediate Notification #

You can configure real-time alerts using SQL Server Agent to notify you the moment a file grows:

Option 1: Alert on Autogrow Event IDs #

SQL Server logs autogrow events with specific IDs:

• Event ID 1035: Data file autogrow completed
• Event ID 1036: Log file autogrow completed

Here’s how to set up an alert:

1. Open SQL Server Management Studio (SSMS), expand SQL Server Agent > Alerts.
2. Right-click Alerts > New Alert.
3. On the General tab:
   • Name your alert (e.g., "Log File Autogrow Alert").
   • Select SQL Server Event Alert.
   • Choose the database you want to monitor (or "All databases").
   • Enter the event ID (1035 for data, 1036 for log).
4. On the Response tab:
   • Add an operator (make sure database mail is configured first if you want email notifications).
   • Check Notify operators via and select your preferred method (email, pager, etc.).
5. Save the alert—you’ll get notified every time the event occurs.

Option 2: Alert on Frequent Growths (Performance Counters) #

If you want to alert on excessive growths (e.g., more than 5 autogrows in an hour), use performance counters:

1. Create a new alert, select SQL Server Performance Condition Alert.
2. Choose the counter:
   • For data files: SQL Server:Databases\Data File(s) Growths
   • For log files: SQL Server:Databases\Log File(s) Growths
3. Set the condition (e.g., "Counter rises above 5" for the "Hour" interval).
4. Configure the response (email, etc.) as before.

Hope this covers all your needs—proactively tracking file growth can save you a lot of late-night troubleshooting!

内容的提问来源于stack exchange，提问作者Aleksey Vitsko