August 28, 2013 at 9:05 am
I am going to preface this post by saying this solution is TEMPORARY and for emergency situations on production servers. many will disagree with even using this but trust me there are circumstances where you need this...I know because I had a situation that necessitated it......
Some background......
Recently my company moved from a single on site data center to a dual remote data center environment. This meant i had several SQL clusters to move with some mirroring, etc. A typical farm in a typical environment if typical can be said of anything that is. In the process several of my servers were shut down less than gracefully. In fact one server was bounced back and forth between cluster nodes 10 times or more before someone who knew what they were doing shut it down correctly. this server came up with db's in emergency mode and all sorts of problems. I finally did get all teh databases to a point where I could select data from them. These were SharePoint databases on this instances. There are roughly 40 databases and SharePoint was displaying pages and seemed happy.
So I wiped the sweat from my forehead and gave a quick "thank God!" adn went about my day only to be called in the middle of the night because SharePoint would not allow documents to be uploaded. What I said? Why? The drives had multiple 100's of gigs free and we expand roughly 1 gig per day.
The issue was that the SQL server was saving dumps to the log drive and overnight had created almost 150 gigs of txt files filling up the whole drive. No this is a production system that was otherwise working. Clearly one of the databases has issues. I need to run some profiles, take them offline, do restores, and possible many other things that would take SharePoint out of service.
Ever tried to convince your director that an emergency outage is needed when the service itself is up and reachable? Well lets just say he wasn't to keen on the idea of doing it outside a normal maintenance window. What do I do now? The databases are usable in some fashion because SharePoint is up. No data corruption seems to be taking place as everything put in SharePoint is retrievable and all the services are up.Do I just keep going in to delete logs every 15 minutes until the next window in 2 weeks?
This solution is NOT the way to go about doing this long term BUT if you need to get through a week or two until a maintenance window the attached program (with source code) will watch any director for any particular file extension(or all files). When it sees a file being created that matches your criteria (for me it was log, mdmp, and txt files), the file will be automatically deleted. Not sent to the recycle bin btw but completely deleted. This will keep your drive from filling up.
If anyone knows how to disable these dumps or move the directory without interrupting the service please do reply with how. I couldn't find a way personally though so I came up with this temporary solution and I'm sharing it with my favorite community. Again...this is NOT a permanent solution BUT it will get you by.....
The source code and a compiled binary are in the attached zip file. Its written in c# and requires that .NET 2.0 or above be installed on the server. Its a PE so there is nothing to install. It can also be used remotely if you give it a UNC path to the directory you want to watch and its accessible. Here is an example of how it is used:
Watcher.exe D:\MSSQL10_50.MSSQLSERVER\MSSQL\Log, mdmp;txt;log
^ A Comma separates the path from extensions. A semicolon delineates the different extensions to look for.
These are the appropriate cmd line args if you are using it for the same purpose I did. Otherwisde it can be used to watch any directory for any files too so maybe it could come in handy for other uses.....
Thanks
August 28, 2013 at 11:25 am
Just a suggestion, you should start a blog or write an article for SQL Server Central on something like this. You can get a lot more hits that way rather than posting to the forums where most people expect to find questions and answers, not just interesting answers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 28, 2013 at 2:03 pm
Good point and true. I was just posting this quickly to share. I really didn't have the time for much more and certainly not to elaborate to that extent. If this helps people then that's great. If it doesn't because people didn't see it well.....we will just have to rely on Google for that. Besides this isn't something I would write up as a standard approach or appropriate even and people (newbies) might take it that way. Its just what the title says. "To get you by" in a real pinch.
Thanks though.....
August 28, 2013 at 4:44 pm
This would make an awesome "Spackle" article especially if you explained how the code works a bit.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply