SQL Server Graph Database of US Capitals

SQL Server 2017 introduced graph database functionality. Graphs are a core concept in computer science and I was excited to hear of this new feature.

Robert Sheldon has a great series on SQL Server’s graph database here. If you’re interested in learning about graphs in SQL Server, it’s a great place to start.

While there’s countless relational databases out there for practice, there’s not much in the way of graph databases. It is my intent to share my graph databases with the world in hopes that it removes the friction associated with your learning.

US Capitals is a popular data set for working with graphs. Nodes identify a state capital. An edge connects a capital in one state with the capital of a neighboring state. Only the lower 48 states are present. While the data is readily available, I was unable to find TSQL scripts to create the graph using SQL Server 2017 graph database. I created those scripts and have made them readily available on GitHub.

Create a SQL Server database and execute these scripts in order:





There are two sets of INSERT statements in order to create a bi-directional graph. Without both edges, the graph could only be traversed in one direction.

Once you have a usable graph database, you can begin programming against it. In my next post, I’ll go over my PowerShell implementation of Dijkstra’s algorithm, which is a classic shortest path algorithm.


Parallel Ola Hallengren Backups

When a SQL Server instance has a large number of databases (100s or 1000s), it can be very challenging to complete backups in a timely manner. Using common methods such as Ola Hallengren scripts or native maintenance plans often take a long time due to serial processing (1 processor). So the question becomes, how do we perform backups in parallel using multiple processors?

With any backup automation, there’s a few core requirements that we’ll keep in mind while developing this solution:
* Ensure newly created databases are backed up
* Ensure deleted databases are not backed up
* Ensure we don’t backup a database multiple times

I’ve come up with a method to run Ola Hallengren’s famous maintenance scripts in parallel. Using dynamic SQL we can generate a new database list at runtime, and backup just the databases in that list. There’s no need to maintain a list of databases and there’s no chance a database will be missed. We will create multiple SQL Agent jobs that each refer to a separate list of databases. These jobs can then be executed in parallel.

Decide On The Number of Jobs

First step to this process is to decide on the number of parallel jobs you want to create. For this example, I will use 4 parallel jobs. It would be wise to consider how many processors you have on the system and the load incurred by the server during this process.

Dividing Up The Databases

Every database is given a unique integer ID upon creation. This database_id can be found in the sys.databases system table. Using the modulo function, we can divide up the databases based on database_id and the number of jobs we decided upon (in my case, 4). We can view the modulo value via:

SELECT name, database_id, database_id % 4 AS [mod] FROM sys.databases

Unfortunately, Ola Hallengren’s scripts don’t run on database_id, they run on database name. Let’s convert our script to return a list of database names, remove the hard coded number, and make the script easier to update:


SET @NumberOfJobs = 4
SET @ThisJob = 0

FROM sys.databases
WHERE database_id % @NumberOfJobs = @ThisJob

Notice the new variable @ThisJob. This variable will be different for each SQL Server Agent job you create. Use values 0 through the number of jobs minus one.

Also notice the COALESCE call which includes a comma. This is critical as it will generate a comma separated list of database names which we can then pass into Ola Hallengren using the @DATABASES variable.

Dynamically Call Ola Hallengren

Looking at the default parameters for Ola Hallengren’s DatabaseBackup procedure, we can see that all databases are backed up via the @Databases = 'USER_DATABASES' parameter value. Let’s convert a DatabaseBackup call to dynamic SQL:


'EXECUTE [dbo].[DatabaseBackup] 
@Databases = ''USER_DATABASES'',  
@Directory = N''C:\SQLBackups'', 
@BackupType = ''FULL'', 
@Verify = ''Y'', 
@CleanupTime = 48, 
@CheckSum = ''Y'', 
@LogToTable = ''Y'''

--print @SQL

And now, let’s combine the modulo script and the dynamic SQL script. This will be our final product that we can put directly into a SQL Agent job.


SET @NumberOfJobs = 4
SET @ThisJob = 0

FROM sys.databases
WHERE database_id % @NumberOfJobs = @ThisJob


'EXECUTE [dbo].[DatabaseBackup] 
@Databases = ''' + @DATABASES + ''', 
@Directory = N''C:\SQLBackups'', 
@BackupType = ''FULL'', 
@Verify = ''Y'', 
@CleanupTime = 48, 
@CheckSum = ''Y'', 
@LogToTable = ''Y'''

