Sql-server – How to restart MS SQL server right, what is best practice to do that

best practicesrecoverysql serversql server 2014

I`m quite new to DB administration.

I have a problem when I restart SQL server, one of the databases goes into Recovery mode and automatically repairs the DB in 30 min. I don't know why this happens.

Here is what I did, but it seems that not enough:

I have SQL Server Microsoft SQL Server 2014 (SP2-GDR).

  1. install Windows updates
  2. SQL: shrink all DB and logs files
  3. SQL: killed all Runtime Brocker`oв
  4. SQL: stopped SQL agent
  5. SQL: stopped SQL server
  6. Restart Windows Server.

UPDATED :

1) How big is the database?

4.5 GB + 400MB Logs

2) Why are you restarting the instance?
Install Windows updates, and also apps that connect to that SQL server become slower and slower with time. We restart the server once a month usually. It takes 15 minutes to start, but this time it took 45 min (while recovering DB).

3) Is this a server or a pseudo-server (desktop running Server OS)?

its virtual machine. Running on server hardware.

4) Is the Server configured for application or background processes?

The server runs SQL and additional background apps/ web services

5) How much RAM does the server have? How many processors?

16 GB RAM
4 CPU

6) SAN storage or local disks? …. The more details you provide the better we can provide you with an adequate answer.

We buy hosting as service, can`t tell exact hardware architecture. But we have SSD disk (where SQL DB stored).

7) 30 Minutes might well be the best SQL Server can do. It depends on a lot of factors.

It usually takes up to 15 min. I think I miss some steps that I need to do to close all DB in right way.

Best Answer

You almost certainly have a very large number of virtual log files (VLFs) in the problematic log file of the database in question. Shutting down and restarting SQL Server causes the recovery process to run on all databases on the instance. For databases that have a very large number of VLFs this process can be agonizingly slow.

You can see the VLFs via this DBCC command:

DBCC LOGINFO;

Each row represents one VLF. How many do you have? I'm betting you have a very high number of VLFs (rows).

You can rebuild the log file to remove the excess VLFs; simply shrink the log file, then grow it in sensible increments to the prior size.

I wrote a blog post showing how to do that here.

If you're concerned about shutting down the SQL Server instance without causing recovery to run, you can issue this command before you shut down SQL Server:

shutdown;

However, if there are existing connections, the command will wait until all connections are closed. See the Microsoft docs for more detail. SHUTDOWN WITH NOWAIT; will cause SQL Server to immediately shutdown, without waiting for existing connections to close, however this will trigger recovery at the next startup, and is exactly what happens when you shutdown the instance via the control panel, or via shutting down the server itself.

Related Question