Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1043
  • Last Modified:

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

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
mangamonster
Asked:
mangamonster
  • 11
  • 7
  • 5
  • +1
1 Solution
 
ZabagaRCommented:
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
 
Jim P.Commented:
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
 
mangamonsterAuthor Commented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
mangamonsterAuthor Commented:
That did the trick!! Thank you!!
0
 
mangamonsterAuthor Commented:
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
 
mangamonsterAuthor Commented:
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
 
Jim P.Commented:
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
 
mangamonsterAuthor Commented:
Thanks, Jim! Does the /ST 08:50:00 time mean it will begin next day at 8:50am?
0
 
Jim P.Commented:
/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
 
mangamonsterAuthor Commented:
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
 
Jim P.Commented:
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
 
mangamonsterAuthor Commented:
Hmm, still no luck. Is the date required? Also, what is the /q switch for?
0
 
Jim P.Commented:
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
 
Anthony PerkinsCommented:
Have you considered configuring SQL Server so the the minidump is created in a drive with more space?
0
 
mangamonsterAuthor Commented:
Anthony - I'd like to give that a shot! Any recommendations on how to set that up?
0
 
Anthony PerkinsCommented:
Are you familiar with the SQL Server Configuration Manager?
0
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
0
 
Anthony PerkinsCommented:
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
 
mangamonsterAuthor Commented:
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
 
Jim P.Commented:
Just dump it to the D: drive and then monitor it until you finally shut it down.
0
 
mangamonsterAuthor Commented:
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
 
Jim P.Commented:
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
 
mangamonsterAuthor Commented:
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 11
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now