Solved

SQL 2005 minidump errors is crashing my server - can I disable this or change the log location?

Posted on 2014-01-22
25
927 Views
Last Modified: 2014-01-28
Hello,

We are struggling with multiple issues with SQL 2005 Ent (retail) over the past couple years. Aboutl a month ago it started to impact users and causing the server to crash. Currently, we only use SQL for Sharepoint. We plan to migrate over to Sharepoint 2013/SQL 2012 in the 2nd QTR of this year, but in the meantime, we are holding up SQL 2005 with 'tape and shoestring'.

 We currently have SP 2013 / SQL 2012 running in a production environment as a reporting server for a Business Intelligence project we are workign on.

The main issue we're experiencing are minidump errors which is eating up about 40GB of disk space every two hours. The logs are written constantly, multiple times per second. I must clean out the log folder every 2 hours to maintain server uptime. After work hours, I cannot do this around the clock (I am the sole server/network/infrastructure support person for our business), so eventually the server crashes due to lack of disk space. SQL 2005 is installed on Win Server 2008 R2 Ent.

 I found that every single log file points to this error:

  Exception Address = 01105772 Module(sqlservr+00105772)
  Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION

After some research, a few articles pointed out that this particular error points to a bug in SQL 2005 Ent (retail) that requires installing SP1 or later to resolve the issue. Since the error began, we have had SP3 installed, so now the issue appears unrelated to the 'fix' posted on various articles.

We had a contractor look at our output logs from both SQL and windows debugger output for them to further look into the problem. Their final suggestion was to upgrade SQL to 2008 R2 or newer to resolve the issue. Reasoning for this was due to MS no longer supporting SQL 2005. My issue with this is that we have already purchased SQL 2012 which is in production on another server, and we plan to eventually migrate to that server in several months, so a $25K band-aid is crazy...small business, small budget.

My question to all of you is if (1) I can somehow disable the log folder from being written to? Or, (2) can I limit the amount of space it can write to? Right now it's pretty much unlimited and it will continue writing on our C: until there is no longer any space. This happens every single night. OR, (3) can I simply change the location of where the log files are written? Such as a external hard drive. That way, when it runs out of space, it simply stops writing? OR, (4) can I slow down the minidump writes? If I only had to clear out the folder every 4 or 5 days, this would be manageable and would provide 24hr uptime of services, as long as I maintain the folder cleaning.

Any help or suggestions at all would be greatly appreciated. This is impacting critical functions of our business, as well as many of our users. My main concern is to have the server stop crashing every day until we can work on the migration, which is in 2-3 months. I would rather have a temporary fix or work around, rather than having to re-install SQL completely and restore all of the data and reconfigure everything.

Thank you for taking the time to read this, and I dearly hope that someone has an answer for me.

-David
0
Comment
Question by:mangamonster
  • 11
  • 7
  • 5
  • +1
25 Comments
 
LVL 15

Accepted Solution

by:
ZabagaR earned 500 total points
ID: 39800351
Edit the section of the config file mentioned in this article

http://support.microsoft.com/kb/919711

In the "exception" section you'll set this value to a 0

<CreateAndSendCrashReports>0</CreateAndSendCrashReports>
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39801866
ZabagaR probably has the best solution.

But a sloppy one that might work. Open a command prompt as an Admin. Then get to the path where the dumps are going. Type in edit to open the old DOS text editor and then hit <Alt>, F, X to quit it.

That will change the path from something like C:\Program Files\Microsoft\Search Enhancement Pack to C:\PROGRA~1\MICROS~2\SEARCH~1.

Then change to that path in the code below.
SCHTASKS /Create /RU SYSTEM /SC MINUTE /MO 20 /TN DelDmps /TR "DEL C:\PROGRA~1\MICS~4\*.* /s /q /f"  /SD %date:~4,10% /ST 08:50:00

Open in new window

This will create a scheduled task that runs every 20 minutes to delete all files in the output path.

Just a thought.
0
 

Author Comment

by:mangamonster
ID: 39812314
Thanks guys for the quick responses. Unfortunately, I was out due to family emergency but I did get a little time to try ZabagaR's suggestion. I changed the CreateAndSendCrashReports to 0 as you suggested last week, but it seems that the same dumpfiles were still be written. For safe measure, I tried it again this morning and voila! It's no longer generating dumpfiles...this is AWESOME news for us and I hope that it holds us over until we migrate to Sharepoint 2013. Again, thanks so much ZabagaR for following up so quickly. This may just have saved us a thousands of dollars in unnecessary costs!
0
 

Author Closing Comment

by:mangamonster
ID: 39812319
That did the trick!! Thank you!!
0
 

Author Comment

