Quick Scan Report – Too Many Log Files

What this check looks for

The number of files in the SQL Server log directory, the folder containing ERRORLOG. The check is skipped on Amazon RDS.

Why it matters

The log directory is where SQL Server writes several different kinds of file, and most of them clean up after themselves badly or not at all.

What accumulates there:

The finding is worth acting on for three separate reasons, and they are not equally important.

The first is that SQLDump files mean something happened. SQL Server writes a memory dump when it hits an exception it did not expect: an access violation, a scheduler that stopped responding, a latch timeout, an assertion. Each dump is accompanied by a text file naming the condition. A directory with a hundred dump files is not a housekeeping problem, it is a hundred unreported incidents, and reading the most recent one is more valuable than deleting any of them.

The second is disk space. A full memory dump can be the size of the buffer pool, so a handful of them on a large instance can fill the volume. And the log directory is typically on the C: drive on a default installation, which means the volume being filled is the system one.

The third is that a directory with thousands of files is slow to enumerate, which affects anything reading the logs, including SQL Server’s own log viewer and any monitoring that watches the folder.

The dumps deserve the attention. Everything else in that list is either self managing or trivially deletable. A pattern of dumps is a genuine finding: it may be a known defect fixed in a later cumulative update, a hardware problem, or a specific query that triggers an engine bug. In every case the answer is to read them, not to clear them.

How to confirm it yourself

Where the directory is:

SELECT SERVERPROPERTY('ErrorLogFileName') AS [error_log_path];

The error logs themselves, with sizes:

EXEC sys.sp_enumerrorlogs;

Whether SQL Server has recorded any dumps, which is the important question:

SELECT [filename],
       [creation_time],
       [size_in_bytes] / 1048576.0 AS [size_mb]
  FROM sys.dm_server_memory_dumps
 ORDER BY [creation_time] DESC;

Any rows here are the finding underneath the finding. An empty result means the directory is just cluttered.

What caused them, from the error log:

EXEC xp_readerrorlog 0, 1, N'Stack Dump';
EXEC xp_readerrorlog 0, 1, N'access violation';
EXEC xp_readerrorlog 0, 1, N'non-yielding';
EXEC xp_readerrorlog 0, 1, N'Assertion';

Those entries name the condition and reference the dump file. The accompanying SQLDump*.txt file in the log directory contains the stack, which is what a support case would need.

Whether the system health session and the default trace are contributing:

SELECT [name], [max_file_size], [max_rollover_files], [file_data_size]
  FROM sys.dm_xe_session_targets AS t
  JOIN sys.dm_xe_sessions AS s ON s.[address] = t.[event_session_address]
 WHERE s.[name] = 'system_health';

SELECT [name], [value_in_use] FROM sys.configurations WHERE [name] = 'default trace enabled';

And how the space is distributed, which you read from the folder itself. From PowerShell on the server:

Get-ChildItem "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log" |
  Group-Object Extension |
  Select-Object Name, Count, @{n='MB';e={[math]::Round(($_.Group | Measure-Object Length -Sum).Sum / 1MB, 1)}} |
  Sort-Object MB -Descending

How to fix it

Read the dumps before deleting anything, then tidy the rest.

1. Deal with the dumps first.

For each recent dump, open the matching SQLDump*.txt file. It names the exception and the stack. Then:

• Check your build. Many dump-producing defects are fixed in later cumulative updates, and the error log entry usually contains enough detail to search for a known fix.
• Look for a pattern. Dumps at the same time each night point at a scheduled job; dumps correlated with a particular query point at a specific defect.
• Check the hardware, since memory errors and storage failures both produce dumps.
• Open a support case if the dumps continue on a current build. The dump files are what the case needs, which is a reason to keep the recent ones rather than clear them.

Keep the last few and remove the rest, once you have read them:

Get-ChildItem "...\MSSQL\Log\SQLDump*" | Sort-Object CreationTime -Descending |
  Select-Object -Skip 5 | Remove-Item

2. Set sensible retention on the error logs, which is the same fix as the huge error log check:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'NumErrorLogs', REG_DWORD, 30;

3. Move the log directory off the C: drive if it is there, which is done with the -e startup parameter in Configuration Manager and takes effect at the next restart:

-eE:\SQLLogs\ERRORLOG

4. Check your own Extended Events sessions. A session writing to a file target with no MAX_ROLLOVER_FILES will accumulate .xel files indefinitely, and that is the most common self inflicted cause of a full log directory:

SELECT s.[name]           AS [session_name],
       f.[name]           AS [field],
       f.[value]
  FROM sys.server_event_sessions       AS s
 INNER JOIN sys.server_event_session_targets AS t ON t.[event_session_id] = s.[event_session_id]
 INNER JOIN sys.server_event_session_fields  AS f ON f.[object_id] = t.[target_id]
 WHERE t.[name] = 'event_file';

Fix one with:

ALTER EVENT SESSION [YourSession] ON SERVER
  DROP TARGET package0.event_file;

ALTER EVENT SESSION [YourSession] ON SERVER
  ADD TARGET package0.event_file
      (SET filename = N'E:\XEvents\YourSession.xel',
           max_file_size = 100,
           max_rollover_files = 5);

5. Leave the system health session and the default trace alone. Both roll over and both are genuinely useful. The system health session is often the only record of a deadlock or a scheduler problem, and deleting its files removes exactly the evidence this finding should make you want.

6. Do not schedule a blanket delete of the whole directory. A script that clears everything older than N days will remove the dumps and the system health files, which are the two things worth keeping.

How long it takes

About an hour, more if the dumps need investigating. Reading one dump text file takes a few minutes and is the most valuable part.

Related reports

Related checks

Frequently asked questions

Can I just delete everything in the log directory? No. Dump files and system health .xel files are evidence, and the current ERRORLOG is in use. Read the dumps, keep the recent ones, and let the self managing files manage themselves.

What is a SQLDump file? A memory dump written when SQL Server hits an unexpected exception. Each one has a matching text file naming the condition. Their presence means something went wrong that nobody reported.

Should I delete the system_health xel files? No. That session rolls over on its own and is frequently the only record of a deadlock or a scheduler problem.

How do I move the log directory? Change the -e startup parameter in SQL Server Configuration Manager and restart. Getting it off the C: drive is worth doing at the same time as any other restart.