SQL Server 2017 CU8 has been released

With the release of CU8 (build 14.0.3029.16)  today, AM detected the following changes since CU7:

New Messages

Message ID Severity Is Event Logged Text
997 16 No Failed to get the update lock on database ‘%.*ls’.
12623 10 No Clone backup succeeded and is stored in %ls.
12624 10 No Clone backup failed.
12625 10 No RESTORE VERIFY failed on the clone backup %ls
12626 10 No Clone database verification has passed.
12627 10 No Clone database verification has failed.
12628 10 No NO_STATISTICS and NO_QUERYSTORE options turned ON as part of VERIFY_CLONE.
12629 10 No Database ‘%.*ls’ is a cloned database.
12630 16 No VERIFY_CLONE option cannot be specified together with SERVICEBROKER option.
33331 16 No DBCC CLEANUPCONVERSATIONS is not allowed on this server.
33332 16 No DBCC CLEANUPCONVERSATIONS cannot be executed through MARS connection.
41870 10 No Dropped %d Orphan Internal Table(s).

Further info on DBCC CLONEDATABASE can be found here.

Changed Message

Message ID New Text Old Text
12621 Database ‘%.*ls’ is a cloned database. This database should be used for diagnostic purposes only and is not supported for use in a production environment. Database ‘%.*ls’ is a cloned database. A cloned database should be used for diagnostic purposes only and is not supported for use in a production environment.

Small wording change.

New configuration option ‘column encryption enclave type’

Just noticed that AM picked up a new configuration in Azure SQL Database called column encryption enclave type.

This option is not currently documented and currently:

  • Configuration ID = 1588
  • Default = 0
  • Minimum = 0
  • Maximum = 3
  • It is not a Dynamic or Advanced configuration setting
  • Description is ‘Type of enclave used for computations on encrypted columns’



This is likely related to Always Encrypted using enclaves early access technology preview.

SQL Server 2016 SP1 CU9 & SP2 CU1 Released

Notification of SQL Server 2016 SP1 CU9 (build 13.0.4502.0) and SP2 CU 1 (build 13.0.5149.0) releases was made today.

AKAWN Monitoring detected the following change between SP2 and SP2 CU1.

Modules

Name New Old
sys.sp_flush_commit_table_on_demand CREATE PROC sys.sp_flush_commit_table_on_demand (@numrows as BIGINT = NULL, @deleted_rows BIGINT = NULL OUTPUT, @date_cleanedup DATETIME = NULL OUTPUT, @cleanup_ts BIGINT = NULL OUTPUT) AS
BEGINIF (is_srvrolemember(‘sysadmin’) = 0)
BEGIN
raiserror(21089,0,1)
return 1
END

IF (@numrows<=0)
BEGIN
raiserror(23100,0,1)
return 1
END

SET DEADLOCK_PRIORITY LOW

IF EXISTS (SELECT * FROM sys.change_tracking_databases where database_id = DB_ID())
BEGIN
DECLARE @batch_size INT
IF (@numrows IS NULL or @numrows >= 10000)
SET @batch_size = 10000
ELSE
SET @batch_size = @numrows

— checking for change tracking side table watermark
DECLARE @deleted_rowcount INT
SET @cleanup_ts = change_tracking_hardened_cleanup_version ()
RAISERROR(22866,0,1,35502,@cleanup_ts)

— checking for safe cleanup watermark
DECLARE @cleanup_version BIGINT
set @cleanup_version = safe_cleanup_version ()
RAISERROR(22866,0,1,35503,@cleanup_version)

IF @cleanup_ts > @cleanup_version
SET @cleanup_ts = @cleanup_version

SELECT @deleted_rowcount = 0, @deleted_rows = 0

WHILE (1=1)
BEGIN
DELETE TOP(@batch_size) sys.syscommittab WHERE commit_ts < @cleanup_ts
SELECT @deleted_rowcount = @@ROWCOUNT
SELECT @deleted_rows += @deleted_rowcount
SELECT @numrows-=10000
IF ((@numrows IS NOT NULL AND @numrows <= 0 ) OR @deleted_rowcount < 10000)
BREAK;
END

select @date_cleanedup = min(commit_time) from sys.syscommittab where commit_ts = (select min(commit_ts) from sys.syscommittab)
IF (@date_cleanedup is null)
SET @date_cleanedup = GETUTCDATE()
END
ELSE IF EXISTS (SELECT * FROM sys.databases WHERE DB_NAME() not in (‘master’, ‘tempdb’, ‘model’, ‘msdb’, ‘mssqlsystemresource’))
TRUNCATE TABLE sys.syscommittab

END

CREATE PROC sys.sp_flush_commit_table_on_demand (@numrows as BIGINT = NULL, @deleted_rows BIGINT = NULL OUTPUT, @date_cleanedup DATETIME = NULL OUTPUT, @cleanup_ts BIGINT = NULL OUTPUT) AS
BEGINIF (is_srvrolemember(‘sysadmin’) = 0)
BEGIN
raiserror(21089,0,1)
return 1
END

IF (@numrows<=0)
BEGIN
raiserror(23100,0,1)
return 1
END

SET DEADLOCK_PRIORITY LOW

