Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-01-22
25
Medium Priority
?
1,033 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
  • 5
  • +1
25 Comments
 
LVL 15

Accepted Solution

by:
ZabagaR earned 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

722 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