SQL Server 2016, upgrading to compatability level 130,Trace flag 139 and additional one-off dbcc checks.


When upgrading to SQL Server 2016 RTM CU3/SP1 and upgrading to database compatablity leve 130 there are additional DBCC checks which should be performed.

These are hidden behind Trace flag 139 which should be temporarily enabled as part of the process of changing database compability level to 130.

  • Enable trace flag 139 by running DBCC TRACEON(139, -1).
  • Run DBCC CHECKDB/TABLE..WITH EXTENDED_LOGICAL_CHECKS to validate persisted structures
  • Run DBCC CHECKCONSTRAINTS commands (if rows are affected the associated where clause to identify the row will be returned).
  • Disable trace flag 139 by running DBCC TRACEOFF(139, -1)
  • Change the database compatibility level to 130.
  • REBUILD any structures that you identified in step 1.

There are impovements to expression evaluation in database level 130 and this affects persisted structures

  • Check constraints
  • Persisted Computed columns
  • Indexes using computing columns whether as part of the key or as included columns
  • Filtered indexes
  • Indexed views

Upgrade to compatability level 130 BEFORE attempting to fix issues so the new expression evaluation logic is used for the fixed.

  • Check constraints – change data or drop/recreate constraint with new expression
  • Persisted Computed columns – Update a column referenced by the computed column to the same value to force recalcuation of the computed column
  • Index/filtered index/indexed views – Either A) Put db in single user mode and run DBCC CHECKTABLE with REPAIR_REBUILD B) ALTER INDEX…REBUILD and if supported in your edition of sql server consider adding the WITH (ONLINE=ON) clause.

NOTE: There are some queries in the Appendix C/D of the article above which can be used to help identify affected objects.

London BI/SQL Server user groups.

If you are BI based and want to meetup with other in London, whether technical or business based here as some contacts.

https://sqlserverfaq.com/ – General sql server (covers lots of usergroups)

http://sqllondon.pass.org/ – London SQL Pass Chapter for SQL Server but BI people do attend as well.

https://www.meetup.com/London-PUG/ London Power BI Users Group – they meet at Skills Matter, 10 South Place near Moorgate tube, big group, lots of people attend – 50+,

https://www.meetup.com/London-Business-Analytics-Group/ – London Business Analytics Group – Very power user/Business based, less technical

SSMS Running remotely

To run SSMS remotely:

runas /netonly /user:domain\username “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe”


runas /netonly /user:WIN-P9OQ9GF24HV\justdave “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe”

SQL Server SSMS and SSDT now GA

SQL Server

SSMS now generally available


SQL Server Data Tools (SSDT) for Visual Studio 2015 is now generally available.



System.Transactions (and AlwaysOn Availability Groups)

With SQL Server 2016 AlwaysOn Availability Groups (on Windows Server 2016 TP4) supports MSDTC which can be used for transactions which access more than 1 database.

However there are Transactions which do not use MSDTC.

Within the .NET Framework there is System.Transactions namespace https://msdn.microsoft.com/en-us/library/system.transactions%28v=vs.110%29.aspx

As per https://msdn.microsoft.com/en-us/library/ms229978%28v=vs.110%29.aspx these implement a Transaction Manager which can handle transactions within SQL Server.

System.Transactions only SOMETIMES escalates to MSDTC!

“As long as the System.Transactions infrastructure handles..at most one durable resource that supports single-phase notifications, the transaction remains in the ownership of the System.Transactions infrastructure”

The question then is with SQL Server 2016 AlwaysOn Availability Groups (on Windows Server 2016 TP4) and a transaction which uses more than 1 database

a) Does SQL Server 2016 AlwaysOn Availability Groups support single-phase notifications?

b) With SQL Server 2016 AlwaysOn Availability Groups does moreĀ  than 1 single database count as 1 durable resource or more than 1 durable resource?

As per http://msdn.microsoft.com/en-us/library/ms229979.aspx tracing can be used to determine who promotes a transaction and why.

Checking SQL Server connectivity -PortQry/

Useful tools for checking SQL Server connectivity – PortQry and PortQryUI


SQL Server and Windows Cluster Shared Volumes


Windows Cluster Shared Volumes are a disk volume which acts as a clustered filesystem i.e. read-write on all nodes.

In fact, in Disk Management on the cold side of the cluster,the Volume is tagged with ‘(CSVFS)’

SMB 3.0 is used behind the scenes

How do I setup a Cluster Shared Volume?

See http://www.smooth1.co.uk/installs/dbinstalls.html#3.1.14

What if a cluster node goes down?

No storage failover is needed when a node goes down

  • sql server has no cluster dependency on the disks anymore!

There is still a co-ordinator node for writes.

What if all storage paths to a node go down?

If this is the co-ordinator node, another co-ordinator node is chosen and i/o rediection occurs. See http://www.smooth1.co.uk/mssql/mssql_csv_iorediection.html

How do Cluster Shared Volumes work internally?


Cluster Shared Volume Diagnostics


Cluster Shared Volume Performance Counters


Cluster Shared Volume Failure Handling