--print @SQL

Pay attention to where the @Databases parameter is set to our modulo generated list of databases. By calling the modulo script everytime, we guarantee that new databases will be backed up and deleted databases will not.

You Must Create Multiple Jobs

To ensure you backup every database on the instance, you must create multiple agent jobs. How many is determined by the value you specified in @NumberOfJobs. If you leave a value out, then all the databases with that database_id modulo result will not be backed up.

In practice, I name my jobs using the default job names with a suffix that corresponds to which modulo result the job uses. I script one of the jobs out, then modify the suffix and the @ThisJob variable. This gives me a tidy set of agent jobs:


There’s definitely room for more automation in this solution. I still need to automate the deployment of all jobs, whichout the need to modify the suffix or @ThisJob. But that’s a simple matter of PowerShell string concatenation. Stay tuned for a future post with that solution.


Logon Trigger Woes


This month’s TSQLTuesday is hosted by Steve Jones on the topic of triggers!

In a large enough environment, eventually the question will be asked “is this database still being used?” There’s a few technologies available to help you answer this question and by far the worst is logon triggers.

I work in a large mixed environment (SQL Server, Oracle, MySQL, YourSQL, etc…) and logon triggers have been used here for a very long time. Specifically on the Oracle servers, logon triggers are used to track who is connecting to the database from where. To a lesser extent, they’re also used to control where connections come from. So the idea of using logon triggers to identify if a SQL Server was still being used seemed very natural to the team.

Logon triggers were something I learned for a 2008 certification exam but I have never seen them put to use in a SQL Server environment.

Setting up a Logon Trigger

First, I created a database with a table to capture login information. Columns for the login, the timestamp, and the host they rode in on:


