Xrhrft

The "Log" folder SIZE in the SQL server root directory (X:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLLog) becomes too big , ~80 GB.

When I checked it, I see that there are a lot of SQLDumpxxxx.mdmp /SQLDumpxxxx.txt files in that folder.

1. what to do with them ?


2. can I delete them and if it is good thing to do ?

10X

You should analyse and determine the cause of the Stack dumps, If you have support from microsoft, you can consult them and of course you can always delete them, they are nothing but memory dumps, may be generated because of memory issue, access violations, DB courruption, etc. You can also check SQL Server error log for the information or errors logged at the same time when dump was generated. Sometimes dumps are also generated because of Database corruption, So i will also suggest to run DBCC CHECKDB.

Hope this will help you, Thanks.

Scenario 1

I asked you about Version of SQL Server and you did not responded, the reason I asked it because if you are running RTM version of SQL Server or your SQL Server is not patched to latest Service Pack and cumulative update there is no point in opening case with Microsoft. If you do so the Microsoft Engineer or the support personal would first ask you to apply latest SP.

Other scenario is if you have not updated your SQL Server to latest SP, for instance for SQL Server 2012 you have SP3 released and you are still on SP1 and you log a case with Microsoft for this issue you would be charged and its quite possible the support guy would say this is known issues and is fixed in Sp3. So you would end up wasting money. So I strongly suggest you to check whether the SQL Server is on latest SP.

I also wanted to check whether you are actually running supported version of SQL Server or not. The way it is creating dump I have hunch that you are running SQL Server which is not patched to latest SP

Scenario 2

If the SQL Server is patched to latest SP and still it is crashing producing stack dumps I would suggest you to open case with Microsoft they are the best in terms of analyzing the stack dump file and would surely tell you the reason. Unless you are really good with analyzing dumps I would not suggest you to waste time in doing so.

I can share with you few blogs which would give you some hint on how to analyze the dumps

• Looking into SQL Server Minidump File


• How to analyze deadlock scheduler dumps


• Analyzing non yielding resource monitor

Scenario 3

Not all crash dumps are because of bug in SQL Server many occur due to poorly configured SQL Server or some rouge queries running. But since you have not shared detailed errorlog it is difficult to say at this point. Make sure your SQL Server is configured correctly. Again if such is the case MS support will point this out.

Moral:

If SQL Server is not updated with latest SP first update it, look for the cumulative updates released after the SP(you can get that from first link I have shared) and make sure bug you are facing is not fixed in CU releases ONLY then open case with Microsoft.

can I delete them and if it is good thing to do ?

If you are planning to open case with Microsoft I suggest you move them to some other location just in case. If you have those dumps you can give more information to support personal who would be looking at your case. Also note that its quite likely that the dump produced would not capture all information related to the issue and support personal would ask you to enable trace flag and wait for the next dump to occur which will capture all the related information.

If you really like to delete it, delete the old ones and leave the new ones.

First of all, everything that Shanky said in his answer is 100% correct. I would like to add:

1. The age of the files - If your log folder has several dumps from two years ago, then no dumps for several months, then a few recent dumps, you can safely delete the two year old dumps.




2. Duplicate dumps - When dumps occur there are three files created: .txt, .log & .mdmp. Open the .txt file for several of the dumps. If they are different, keep the dumps. If they are the same, delete the old ones.




3. Your available disk space - If the disk where your dumps are accumulating is also the same disk where your database files or transaction log files also reside, and you are going to run out of disk space if you don't do something, and you have no where to move the dump files to, then by all means, delete them.




4. Types of dumps - If the .log file indicates "Non-yielding Scheduler", "Non-yielding IOCP Listener" or "Non-yielding Resource Monitor", these are performance related specific to CPU. You can troubleshoot those following the steps in this blog: https://blogs.msdn.microsoft.com/karthick_pk/2012/08/21/non-yielding-iocp-listener-non-yielding-scheduler-and-non-yielding-resource-monitor-known-issues-and-fixes/

If the .log file indicates "Deadlocked Schedulers", this is also performance related specific to CPU. You can troubleshoot those following the steps in this blog: https://blogs.msdn.microsoft.com/karthick_pk/2010/06/22/how-to-analyze-deadlocked-schedulers-dumps/

5. Corruption Dumps - Just as the first answer states, run DBCC CHECKDB. If the output from DBCC CHECKDB indicates corruption in your indexes, rebuild them. If it indicates that "repair allow dataloss is the minimum" necessary to repair the database, then restore from last known good backup.




6. AV Dumps - You can attempt to tackle this yourself: https://blogs.msdn.microsoft.com/sqlcat/2009/09/11/looking-deeper-into-sql-server-using-minidumps/
   
   If you run into difficulty, open a case with Microsoft, but just as Shanky said, ensure you are on a supported version and you have the latest updates.

1-what to do with them ?

In the hosting Windows Servers's this can occure because brutforce attack can cause a huge log files!

Solution

you can read log files and block unknown ips trying to log in with "SA" or another user.

2-can I delete them and if it is good thing to do ?

Solution

yes you can delete logs files، But for caution move them to another folder and after a short time remove.