Press "Enter" to skip to content

Category: Administration

Indirect Checkpoint

Published 2016-06-20 by Kevin Feasel

Kendra Little notes that indirect checkpointing is now the default in SQL Server 2016:

SQL Server 2016 introduces big new features, but it also includes small improvements as well. Many of these features are described in the “It Just Runs Faster” series of blog posts by Bob Ward and Bob Dorr.

One article in this series explained that new databases created in SQL Server 2016 will use “Indirect Checkpoint” by default. Indirect checkpoint was added in SQL Server 2012, but has not previously been enabled by default for new databases. The article emphasizes this point:

Check out the comments as well.

Comments closed

ElasticSearch Production Checklist

Published 2016-06-20 by Kevin Feasel

Darren Perucci has a checklist for spinning up a production ElasticSearch environment:

When you are looking for a node or a cluster, it is a good idea to have a name which describes what you will need to find and separates one from another.

The default cluster name of “elasticsearch ” could allow any nodes to join the cluster, even if this was not intended. Thus, it is a good idea to give the cluster a distinct identifier instead.

There are a few things to think about in this post.

Comments closed

Create An SSIS Catalog

Published 2016-06-17 by Kevin Feasel

Andy Leonard shows how to create an SSIS catalog:

Check the “Enable CLR Integration” checkbox to enable the other controls on the form.

I recommend you also check the “Enable automatic execution of Integration Services stored procedure at SQL Server startup” checkbox. This feature causes a stored procedure to execute whenever SQL Server starts. The stored procedure will identify any SSIS packages in a running (or other “active”) status and mark them as “Ended Unexpectedly.” You want this. Trust me. (As my friend Kevin Boles (LinkedIn | @thesqlguru) says, “Push the trust me button and let’s move on,” (paraphrased).

You cannot alter the name of the SSIS Catalog database. It is SSISDB. And, as in Highlander, there can be only one SSIS Catalog per instance of SQL Server.

This post is full of helpful notes if you’ve never used the SSISDB database before.

Comments closed

Finding File Growth

Published 2016-06-15 by Kevin Feasel

Andy Galbraith shows how to use extended events to track file growth events:

…but what does this have to do with Extended Events?

As I started out, I was looking for something slick I do with a trace that I could replicate in Extended Events, and this was a great candidate.

—

The catch as I discovered, is that while file growths are captured in the default trace, they are *not* in the system health session…

Fortunately, you can create your own XEvent and Andy gives us the details.

Comments closed

Database Scoped Configurations

Published 2016-06-14 by Kevin Feasel

Niko Neugebauer looks at a new feature in SQL Server 2016:

In the first version/iteration of the Database Scoped Configurations, there are 5 functionalities are supported on per Database level:
– Procedure Cache cleaning
– MAXDOP control
– Query Optimizer Cardinalitiy Estimation Model activation control
– Parameter Sniffing control
– Query Optimizer Hotfixes enabling

Niko also shows an example of how the different MAXDOP settings interact.

Comments closed

Dueling Log Backup Jobs

Published 2016-06-14 by Kevin Feasel

Robert Davis ran into HADR_WORK_QUEUE waits recently:

Our 3rd party monitoring solution collects blocking information, but not for system threads. There was no additional information available for this blocking incident, but I could see that the system thread was a background process with the command “UNKNOWN TOKEN” and was sitting in a wait type of “HADR_WORK_QUEUE”. It was clearly the worker thread for the AG of a specific database.

A little later, we had blocking again involving that same thread, but this time, the AG worker thread was blocking the log backup thread. Seemed logical that if the worker thread could block the log backup, then the log backup could have also blocked the worker thread, but still it did not make sense to me.

This is one of those cases in which the answer makes perfect sense after the fact, but can be maddening until then.

Comments closed

SQL Server 2014 Express Docker Image

Published 2016-06-13 by Kevin Feasel

Perry Skountrianos introduces a new Docker image:

We are excited to announce the public availability of the sql server 2014 express Docker image for Windows Server Core based Containers! The public repo is hosted on Docker Hub and contains the latest docker image as well as pointers to the Dockerfile and the start PS script(hosted on Github). We hope you will find this image useful and leverage it for your container based applications!

Containerization is a huge part of modern administrative world and it’s good to see Microsoft (belatedly) jumping onto the bandwagon.

Comments closed

Think About Recovery Mode

Published 2016-06-10 by Kevin Feasel

Kendra Little answers a user question about a co-worker who puts all prod databases into Simple recovery mode:

What if Your Coworker Regularly Goes Off the Ranch and Doesn’t Use Change Control?

Don’t cover for them.

Ask them about it first to make sure there wasn’t an emergency change request you’re unaware of for the change, but be honest about what happened when you’re asked.

In other words, treat them as an equal and a grown-up.

Sometimes in this situation, people sugar coat things or cover for the person who makes mistakes. You need to treat them as an adult though.

If you made mistakes, you would own up to what happened and work to not do it again, right? It’s just about respectfully allowing others to own their actions.

Kendra’s answer is a good mixture of technical explanation and not being socially inept.  It’s important to follow that two-pronged approach.

Comments closed

Don’t Ignore .NET Framework 3.5 Just Yet

Published 2016-06-10 by Kevin Feasel

Shaun J. Stuart notes that database mail apparently still requires .NET Framework 3.5:

However, I did run into one issue later on regarding database mail. I was able to configure it correctly and was even able to send a test email through SSMS without any errors. Unfortunately, the email was never delivered. Additionally, the database mail logs did not show database mail starting or attempting to send the message. I checked with my Exchange administrator and he said he never saw the SQL Server connecting to the mail server.

A check of the database mail table in msdb (sysmail_allitems) showed my messages sitting there with a status of Unsent.

After troubleshooting for a couple of hours and getting nowhere, we installed in the .Net Framework 3.5 and suddenly database mail started working.

This, hopefully, is a bug.  But not getting e-mail alerts you’re expecting to receive can be a scary scenario.

Comments closed

Generating Bulk Import Files

Published 2016-06-10 by Kevin Feasel

Cody Konior shows us how to create a SQL Server bulk insert format file using Powershell:

Don’t forget there are a few minor security considerations:

  • Your login needs Administer Bulk Operations permission.
  • Your AD account needs access to the file (and possibly delegation enabled for remote shares)
  • Or if using an SQL login the database engine service account needs access to the file.

Anyway now that we have the data in table format without worrying about ordering or duplicate column names, we can much more easily manipulate it and store it into the database.

I remember creating a couple of these by hand, and that was no fun.  I never created enough to get the hang of the syntax or to want to automate the process, but at least I know where to look if I ever have to do this again.

Comments closed