Large number of log/crash dump files generated.
Question
Wednesday, October 14, 2009 10:59 AM
Large number of log files,text files and crash dump files are generated at: "C:\Program Files\Microsoft Office Servers\12.0\Data\MSSQL.1\MSSQL\LOG" location.
The 1st file is ERRORLOG,its size gets increasing and it can't be deleted.Other files are in sequence:SQLDump001.mdmp,SQLDump001.txt,SQLDump001.log,SQLDump002.mdmp,SQLDump002.txt,
SQLDump002.log,etc. There are also files such as:SQLDUMPER_ERRORLOG.log
These files keep consuming the disk space and the hard disk becomes full.
Can anyone suggest a solution to stop generation of these files.
Any help is appreciated.
All replies (9)
Wednesday, October 14, 2009 11:04 AM ✅Answered
Hi moss_lover
these are generated by Access Violation errors in the SQL Server process. You cannot stop them being created - they are generating debug information.
Errorlog is the file written by SQL Server with general error/informational messages. This should tell you which process is generating the AV errors. You can open that file in notepad to get more info.
In the short-term, you can delete files named SQLDumpnnnn.mdmp, since they are not active (though you'll lose the debug info contained in them)
Possibly consider applying the latest service pack, if you haven't already done so.
HTH
Ewan
If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).
If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
Wednesday, October 14, 2009 11:18 AM ✅Answered
To add on to Ewan's response, the ERRORLOG fle (no extension) includes informational and error messages. The ERRORLOG file is renamed with a numbered version extension (ERRORLOG.n) each time the instance is restarted or when your execute sp_cycle_errorlog. SQL Server keeps several versions by default. The files are typically fairly small but can become relatively large if you have repeated problems, like access vioations. I suggest you investigate the root cause of those.
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
Monday, December 27, 2010 5:48 PM
At the end what was the problem, who were generating the SQLDump files? How did you fix it? Does the SP work for you? am having the same problems with these files in SQL Server 2008 SP1. the thing is a dont see anything odd in SQL Error log, just info and warnings. I will appreciate any help. Thanks,Ing. Carlos Ureña H.
Saturday, November 19, 2011 3:18 PM
Hello Carlos and Dan
I have the same problem right now. I do maintenance one time in one of the remote servers and wondered why the size of the System drive is so big as compared with the other systems I found out that the folder log was full of SQLDUMP###.TXT, LOG and MDM.
I am researching on how to correct this one and suggestions on threads were:
1. change the memory of the server. since it speaks of modules. ( I had changed the memory and observe if the error is rectified . to my dismay it came back after a maintenance plan was executed.
2. I have to try other ways like apply the latest service pack..
Can yoou suggest some more aside from reinstalling the SQL Server?
Thanks in advance
Rene
Saturday, November 19, 2011 3:58 PM
A dump means that something unexpected happened, due to either corruption, bugs or hardware problem. Run DBCC CHECKDB to rule out database corruption and apply the latest service pack to apply the included bug fixes. If you cannot resolve the errors, see http://support.microsoft.com/ph/1044 for support options. In the case of paid incident support, the fee can be refunded if it's a SQL Server bug.
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
Monday, November 21, 2011 9:53 AM
Dan,
I have 29 servers and this the only one that has that error.
I posted the error below.
1. Can this be done while on production it should be taken offline?
2. I have taken back up. Can these be a prelude to database crash?
3. This is the only error. I found that I know was causing the SQLDUMP.
4. Do I need to patch the server to its latest patch. SQL Server Patch 4?
Task start: 11/13/2011 4:00 AM.
Task end: 11/13/2011 4:00 AM.
Failed:(-1073548784) Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
" failed with the following error:
"Table error: Table 'sys.sysidxstats' (ID 54). Data row does not have a matching index row in index 'nc' (ID 2). Possible missing or invalid keys for the index row matching:
Data row (1:520:1) identified by (id = 577004096 and indid = 2) with index values (name = '_WA_Sys_00000004_22646240' and id = 577004096).
Table error: Table 'sys.sysidxstats' (ID 54). Data row does not have a matching index row in index 'nc' (ID 2). Possible missing or invalid keys for the index row matching:
Data row (1:520:2) identified by (id = 577004096 and indid = 3) with index values (name = '_WA_Sys_00000005_22646240' and id = 577004096).
Table error: Table 'sys.sysidxstats' (ID 54). Data row does not have a matching index row in index 'nc' (ID 2). Possible missing or invalid keys for the index row matching:
Data row (1:520:3) identified by (id = 577004096 and indid = 4) with index values (name = '_WA_Sys_00000008_22646240' and id = 577004096).
Table error: Table 'sys.sysidxstats' (ID 54). Data row does not have a matching index row in index 'nc' (ID 2). Possible missing or invalid keys for the index row matching:
Data row (1:520:4) identified by (id = 577004096 and indid = 5) with index values (name = '_WA_Sys_00000009_22646240' and id = 577004096).
Table error: Table 'sys.sysidxstats' (ID 54). Index row in index 'nc' (ID 2) does not match any data row. Possible extra or invalid keys for:
Index row (1:1286:31) with values (name = '_WA_Sys_00000002_207C19CE' and id = 545003982) pointing to the data row identified by (id = 545003982 and indid = 3).
Table error: Table 'sys.sysidxstats' (ID 54). Index row in index 'nc' (ID 2) does not match any data row. Possible extra or invalid keys for:
Index row (1:1286:32) with values (name = '_WA_Sys_00000002_21703E07' and id = 561004039) pointing to the data row identified by (id = 561004039 and indid = 3).
Table error: Table 'sys.sysidxstats' (ID 54). Index row in index 'nc' (ID 2) does not match any data row. Possible extra or invalid keys for:
Index row (1:1286:87) with values (name = '_WA_Sys_00000003_207C19CE' and id = 545003982) pointing to the data row identified by (id = 545003982 and indid = 4).
Table error: Table 'sys.sysidxstats' (ID 54). Index row in index 'nc' (ID 2) does not match any data row. Possible extra or invalid keys for:
Index row (1:1286:88) with values (name = '_WA_Sys_00000003_21703E07' and id = 561004039) pointing to the data row identified by (id = 561004039 and indid = 2).
Table error: Table 'sys.sysidxstats' (ID 54). Index row in index 'nc' (ID 2) does not match any data row. Possible extra or invalid keys for:
Index row (1:1508:292) with values (name = '_WA_Sys_00000001_207C19CE' and id = 545003982) pointing to the data row identified by (id = 545003982 and indid = 2).
The Index Allocation Map (IAM) page (1:1641) is pointed to by the next pointer of IAM page (0:0) in object ID 497003811, index ID 0, partition ID 72057691404828672, alloc unit ID 72057691409416192 (type In-row data), but it was not detected in the scan.
Table error: Could not check object ID 497003811, index ID 0, partition ID 72057691404828672, alloc unit ID 72057691409416192 (type In-row data) due to invalid allocation (IAM) page(s).
Table error: Allocation page (1:1641) has invalid IAM_PAGE page header values. Type is 1. Check type, alloc unit ID and page ID on the page.
The Index Allocation Map (IAM) page (1:1603) is pointed to by the next pointer of IAM page (0:0) in object ID 513003868, index ID 0, partition ID 72057691404894208, alloc unit ID 72057691409481728 (type In-row data), but it was not detected in the scan.
Table error: Could not check object ID 513003868, index ID 0, partition ID 72057691404894208, alloc unit ID 72057691409481728 (type In-row data) due to invalid allocation (IAM) page(s).
Table error: Allocation page (1:1603) has invalid IAM_PAGE page header values. Type is 2. Check type, alloc unit ID and page ID on the page.
CHECKDB found 0 allocation errors and 9 consistency errors in table 'sys.sysidxstats' (object ID 54).
CHECKDB found 1 allocation errors and 2 consistency errors in table 'COG_SALES_INVENTORY' (object ID 497003811).
CHECKDB found 1 allocation errors and 2 consistency errors in table 'COG_TOTAL_SALES' (object ID 513003868).
CHECKDB found 2 allocation errors and 13 consistency errors in database 'POS_SITE'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (POS_SITE).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Sincerely
Sincerely,
Rene
Monday, November 21, 2011 12:41 PM
1. Can this be done while on production it should be taken offline?
2. I have taken back up. Can these be a prelude to database crash?
3. This is the only error. I found that I know was causing the SQLDUMP.
4. Do I need to patch the server to its latest patch. SQL Server Patch 4?
The database must be in single-user mode to run DBCC CHECKDB...REPAIR_ALLOW_DATA_LOSS. Try running it against a copy of the database (preferably on another server) to make sure the errors are detected and corrected. When repair_allow_data_loss is the minimum repair level, your best action is usually to restore from your last known good backup and perform forward recovery.
Consider applying SP4 after correcting the corruption. I can't say if your problem is due to an issue corrected by a service pack but it's a good practice to stay current anyway.
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
Tuesday, November 22, 2011 5:28 AM
Dan I was successful the last time i placed the database in single mode. It difficult though to return it to multiuser because SPID's are connected to the server. I used the code below to kill existing SPID's before you can set it to multiuser but it is producing an error at KILL @SPId... So I dont like to repeat the adrenaline of returning to that scenario. I will follow your advice and see if it can be resolved in another server. I will keep you posted of the results. Best regards and thank you.. Rene DECLARE @DatabaseName nvarchar(50) DECLARE @SPId int SET @DatabaseName = N'POS_SITE' DECLARE my_cursor CURSOR FAST_FORWARD FOR SELECT SPId FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId OPEN my_cursor FETCH NEXT FROM my_cursor INTO @SPId WHILE @@FETCH_STATUS = 0 BEGIN KILL @SPId --produces an error? but /*you can directly placed the SPId as search by the statement SELECT SPId FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId like KILL 55*/ FETCH NEXT FROM my_cursor INTO @SPId END CLOSE my_cursor DEALLOCATE my_cursor SQL Green Apple
Tuesday, November 22, 2011 7:46 AM
Dan, I done exactly what you told me and the errors were corrected in my test server. 1. I have created a new database on my test server 2. I have restored the backup database from the problem server. using the command RESTORE DATABASE. 3. The restoration went as expected. The restoration is from a production to a test server therefore the server name is renamed. 4. I executed DBCC CHECKDB(Nameofdatabase, NOINDEX), the errors appear as the one in the log file. 5. I executed DBCC CHECKDB(NameOfDatabase, REPAIR_ALLOW_DATA_LOSS) 6. after execution the table errors were gone 7. Executed back step 4 and the errors were not there anymore. 8. Problem is implementing this in the production servers. 9. I have to shutdown production servers in a few hours. I hope it will work tomorow... thanks dan, your a big help...!!! two thumbs up SQL Green Apple