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.

SSMS SQL Server cmdlets

In SQL Server Management Studio 17.0 the PowerShell module was moved into the PowerShell gallery.
 
This means that you may see the following:
image1
image2
When you go to https://www.powershellgallery.com/ you currently see:
image3
I don’t know about you, but it suddenly seems that a simple tweak to SSMS has become a headache for DBAs.
 
Performing a search gives 4 results and it looks like the matteot_msft is the correct one. Having company package under a users name doesn’t sound like a good idea to me, possibly this should be a team account?
image4
So how do you download it?
 
image5
lol mind blown, KISS appears to have gone out the window :)
 
If you’ve made it this far and have an internet connection the supplied method is:
Inspect

Save-Module -Name SqlServer -Path

Install

Install-Module -Name SqlServer

 
Lets give it a try:
 
It’s now requesting NuGet provider version ‘2.8.5.201’ or newer
image6
Once completed you’ll see the folder and files
image7_1
For the next step, I received the following message which requested for -Force to be used:
image9
image10
Once completed, the SSMS error message no longer occurs
image11

Using Tail with PowerShell

Yes from PowerShell 3.0 there is the ability to use the Tail parameter in Get-Content.
 
Details can be found here.
 
Say you wanted to look at a SQL 2016 default instance errorlog file based on a D drive you could use:

Get-Content  "D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG" -Tail 10 –Wait

image1

SQL Server Mobile Report Publisher install

If you come across the following error when attempting to install SQL Server Mobile Report Publisher
image1
you’ll likely find that the currently link doesn’t work
image2
At this time the requirement can be found here. Note: After the install you may be requested for the server to be restarted. You don’t have to, but it’s a good idea to do so.
 
The install then should go smoothly.
image3
Given that Visual Studio 2017 is now out, we’ll hopefully be seeing this legacy 2013 requirement removed.

View top 5 rows in all views

A few years ago I did a blog on viewing sample rows in all used tables.
 
Here is another similar query which allows you to peek at the top 5 rows in each view.

SELECT 'SELECT TOP 5 ''[' + SCHEMA_NAME([schema_id]) + '].[' + [name] + ']'' AS view_name, * FROM [' + DB_NAME() + '].[' + SCHEMA_NAME([schema_id])+ '].[' + [name] + '] WITH (NOLOCK);'  
FROM sys.all_views
ORDER BY [name];

image1
If you are using Azure SQL Database, then use the following which removes the use of the NOLOCK hint:

SELECT 'SELECT TOP 5 ''[' + SCHEMA_NAME([schema_id]) + '].[' + [name] + ']'' AS view_name, * FROM [' + DB_NAME() + '].[' + SCHEMA_NAME([schema_id])+ '].[' + [name] + '];'  
FROM sys.all_views
ORDER BY [name];

Dubious default settings

The installation process of SQL Server is continually evolving.
 
Recently the setup allowed tempdb file adjustments with a default of 4 files and the ability to grant the Perform Volume Maintenance Task privilege.
 
Such a focus on tempdb was a surprise to me as in the 13+ years I’ve worked with SQL Server (tackling literally tens of thousands of instances) there was only once when more than 1 data file was required. To put this in perspective, the SQL Server was accessed by all students at a large university and the tempdb GAM contention was obviously clear. Adding another single data file (2 in total) to this high spec server resolved the issue.
 
Adding additional tempdb files, to me is an advanced feature and should be carefully considered so as to not waste disk space, ensure even file growths, minimize complexity etc.
 
In my view it would have been more productive for Microsoft to have focused the setup on the following:

  • set memory limits (this has to be done 100% of the time) and don’t stop with the engine, include SSAS and SSRS
  • set max degree of parallelism (I believe the default of 0 is incorrect, rather it should be 1 as most instances are not dedicated to a hosting a single application due to licensing costs,consolidation etc)
  • set backup compression (should be defaulted to 1)
  • set optimize for ad hoc workloads (should be defaulted to 1)
  • allow setting Perform Volume Maintenance Tasks (tick)
  • allow setting Lock Pages in Memory
  • allow enabling remote admin connections
  • allow setting the listening port and adding the appropriate firewall entry (this would likely save thousands of can’t connect issues)