This is a post in our new Case of the Week series — short write-ups from the real SQL Server issues our team of 17 SQL Server experts helps clients solve every day.
Case of the Week: .
Quick Summary
A client reported that their SQL Server service was “randomly shutting down”. They would restart SQL Server – then after some time – the SQL Server engine would just shut down.
Context
This was a SQL Server 2016 environment on-prem – it served various health care records and was a system used for reviewing data, importing batches of data from a few sources, but also a transactional system. Uptime is critical here. This random shutting down was obviously way less than ideal.
The Problem
I actually blogged about this issue earlier this week. And a lot more details are there. Ultimately, the problem was tracked down to an issue with some drivers related to MS Office and the MS Office installation on their server.
The Investigation
Through talking with the client and looking at the error logs, job schedules and activity – we realized that this SQL Server would shut down anytime a job ran. I confirmed this in a maintenance window by running the job myself and proving I could crash the SQL Server each time.
I tried running SSMS as an Administrator – the job would actually run fine for me without crashing the SQL Server – and then when the job would launch from the SQL Agent job schedule, there would not be a crash any longer. Every time SQL was restarted, though, the job would crash the server again, until someone ran SSMS as administrator and ran the job.
We started looking into all the steps in the job, and it turned out that an import was using an Excel spreadsheet. However, at some point, the client had installed MS Office to get a driver on the server, and they were using an OPENROWSET query to access that spreadsheet. And the client had ticked off the “Allow inprocess” box in their troubleshooting and work to make that process work. But that’s where the problem came in.
The Fix
Ultimately, we uninstalled Office, moved over to the ODBC driver to do the imports, and created a linked server to continue their processing – the SQL Server hasn’t stopped (without us telling it to for all of that patching we’ve been having to do lately) since.
Lesson/Takeaway
A few things here stick out:
- Work with your DBA team before making changes to production SQL Servers.
- If you think you have to install Office on a SQL Server to make some import work, you likely aren’t thinking through all of the things you could be thinking through. Consider alternatives.
- Test in dev – and test a lot.
All is good here. And you can read a LOT more about the journey of that weird one in my post on our blog from earlier this week about Openrowset causing the SQL Server service to stop.
The Straight Path Team and Skills
I ran this one by the team in our daily standup meeting where we get to talk about our SQL Server issues and get advice – but ultimately this one was really just a combination of troubleshooting skills, general SQL Server DBA skills, and some of the ETL experience on the team. It seems like a few have been bit by various “oddities” when working with the office drivers and jet drivers.
This post is part of our Case of the Week series — real SQL Server issues and lessons from the field.
If you’re dealing with your own SQL Server mystery and you just want some help from a team who knows how to act cool under pressure – reach out — we love solving these with our clients.
