Category: Administration

Michal Sadowski walks us through unattended installations:

Imagine a situation when you are tasked to install a dozen SQL Server instances. Using an approach described in previous blog post  installation can take a significant amount of resources and time, also there is a probability of errors due to typos (e.g. in directory paths, etc.)
There are two options that can be used for the installation:
1. Using ConfigurationFile.ini
2. Using command line parameters to setup.exe file
Below there are step-by-step instructions on how to install SQL Server using both options.

If you only have a couple of SQL Server instances in your environment, unattended installation may seem a bit overwhelming. But as you have to deal with larger and larger numbers of instances, it’s the only way to keep your sanity. It’s also the only way to install on Windows Server Core.

Jamie Wick has a couple of scripts to help us monitor virtual log file counts for our transaction logs:

The LOGINFO command returns a row for each Virtual Log File in the Transaction Log. The FileSize column shows the size (KB) of each individual VLF. The amount of the transaction log that is currently in use can be determined by the number rows with a Status of 2 (0 = Inactive, 2 = Active & 4 = VLF exists on the Primary replica, but not on the Secondary replica).
One of the biggest problems with the LOGINFO command has been the difficulty of extrapolating the information to include all databases on the server, for use in reports, automated alerts and maintenance processes. Beginning with SQL Server 2016 SP2, there are 2 new Dynamic Management Views (DMVs) that can be used for monitoring the VLF information: sys.dm_db_log_info & sys.dm_db_log_stats.

A huge number of VLFs can be a hidden performance sink.

Jason Brimhall has a three-part series on the types of monitoring built into SQL Server. Part one is an overview and includes the Default Trace:

The default trace by itself is something that can be turned off via configuration option. There may be good reason to disable the default trace. Before disabling the default trace, please consider the following that can be captured via the default trace. I will use a query to demonstrate the events and categories that are configured for capture in the default trace.

Part two looks at the system_health Extended Event session:

Beyond being a component of the black box for SQL Server, what exactly is this event session? The system_health is much as the name implies – it is a “trace” that attempts to gather information about various events that may affect the overall health of the SQL Server instance.
The event session will trap various events related to deadlocks, waits, clr, memory, schedulers, and reported errors. To get a better grasp of this, let’s take a look at the event session makeup based on the available metadata in the dmvs and catalog views.

Part three is the sp_server_diagnostics stored procedure:

Beyond being a component of the black box for SQL Server, what exactly is this diagnostics process? The sp_server_diagnostics is much as the name implies—it is a “diagnostics” service that attempts to gather information about various events that may affect the overall health of the SQL Server instance.
The diagnostics process will trap various server related health (diagnostics) information related to the SQL Server instance in an effort to try and detect potential failures and errors. This diagnostics session/process traps information for five different categories by default. There is a sixth category of information for those special servers that happen to be running an Availability Group.

I’ve used the first two but did not know about the third. Jason goes into good depth on each, showing you the types of information you can get out of these. Read the whole thing.

Steve Jones walks through several reasons why you might see gaps in identity columns:

Deleting Rows
This is noted in the tweet as a cause, but let’s test this.
One of the common ways that we get gaps in identity values is when rows are deleted. Let’s remove the row with Steve in it.

Steve explains a few others, but even that’s not complete: identity columns can jump after the service restarts as well.

In short, please do not use identity values in cases where you need to guarantee sequentiality (like check numbers or invoice numbers). Don’t use sequences either, as they’ll behave similarly.

Jeff Mlakar walks us through a few tools for troubleshooting network connectivity solely from the command line:

NSLOOKUP
The nslookup command can check the name which an IP address will resolve to or which IP address resolves to a name (aka reverse lookup). This can be done either way as shown:

After having spent the long weekend futzing with Server Core instances for an upcoming project, I can also recommend learning the Powershell tools as well.

Hamish Watson walks us through a weird scenario:

Looking at the event logs I (eventually) found this:
Event ID 7000:
The MSSQLSERVER service failed to start due to the following error: 
The account name is invalid or does not exist, or the password is invalid for the account name specified.
Which is bizarre – as the service account had been used for months – but after each reboot the services had to be manually started. GPO and other things had been blamed but no one could actually find out why.

I’ve never used @ naming for service accounts, so until I read this I didn’t even know it was possible.  Now I’m going to forget again because apparently it’s not a good idea.