Tuesday, August 25, 2015

Error 1814 - Could not Start SQL Server Services

Hey Guys,

Some of you might be very well familiar with the below error. Let's see under what circumstances I've faced this error.

Few months back while I was configuring "AlwaysON" on one of the Server, something went wrong and due to which I've to completely decommission the AlwaysON configuration along with Windows Clustering.

After doing this, I restated the server and when the server was up what I saw was the SQL Server service was disabled, tried starting the same through Configuration Manager but hard luck I was facing the below error message.

Error 1814
Error 1814
Let's have a look below into the SQL Server Error log what its was it looking like:

Error Log
Error Log
With these error logs we started troubleshooting:
  1. The error in the SQL Error Log states that due to insufficient disk space "TempDB" was not created.
  2. Now what we were wondering about the error, as it was a new Server and disk was having sufficient space. So insufficient space was out of question.
  3. Just to cross verify I open the My Computer tab and what I saw was only C: drive was reflecting and rest of the drive were not visible.
  4. While installation I have kept the TempDB in D: drive.
  5. Now here comes the twist, let's recollect the above activity of AlwaysON which I was performing. But for some reason I have to decommission it.
  6. During decommissioning I've put the disks into offline state and due to which the disk were not accessible.
  7. So at start of SQL Server if the TempDB is not created, the SQL Server Services will not start.
Brief note on System Databases:

  1. Master, Model, MSDB and TempDB are system databases.
  2. Files of the Master Database is used in start up parameter during the SQL Server instance starts. 
  3. So if the Master Database is corrupted SQL Server instance will not start. 
  4. Also as it's one of the important properties is it stores the location information for other Database. Therefore Master Database is said to be the heart of SQL Server.
  5. Model Database acts as a template for the other User Database as well as TempDB while creation.
  6. So even if Model Database is corrupted SQL Server instances will not start. As indirectly TempDB will not start and if TempDB will not start SQL Server instance will not start.  
So guys System Database plays an very important role in proper functioning of SQL Server Instance.

Here my question is: What if MSDB is corrupted, will the SQL Server Instance will start? You can comment below.

Thank You Guys.
Keep Learning and Enjoy Learning!!!