SQL SERVER – How to Auto Start SQL Followed by a Crash?

Sometimes I do get some quick questions from my existing clients. I try to help them as much as possible. Most of the time, I provide them link to my blog or someone else’s blog. If there is no relevant information based on my search, then I provide them relevant information and write a blog for future reference. This blog is an outcome of one such email communication. Here is the short question about auto start SQL.

SQL SERVER - How to Auto Start SQL Followed by a Crash? autostart-800x268

“Hi Pinal,
We are seeing our SQL Server getting crashed randomly and goes down. Is there any way we can configure it to start automatically?”

Here was my long answer.

Solarwinds

There are two possible crashes which can happen on SQL Server running on windows operating system.

  1. Operating system crash causing the machine to restart.
  2. SQL Server crash.

Here are the solutions for both the situations.

SOLUTION # 1

If we want to get SQL Server automatically started after restarting the operating system, then you can use SQL Server Configuration manager.

  1. In SQL Server Configuration Manager, click on SQL Server Services.
  2. In the details pane, right-click the name of the instance and then click Properties.
  3. Go to “Service” tab and set Start Mode to Automatic.

SQL SERVER - How to Auto Start SQL Followed by a Crash? auto-start-01

SOLUTION # 2

There could be situations where SQL Server is shutting down automatically due to a crash or an unexpected behavior with SQL Server. In some situation, we see errors like below in application event log.

  1. Event Viewer (Application Error)

Faulting application name: sqlservr.exe, version: 2011.110.2100.60, time stamp: 0x4f36496e
Faulting module name: ntdll.dll, version: 6.1.7601.18247, time stamp: 0x521eaf24
Exception code: 0xc0000374
Fault offset: 0x00000000000c4102
Faulting process id: 0x155c
Faulting application start time: 0x01d08bfa7cdf5078
Faulting application path: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
Faulting module path: C:\Windows\SYSTEM32tdll.dll

  1. Event Viewer (Windows Error Reporting)

Fault bucket , type 0
Event Name: APPCRASH
Response: Not available
Cab Id: 0
Problem signature:
P1: sqlservr.exe
P2: 2011.110.2100.60
P3: 4f36496e
P4: StackHash_c079
P5: 6.1.7601.18247
P6: 521eaf24
P7: c0000374
P8: 00000000000c4102
P9:
P10:

Here are the settings, which can help in forcing SQL to restart in such unforeseen event.

  1. Setting of SQL Server Agent via SQL Server Management Studio.
    SQL SERVER - How to Auto Start SQL Followed by a Crash? auto-start-02
  2. Another possible setting is part of the operating system. Here are the steps
    1. Go to “Start > Run > Services.msc”
    2. Locate SQL Server service which we want to configure to handle a crash situation.
    3. Go to Properties of that service and go to “Recovery” tab and choose options shown below.
      SQL SERVER - How to Auto Start SQL Followed by a Crash? auto-start-03

Have you implemented any of above?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – How to Remove TempDB File?
Next Post
SQL SERVER- New DMF in SQL Server 2017 – sys.dm_os_file_exists. A Replacement of xp_fileexist

Related Posts

1 Comment. Leave new

  • Good morning Pinal. Just a quick comment on the title of this post. I am sure what you actually mean is “How to Auto Start SQL Following a Crash”. (Followed by) would suggest that the server would end up in a boot-crash loop.
    Anyway… Awesome work as usual.

    Reply

Leave a Reply

Menu