CREATE TABLE [dbo].[LoginInfo](
[Login_Name] [nvarchar](256) NULL,
[Login_Time] [datetime] NULL,
[Host_Name] [nvarchar](128) NULL

I wrote my logon trigger to delete any old Login\Host records before inserting the new record. The idea being that I really only care about the most recent login. I also don’t want this table to cause any growth concerns and I also don’t want to put a purge job in place. Having the DELETE in the trigger takes care of all those concerns (but adds new ones, more on that below):

DECLARE @hostname NVARCHAR(128)

--Capture the hostname
SELECT @hostname = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(128)')

--Remove old Login\Hostname records
DELETE FROM DBAInfo.dbo.LoginInfo
AND [Host_Name] = @hostname

--Insert the new record
INSERT INTO DBAInfo.dbo.LoginInfo

The trigger works as expected, recording all logins. As a DBA, I like having a table to query and sort to my liking.


But what happens when the trigger fails? My first brush with this was when I dropped the database containing the logon trigger. This causes an error in the trigger and fails the logon! No users or application accounts can login, including myself.

The error message provided is “Logon failed for login due to trigger execution” and there are two ways to fix it.
1. Logon with the dedicated administrator connection (DAC) and disable\drop the trigger.
1. Restart SQL Server in single user mode and disable\drop the trigger.

While I may be able to go the rest of my career without breaking this trigger, another DBA might not. Mistakes happen to all of us and having a server’s availability hinge on a single table or database strikes me as very risky.


I implemented this trigger on a test system that was being monitored by Idera SQL Diagnostic Manager. Idera was creating so many connections that it was deadlocking due to the logon trigger. Both failed logons and deadlocks are logged in the SQL Server Error log:


My logon trigger, as written, cannot go into production. If you are forced to use a logon trigger in production, here’s some thoughts:

  1. Make sure you have error handling in place. The trigger can never fail or you will lock users out of the instance.
  2. Make sure you have remote DAC configured (and know how to use it!), for cases when the trigger does fail.
  3. Limit the transactions in the trigger to avoid deadlocking.

If you’re like me and need to get a grip on who’s loging into your server, a better solution is really to use an audit to capture logins (post coming in the future on that), a trace, or a third party tool. There are many ways this trigger could be re-written to prevent these issues but IMO logon triggers are too risky for critical environments.

SUSER_SNAME() Returning the Wrong Login

If users are logging into your SQL Server with their Active Directory accounts, you may run into an issue if their login changes. This issue is usually associated with someone changing their account name to reflect a change in their legal name. If the user executes the function SUSER_SNAME(), it may return their old login.

There is some caching that takes place on the server which is holding onto the “key” associated with that account. When a user has their account name changed, Active Directory will not update this “key”, only the “name”. Any server that has cached the account may retain the old account name. When SQL Server processes the SUSER_SNAME() call, the old account name is returned.

Rebooting the server has been shown to resolve this issue but there may be a better way. If your users are obtaining access through an Active Directory group (which they should be), simply creating a login for that specific user and then dropping it will resolve the issue.

Creating the login will require the server to validate the account “name” against Active Directory, during which the cached record will be over-written. Dropping the login will return the instance configuration to it’s original state, where the user obtains access via their group.

Finding VMotions with Powershell and the VCenter CLI

If you work in a VMWare shop you’re probably aware that VMs can be migrated to a new host under certain conditions. While the purpose of these migrations is good, I’ve found that virtual SQL Servers usually do not like to be VMotioned. At my current employer, this is often noticed in AlwaysOn Availbility Groups.

Our team will occasionally get notified that an AlwaysOn Group has failed over. After investigating, no meaningful cause can be identified. Then, I’ll look in the VSphere client and sure enough, the server was migrated to a new host. This happens so often that VSphere is now the first place I check for failovers. VMWare makes some Powershell capabilities available to us, so I’ve created a CMDlet to quickly return the VMWare “events” for a given VM.

Before I get to the code, you might be thinking that VMotion is not the culprit. I assure you it is. I routinely have this argument with virtualization experts and even other DBA’s. “But Microsoft says it’s supported!”… I know they do, but VMs still don’t migrate without impact 100% of the time. As a DBA, I believe VMotions should be used only for host outages (planned or un-planned). Letting VCenter migrate VMs based on resource usages means you havn’t allocated the proper resources to your servers.

First, download and install the VMWare PowerCLI. You can find the install and the documentation here https://www.vmware.com/support/developer/PowerCLI/index.html

As the name implies, the Command Line Interface is going to drop you into an interactive session with VCenter. We’re going to design our function to perform the following:

  1. Save our current location
  2. Import the CLI module
  3. Open the CLI
  4. Use the CLI to get the events for our server
  5. Close the CLI
  6. Return to our original location

The first step is easily accomplished with a native Powershell CMDLet and the second step is a basic module import


Import-Module VMWare.VimAutomation.Core

This CMDLet captures your current directory and adds it to a stack for use later.

Next, open the CLI with the following, replacing $VCENTERSERVER with your VCenter server

$VCenterServer = 'VCenterServerXYZ'
Connect-VIServer $VCenterServer

Once the CLI is opened, we need to retrieve the VM we’re after, then retrieve the events for that VM

$Server = 'ServerABC'
Get-VM -Name $Server | Get-VIEvent

The above command will output the results to our console, I prefer to clean up the output as follows

$Server = 'ServerABC'
Get-VM -Name $Server | Get-VIEvent | Select-Object CreatedTime, FullFormattedMessage

Once we have our information, we can close the CLI

Disconnect-VIServer -Server $VCenterServer

And lastly, pop our original directory location off the stack


In practice, I have this code wrapped up in a CMDLet and loaded into every Poweshell session via my profile. I have the VCenter server hardcoded but the VM that I’m retrieving events for is a CMDLet parameter. The whole CMDLet looks like this

 NAME:    Get-VMEvents
 AUTHOR:  James Livingston

Queries VCenter Server for the events related to a specific virtual machine.
Requires the VCenter CLI.

Get-VMEvents -Server ServerABC
function Get-VMEvents {
    param (
        [Parameter(Mandatory = $true)] [string]$Server


    Import-Module VMware.VimAutomation.Core

    $VCenterServer = 'VCenterServerXYZ'

    Connect-VIServer $VCenterServer | Out-Null

    Get-VM -Name $Server | Get-VIEvent | Select-Object CreatedTime, FullFormattedMessage

    Disconnect-VIServer -Server $VCenterServer -Force -Confirm:$false


Get-VMEvents -Server 'ServerABC'

So You Blew Up the Plan Cache…

Recently, I was asked to investigate high CPU on a production server. I began my routine which is a combination of wait stats, performance counters, and resource hogging statements. I had identified a CPU intensive stored procedure that had a cardinality issue in the plan. I was about to present my findings when…. POOF.

Another DBA had just executed DBCC FREEPROCCACHE and cleared the procedure cache. Immediately following this statement, the server hit 100% CPU and stayed there for 15-20 minutes. So, I started my investigation over and found the server was being flooded by a rarely seen wait type:



This wait type means SQL Server is trying to create an execute plan but doesn’t have enough memory, so the request waits until more memory is available.

On a stable system, you probably won’t see this wait type much unless you’re starved for memory. The server I was investigating had 64GB which seemed appropriate for the work load. So why are we getting hammered with these waits? And why is it taking so long to stabalize?

The key here was to look at the plan cache after the system stabalized. I use the SQLSkills script found on Kimberly Trip’s blog post.


Notice the high count of adhoc plans, they completely dominate the plan cache. 1/3rd of the plans are single use and the average use/plan is 58 uses. There’s far fewer procedure plans but they are extremely high re-use with 100k average uses/plan.

After the plan cache was cleared, all of these plans have to compete for same compilation memory, thus the memory waits. Having to re-compile tens of thousands of plans contributed to the high CPU, which in turn lead to dreadful database performance. In this scenario, a single use adhoc plan was using resources that would be better spent on a high use procedure plan. Had this been a system with a lower number of plans (or a lower number of adhoc plans), the performance degradation may have been shorter lived.

The lesson learned here is that blowing up the procedure cache on a system with a large amount of plans will lead to pain. In our case, we could not differentiate between the initial high CPU issue and the high CPU caused by clearing the cache. So in the end, we don’t know if DBCC FREEPROCCACHE helped the issue.

Rolling Downgrade From Enterprise to Developer

So, is it possible to go from Enterprise to Developer in an AlwaysOn cluster? TLDR: Yes.

I was working in a dev environment recently that had been setup with AlwaysOn Availability Groups using the Enterprise Edition of SQL Server 2016. This environment is completely devoted to development of our applications which means it qualifies for use of the Developer edition of SQL Server. Not all shops agree with using Developer edition for dev or test environments, and I understand the argument. However, our shop decided that in this case, we’re comfortable with it.

Now, there’s an “Edition Upgrade” option in the “Maintenance” tab of the SQL Server 2016 install. The intended use is to upgrade from Developer->Enterprise…. but there’s no functionality to downgrade Enterprise to Developer. I could have new virtual servers built and conduct a migration but I decided to attempt a “rolling downgrade” across this AlwaysOn cluster.

The basic process is to remove the replica from the Availability Group, un-install SQL Server Enterprise edition, re-install SQL Server Developer edition, and finally add the replica back to the Availability Group. During this rolling downgrade, SQL Server didn’t give any errors or complaints about mixing Enterprise with Developer editions in the same Availability Group.

If that sounds like a lot to you, keep in mind that I have everything automated (including the un-install, including the download\extraction of the installation ISO, everything….). The downgrade of the 2 node cluster was completed in under 30 minutes for this small database. Most of that time was spent downloading media from a remote server and installing SQL Server. My complete process was this:

  1. Set the AG to manual failover – This is a best practice for any patching\upgrading\downgrading processes
  2. On the secondary replica
  3. Remove secondary replica from AG
  4. Delete un-joined database on the secondary – SQL will complain later if the database files exist
  5. Un-install SQL Server enterprise on the secondary
  6. Install SQL Server developer on the secondary
  7. Add the secondary replica back to the AG and replicate our database(s)
  8. Copy any logins\jobs\objects required from the primary to the secondary – We’re about to failover, so configure anything you need on your “new” secondary
  9. Failover
  10. On our “new” secondary replica
  11. Remove new secondary replica from the AG
  12. Delete un-joined database on the secondary – SQL will complain later if the database files exist
  13. Un-install SQL Server enterprise on the secondary
  14. Install SQL Server developer on the secondary
  15. Add the secondary replica back to the AG and replicate our database(s)
  16. Copy any logins\jobs\objects required from the primary to the secondary
  17. Set the AG back to automatic failover

Our configuration here uses an AlwaysOn Listener which allows me to failover the AG without taking down the application. In fact, the application didn’t make a peep during the entire process.