A client called me this week to help with their BizTalk Server production environment. BizTalk was not running, and they needed to know the reason why. Quickly, while we investigated the issue, we saw on the BizTalk Server administration console the following error:
BizTalk Server cannot access SQL Server. This could be due to one of the following reasons:
- Access permissions have been denied to the current user. Either log on as a user that has been granted permissions to SQL and try again, or grant the current user permission to access SQL Server.
- The SQL Server does not exist, or an invalid database name has been specified. Check the name entered for the SQL Server and database to make sure they are correct as provided during SQL Server installation.
- The SQL Server exists, but is not currently running. Use the Windows Service Control Manager or SQL Enterprise Manager to start SQL Server, and try again.
- A SQL database file with the same name as the specified database already exists in the Microsoft SQL Server data folder.
Internal error from OLEDB provider: “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)” (WinMgmt)
Cause
In this case, the error message clearly specifies perfect paths to troubleshoot and fix the issue. We knew that the first two and the last one didn’t fit our issue because SQL Server exists, and now one has changed access permission.
So, we immediately focus on point number three: The SQL Server exists, but is not currently running. We had the SQL Server Management Console open, and it appeared to be running, but when we checked the services, we realized that the SQL Server (BIZTALK) was not running but Starting.
But any attempt on our part to quickly try to get the service running was futile. Even restarting the machine was unsuccessful.
This SQL Server behavior surprised me – to be clear, at this point, we knew that this was not a BizTalk Server issue but a SQL Server issue that was affecting BizTalk Server – and that forced me to investigate one of the obvious reasons that everyone says they monitor, but… the free space on the hard drive! And guess what? We had 0 free space on C drive.
And that was the main reason for this issue in our case.
Solution
So, to solve this issue, we had to:
- First, of course, the quick win approach was to free some space on the hard drive – we were able to clean 5GB.
- Then, start the SQL Server (BIZTALK) service and dependencies again. After freeing up disk space, we didn’t find any issues in getting this started.
- And, of course, we asked the IT team to increase the C drive with extra disc space.
- Finally, we implemented a monitoring script to notify us about disk space issues: Monitoring disk spaces in your BizTalk environment with PowerShell
Hope you find this helpful! So, if you liked the content or found it useful and want to help me write more, you can buy (or help me buy) my son a Star Wars Lego!
