Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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??

share|improve this question
2  
Can you explain your nomenclature a bit? By 'log directory' do you mean the directory holding the database log files (the LDFs), usually named Data, or the directory named 'log' which holds the erorlog files? And by Dump file, do you mean files named sqldump or ... something else? –  Remus Rusanu Jul 5 '12 at 14:23
    
Good question, @RemusRusanu. My answer assumed the OP was talking about the SQL Server Logs. –  Thomas Stringer Jul 5 '12 at 14:25
add comment

migrated from stackoverflow.com Jul 5 '12 at 14:15

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 1 down vote accepted

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.