MSSQL server: Stop/Start MSSQL Instance options (SQL Server Configuration Manager & Sql Server Management Studio)

Objective: How to stop/start MSSQL instance

Server: Windows 2022

SQL Server: 2019 (version 15)

We have 2 (possibly more), options as far as I have learnt...

1. Using SQL Server configuration Manager

2. Using SSMS (SQL Server Management Studio)

Option 1 - Restart MSSQL using SQL Server Configuration Manager:

a. Go to start (if you already have a shortcut, then use it like below) & click on the SQL Server 2019 Configuration Manager

b. Look at the current state of the SQL Server (Notice the SQL Instance name: MSSQLSERVER, in case you have multiple instances - ensure to choose the right one).

c. Then perform PAUSE > STOP > START or STOP > START action as needed.

This is what Microsoft has to say about stop/start/pause/restart options...

Start - Will start the SQL Server service & accept new connections

Stop - Will stop the SQL Server service

Restart - Will stop/start the SQL Server service

Pause - Will make the service stop accepting new connections & let existing connection continue to use the service until their connection is either broken or closed.

Resume - Will allow the service to accept new connections as well.

d. I have decided to STOP > START the MSSQL service here

You see after the service is started, we didnt select any service; hence the action tab just vanished.

Anyway this is how the sql server needs to be stopped and started.

To check if the restart was success, we can try make a connection using SSMS and also check the db alert log from SSMS

Option 2 - Restart the MSSQL server using SSMS:

Goto Start > SSMS (in search) >  you will see the SQL Server Management Studio 19 (latest) popping up

Open the application & connect to the db as sa (system administrator) - note I havent setup any additional roles, so I am proceeding with the default sa account.

Notice below after we click connect, we are connected. The connection details are highlighted below with host to which we are connected and the user who we connected

So a successful connection means - we performed first validation

Go to Management > SQL Server Logs > Current - <date> log

This will open a new window called "Log File Viewer". You can notice a message saying db is ready to accept connection like ... "2023-04-08 15:04:44.10 spid21s     SQL Server is now ready for client connections. This is an informational message; no user action is required."

I looked @ compmgmt.msc (event viewer for app/system etc.. this type of messaging isnt available). I used process explorer and process monitor to see what the application is to open it independently without connection to db; but it is a dll

Anyway this is 1 method. 2nd method is more or less the same. Just using SSMS (SQL Server Management Studio). You can see below it is the same setup of option from the drop down. But remember we are using db credential this time which is 'sa'.

As soon as you click STOP, you get the below message...

Once you stop the db, you see START only option now to start the database.

When we click on START..

The db starts fine..

You now see 3 options as to pause/stop/restart...

As usual we verify the log and ensure the db restart was successful.

Please Note: In case you have stopped the MSSQL and disconnected from the SSMS. It isnt possible to connect to the MSSQL from SSMS later either using windows authentication or SQL Server authentications. It will fail saying the sql server connection failed (pipe broken sort of message) and authentication wont happen. 

In this case, please go to SQL server configuration manager to start the database (using the play button).

Thanks for visiting the blog.