DECLARE @batch_size INT
IF (@numrows IS NULL or @numrows >= 10000)
SET @batch_size = 10000
ELSE
SET @batch_size = @numrows

— checking for change tracking side table watermark
DECLARE @deleted_rowcount INT
SET @cleanup_ts = change_tracking_hardened_cleanup_version ()
RAISERROR(22866,0,1,35502,@cleanup_ts)

— checking for safe cleanup watermark
DECLARE @cleanup_version BIGINT
set @cleanup_version = safe_cleanup_version ()
RAISERROR(22866,0,1,35503,@cleanup_version)

IF @cleanup_ts > @cleanup_version
SET @cleanup_ts = @cleanup_version

SELECT @deleted_rowcount = 0, @deleted_rows = 0

WHILE (1=1)
BEGIN
DELETE TOP(@batch_size) sys.syscommittab WHERE commit_ts < @cleanup_ts
SELECT @deleted_rowcount = @@ROWCOUNT
SELECT @deleted_rows += @deleted_rowcount
SELECT @numrows-=10000
IF ((@numrows IS NOT NULL AND @numrows <= 0 ) OR @deleted_rowcount < 10000)
BREAK;
END

select @date_cleanedup = min(commit_time) from sys.syscommittab where commit_ts = (select min(commit_ts) from sys.syscommittab)
IF (@date_cleanedup is null)
SET @date_cleanedup = GETUTCDATE()
DECLARE @dbname sysname
SET @dbname= DB_NAME()

raiserror(21092,-1,-1,@dbname)

END

SQL Server 2017 CU7 Released

Today SQL Server 2017 CU7 (build 14.0.3026.27) was released.

AKAWN Monitoring detected the following change since CU6.

Message ID New Old
833 SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database id %d.  The OS file handle is 0x%p.  The offset of the latest long I/O is: %#016I64x.  The duration of the long I/O is: %I64u ms. SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database id %d.  The OS file handle is 0x%p.  The offset of the latest long I/O is: %#016I64x

So this is a nice add as the long I/O requests now includes the duration in ms.

Microsoft Default Constraints Naming Convention

Have you ever wondered how Microsoft comes up with Default Constraint names?

The name consists of several parts separated by _

  1. DF : Default Constraint
  2. sysmail_p: First 9 characters of the table name
  3. last_ : First 5 characters of the column name
  4. Binary value of the columns’ default_object_id

    | sysmail_profile | last_mod_datetime | 622625261

DF_ | _sysmail_p_     | _last__           | _251C81ED

12_ | _123456789_     | _12345_           | _…..

The T-SQL formula to work out the binary value is
 

SELECT
  [name] AS column_name
, CAST(default_object_id AS varbinary(MAX)) AS binary_value
FROM sys.columns
WHERE 1=1
AND OBJECT_ID = OBJECT_ID('sysmail_profile')
AND default_object_id <> 0;

 

Recover SSMS Scripts

So you’ve been working on scripts in SSMS v17.x and suddenly it freezes and won’t let you interact with it at all.

No fear, when you close (kill) the SSMS process using Task Manager and reopen it you should be prompted to recover your files. The caveat being that you haven’t disabled Save AutoRecover in the Tools – Options menu item.

However, if you’d rather not take the risk of not being prompted and as per the previous screenshot, you should hopefully be able to find your scripts under your Documents\Visual Studio 2015 folder.

Does using a NULL value save you space?

When saving data you may have wondered if converting a null value to another character i.e. a space, impacts disk space usage in your database.

Why would you convert a null to a space or dash? Well some people/organizations, rightly or wrongly, prefer this for reporting purposes or some other business requirement.

OK, lets review 3 common storage types, int, char and varchar:

INT


As we can see, there is no storage difference when storing a null and a single int value.

CHAR


Again, there is no storage difference when storing a null and a single int value.

VARCHAR

Finally we get a difference of 3 bytes when using a null rather than another character when using varchar.

Conclusion, not all data types allow for space savings when using nulls.

SSMS Drag/Drop Format Options

With the newly released SSMS v17.6 there has been a new Drag/Drop section added to Tools -> Options menu item as shown below:
 

 
When both are set to True you’ll get the existing functionality
 

 
When both are set to False you will now get:
 

 
Notice that the Database Version column will still have brackets as it has a space in the name even though Surround object names with brackets when dragged is set to False.

Identify columns available for extended events

The following query helps identify the columns associated with extended events and the format of the column i.e. int32.

SELECT 
 b.[name] AS package
,a.[object_name] AS [event]
,a.column_id
,a.[name] AS [column]
,a.column_type
,a.[type_name] AS column_storage
,a.column_value
,a.[description] AS column_desc
,b.[description] AS package_desc
FROM sys.dm_xe_object_columns a, sys.dm_xe_packages b
WHERE 1=1
AND a.object_package_guid = b.[guid]
AND a.column_type <> 'readonly'
ORDER by b.[name], a.[object_name],a.column_id;


As the data is typically extracted and used on SQL Server, I would have thought it would be more meaningful for Microsoft to actually use the SQL Server types i.e. bigint, nvarchar(MAX), instead we are supplied with a you guess field and it just adds another unnecessary frustration for the DBA / developer.