by:mangamonster
ID: 39812531
Ack! After rebooting, it came back. I double checked the ini file and it is still set to '0'. Hmm. Jim P., would you mind giving me a little more detail on the fix you suggested. I'm lost at the path 'type in edit' section.
0
 

Author Comment

by:mangamonster
ID: 39812742
OK - I think I see the problem here with ZabagaR's solution. The msmdsrv.ini file point to a different log path. Here is the configuration for that path:

<ConfigurationSettings>
      <DataDir>C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Data</DataDir>
      <LogDir>C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Log</LogDir>
      <BackupDir>C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Backup</BackupDir>
      <AllowedBrowsingFolders>C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Log|C:\Program Files\Microsoft SQL Server

The LogDir that is generating dump files is:
C:\Program Files (x86)\Microsoft Office Servers\12.0\Data\MSSQL.5\MSSQL\LOG

Is it ok for me to change the LogDir to the correct folder, or will this cause any issues?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39813217
Either path should work.

So you would type in the C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP then do the Edit. That will bring a path back like C:\PROGRA~1\MI3EDC~1\MSSQL.3\OLAP. That short path will work without quotes.
0
 

Author Comment

by:mangamonster
ID: 39813265
Thanks, Jim! Does the /ST 08:50:00 time mean it will begin next day at 8:50am?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39813302
/ST 08:50:00 time mean it will begin next day at 8:50am?

It will start on the current day. I just picked 8:50 because I made the assumption that you probably would not get it done until some time after that on any given day.
0
 

Author Comment

by:mangamonster
ID: 39813374
Hmm, so I've successfully created the task as follows:

SCHTASKS /Create /RU SYSTEM /SC MINUTE /MO 10 /TN DelDmps /TR "DEL C:\Program Files (x86)\Microsoft Office Servers\12.0\Data\MSSQL.5\MSSQL\LOG /s /q /f"  /SD %date:~4,10% /ST 08:50:00

It's been over 10 minutes but the folder files are not being deleted. Any ideas?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39813510
Try it like this:

SCHTASKS /Create /RU SYSTEM /SC MINUTE /MO 10 /TN DelDmps /TR "DEL C:\Program Files (x86)\Microsoft Office Servers\12.0\Data\MSSQL.5\MSSQL\LOG\*.* /s /q /f"  /SD %date:~4,10% /ST 08:50:00
0
 

Author Comment

by:mangamonster
ID: 39813603
Hmm, still no luck. Is the date required? Also, what is the /q switch for?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 38

Expert Comment

by:Jim P.
ID: 39813682
The long path with the spaces is probably the issue.

The DEL context is

DEL <path/file(s)

/s = subdirectory
/Q = Quiet --no confirmation
/F = Force
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39813919
Have you considered configuring SQL Server so the the minidump is created in a drive with more space?
0
 

Author Comment

by:mangamonster
ID: 39813931
Anthony - I'd like to give that a shot! Any recommendations on how to set that up?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39813954
Are you familiar with the SQL Server Configuration Manager?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39813959
If you are then you can use it to set the path to the Dump Directory.  As in:
1. Click on SQL Server Services on the Left pane.
2. Double click on SQL Server (MSSQLSERVER) on the right pane.
3. Click on the Advanced tab,
4. Enter a valid folder on a local drive in the "Dump Directory" option.

You may need to restart the SQL Server service.  Not sure.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39813960
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39813966
P.S.  The comment you accepted (http:#a39800351) was for article that had to do with SSAS and not SQL Server.  The "OLAP" folder should have been a dead giveaway.
0
 

Author Comment

by:mangamonster
ID: 39815386
How safe is using partition magic on this production server? There are only two partitions and the D: has plenty of space. I would like to shrink it and create a new partition, but wasn't sure if any of you had experience doing this. The D: has some databases on it as well, but very little space is being used.

Also, I was thinking. What if I create a fake drive and simply change the dump dir to a path that doesnt exist?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39815410
Just dump it to the D: drive and then monitor it until you finally shut it down.
0
 

Author Comment

by:mangamonster
ID: 39815432
Hmm, well, it's writing about 40GB of dumpfiles approx every 2 hours. I have about 500GB avail on D:.  I would still probably have to clean out the drive every 6-7 hours before I run out of disk space.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39815485
Well we can go back to the delete process.

If you redirect it to the simple folder like D:\MiniDump then the task would look like

SCHTASKS /Create /RU SYSTEM /SC MINUTE /MO 10 /TN DelDmps /TR "DEL D:\MiniDump\*.* /s /q /f"  /SD %date:~4,10% /ST 08:50:00

Open in new window

0
 

Author Comment

by:mangamonster
ID: 39815595
ok, the good news is that I went ahead and shrunk the D: as I realized the databases  running off it were no longer in production. It won't affect the Sharepoint data, which was my main concern. I'm going to monitor it now and hopefully everything is fine. Thanks, Jim!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now