9

The "Log" folder SIZE in the SQL server root directory (X:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log) 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

3
  • 1
    Why are you accumulating so many mini dump files? Is the service constantly crashing? – Martin Smith Dec 28 '14 at 14:40
  • 2
    You diagnose why they are there, they are created for a reason. – user507 Dec 28 '14 at 16:13
  • What is version and edition of SQL Server – Shanky Dec 28 '14 at 16:46
4

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.

5

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/

  1. 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.

  2. 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.

4

For me it was polybase service with SQL Server 2019 Developer edition, which was not able to start because of TCP/IP protocol was disabled (by default) and was creating large memory dumps multiple times a day.

Here's a very good article with step by step instruction to resolve this issue.

https://nielsberglund.com/2019/11/20/fix-polybase-in-sql-server-2019-developers-edition/

Main extract from the above article is to

  1. Enable TCP/IP as a network protocol for SQL Server. Network protocols can be enabled using the SQL Server Configuration Manager. Open SQL Server Configuration Manager either via the Windows Start menu or from Computer Manager
  2. Restart SQL Server service
  3. If you can't restart SQL Server service, kill the process of service using "taskkill" command from command prompt (as administrator) with process id. e.g. taskkill /PID {process id} /F
  4. Start SQL Server service
2
  • You may want to add salient points from the linked page to your answer directly, lest it becomes useless once the link goes stale. – mustaccio Jul 15 '20 at 16:42
  • @mustaccio modified my answer as suggested. Thanks – kiran Jul 16 '20 at 17:18
3
+50

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

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.

2
  • I've started the bounty. For me the version is: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) – Hasan Gürsoy Nov 22 '16 at 15:05
  • 3
    SQL Server 2008 RTM is NOT SUPPORTED at all. You only have extended support for SQL Server 2008 SP4 not the main stream support. So i suggest go ahead and first apply SQL Server 2008 Sp4 and if you still face the issue then open the case with MS. – Shanky Nov 22 '16 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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