SQL Server 2008 log directory has too much dump file

Question

My SQL Server log directory has too many dump files. I have 3 databases on the server and the number of the transactions on them is very small, why would there be so many large files??

Answer 1

I had a similar problem and the problem was in the in msdb (sysjobhistory). try execute dbcc checkdb(msdb) if it display errors in the table sysjobhistory, try truncate sysjobhistory. this occurs because when mssql need to insert a new record in sysjobhistory as it is corrupted mssql dump data to the log dir. sorry for bad english

Answer 2

Start checking the log - the dump files are that - dump files. Logs should indicate why the server crashs so often. Check hardware issues. Are you properly patched up? Do that - install the last CU from SQL Server Release Services.

If nothing helps, then a MS PSS ticket needs to be opened, they can analyze the dump files. This likely leads to some hotfix, in which case it is a free ticket.

But first check logs and patch up the server.

Answer 3

SQL Server manages its log files by creating a new error log file every time SQL Server is started. The default is to have the current error log and 6 historical error logs (a stack of them for later reference).

If you want to manually cause the cycling of the error log for whatever reason, you can make a call to sp_cycle_errorlog.

You can also change this behavior if you want to minimize the retention of past log files by doing a registry write:

USE [master]
GO
EXEC xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 
    N'NumErrorLogs', 
    REG_DWORD, 
    4
GO

Obviously you can change that last parameter value (4 for example's sake) to whatever retention you want.