SSMS AutoRecover Location

By default, SSMS saves your queries every 5 minutes and keeps the info for 7 days.
 
I suspect this has saved many a DBA when unforeseen outages occur.
image1image2
One quick way to get to this recovery location is shown below and is by right clicking on a Query tab:
image3image4
As you may have noticed, this provides a quick access method to your hidden AppData folder.

Starting the SQL Agent from the command line

If you’ve attempted to start the SQL Agent from the command prompt you likely would have received the following message:
 
image1
It is possible to run the Agent, you just require arguments i.e.

  • -c which allows you to use the command prompt, so always required
  • -v optional verbose mode which includes the standard SQL Server Agent error log entries
  • -iinstancename if you are using a named instance

Below shows with -c used for a default instance
 
image2
Yes there will probably be times when you’ll need to use the above to troubleshoot Agent issues.
 
CTRL + C will stop the Agent.

SQL Server system database templates

If for some reason you need to restore the system databases, you’ll typically find copies of the original system databases in the …\Binn\Templates folder.
 
Below shows the file on a SQL Server 2016 default instance installation:
image1
Details on how to make use of them is found here

Pausing the SQL Server service

The Pause option on the SQL Server service account prevents new connections, but allows existing connections to continue operating.
 
image1
image2
When would you use this? Most likely during a patching or outage cycle to allow existing users to gracefully exit while preventing new connections.

Remote shutdown

The shutdown /i command has come in handy many a time and especially when you find that a server just doesn’t appear to be restarting and you’re unable to connect to it.
 
image1
So if you find yourself in that situation, give this a try and see if it help to resolve the issue.

tempdb AUTOGROW_ALL_FILES

In case you missed it, AUTOGROW_ALL_FILES was added in SQL Server 2016 and tempdb has this enabled by default.
 
Further info is mentioned here.

EXEC sp_MSforeachdb '
USE [?];
SELECT
DB_NAME() AS database_name
,[name]
,type_desc
,CASE WHEN is_autogrow_all_files = 0 THEN ''no'' ELSE ''yes''
 END AS is_autogrow_all_files
FROM sys.filegroups';

image1
What this means is that you should be extra careful that you don’t run out of disk space when suddenly all files need to grow.