SQL Server – SSMS – Options – “Override connection string time-out value for table designer updates”

 

Background

This is the third in a series of posts on timeouts

SQL Server Management Studio ( SSMS)

Designers

For those that use the Designer built into SSMS to make structural table changes, the Designer Option panel is a good place to go and make sure that the default settings are what you want them to be.

Screen Shot

Here is what that screen looks:

Initial

Post Changes

Image

Explanation
  1. Here we changed the “Transaction Time-out after:” from the default of 30 seconds to 90 seconds

Misnomer

I think outside of the Database World and specifically SQL Server, it is OK to use the phrase & words, but I think for a tool that targets SQL Server and only that table, a bit more care should be taken.

And, you likely ask why.

Grouping :- Override connection string time-out value for table designer updates
Phrase :- Transaction time-out after

Here are the difficulties that I have with the Group Label “Override connection string time-out value

  1. We all know that the Connection String does not expose command execution nor lock timeout
    • Command Execution can be set in the Connection or Command Object, but not in the actual connection string
    • Because of this understanding, coders have to look into the code and set the timeouts
    • They are not able to do so declaratively in the Web.config and thus not suffer the pain of recompile
  2. There is more than enough misappropriation that Connection Open timeouts and Command Execution timeouts, are one and same

Microsoft – Connect

  1. Item #  :- 3130697
    • Link :- Link
      Opened By: Daniel Adeniji
      Date Opened:- 2017-March-25
      Type :- Suggestion
      Status :- Active

SQL Server – DBCC Failed on Data Purity Check

Background

Reviewing SQL Server Agent Jobs and noticed that one of Database Maintenance Job was disabled.

Enabled and ran it, and it errored out.

 

Failed Job

Here is the failed Job…

StartJobs-20170303-0521PM (Cropped up)

 

SQL Dump

Unfortunately, not only did the job fail, it created SQL Dump files, as well.

Here is a sample of the created dump files.

Image

SQLDump0333_log_20170303_0526PM ( cropped-up)

 

Textual

 

 


2017-03-03 17:05:26.22 spid60      DBCC CHECKDB (SGS) WITH all_errormsgs, no_infomsgs, data_purity executed by LAB\dbsvc found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.  Internal database snapshot has split point LSN = 00013838:00000171:0001 and first LSN = 00013838:00000170:0001.
2017-03-03 17:07:08.99 spid60      DBCC CHECKDB (SRPA) WITH all_errormsgs, no_infomsgs, data_purity executed by LAB\dbsvc found 10 errors and repaired 0 errors. Elapsed time: 0 hours 1 minutes 42 seconds.  Internal database snapshot has split point LSN = 003029a5:0001b40e:0001 and first LSN = 003029a5:0001b40d:0001.
2017-03-03 17:07:09.14 spid60      Using 'dbghelp.dll' version '4.0.5'
2017-03-03 17:07:09.18 spid60      **Dump thread - spid = 0, EC = 0x0000000175F700F0
2017-03-03 17:07:09.19 spid60      ***Stack Dump being sent to E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0329.txt
2017-03-03 17:07:09.20 spid60      * *******************************************************************************
2017-03-03 17:07:09.20 spid60      *
2017-03-03 17:07:09.20 spid60      * BEGIN STACK DUMP:
2017-03-03 17:07:09.20 spid60      *   03/03/17 17:07:09 spid 60
2017-03-03 17:07:09.20 spid60      *
2017-03-03 17:07:09.20 spid60      * DBCC database corruption
2017-03-03 17:07:09.20 spid60      *
2017-03-03 17:07:09.21 spid60      * Input Buffer 196 bytes -
2017-03-03 17:07:09.21 spid60      *             EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DAT
2017-03-03 17:07:09.21 spid60      *  ABASES', @LogToTable = 'Y'
2017-03-03 17:07:09.21 spid60      *  
2017-03-03 17:07:09.21 spid60      * *******************************************************************************
2017-03-03 17:07:09.21 spid60      * -------------------------------------------------------------------------------
2017-03-03 17:07:09.21 spid60      * Short Stack Dump
2017-03-03 17:07:09.38 spid60      Stack Signature for the dump is 0x00000000000000F4
2017-03-03 17:07:23.11 spid60      External dump process return code 0x20000001.
External dump process returned no errors.

2017-03-03 17:09:07.28 spid60      DBCC CHECKDB (SRPA_CORRUPT2) WITH all_errormsgs, no_infomsgs, data_purity executed by LAB\dbsvc found 10 errors and repaired 0 errors. Elapsed time: 0 hours 1 minutes 41 seconds.  Internal database snapshot has split point LSN = 00302996:0000d702:0001 and first LSN = 00302996:0000d701:0001.
2017-03-03 17:09:07.28 spid60      **Dump thread - spid = 0, EC = 0x0000000175F700F0
2017-03-03 17:09:07.28 spid60      ***Stack Dump being sent to E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0330.txt
2017-03-03 17:09:07.28 spid60      * *******************************************************************************
2017-03-03 17:09:07.28 spid60      *
2017-03-03 17:09:07.28 spid60      * BEGIN STACK DUMP:
2017-03-03 17:09:07.28 spid60      *   03/03/17 17:09:07 spid 60
2017-03-03 17:09:07.28 spid60      *
2017-03-03 17:09:07.28 spid60      * DBCC database corruption
2017-03-03 17:09:07.28 spid60      *
2017-03-03 17:09:07.28 spid60      * Input Buffer 196 bytes -
2017-03-03 17:09:07.28 spid60      *             EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DAT
2017-03-03 17:09:07.28 spid60      *  ABASES', @LogToTable = 'Y'
2017-03-03 17:09:07.28 spid60      *  
2017-03-03 17:09:07.28 spid60      * *******************************************************************************
2017-03-03 17:09:07.28 spid60      * -------------------------------------------------------------------------------
2017-03-03 17:09:07.28 spid60      * Short Stack Dump
2017-03-03 17:09:07.35 spid60      Stack Signature for the dump is 0x00000000000000F4 

 

 

 

Troubleshooting

Review Job

Reviewed the Job and it’s lone step.

 

Repair Database

Scripted out DBCC/Repair Rebuild

Outline

  1. Place database in single user mode
  2. Initiate new transaction
    • Issue DBCC with following options
      • Data purity check
      • Using Repair/Rebuild, attempt to repair errors
      • Display all error messages
      • Withhold informational messages
  3. Exit Database from single user mode

Script

 
use master;

ALTER DATABASE [HRDB]
	SET SINGLE_USER
		WITH ROLLBACK IMMEDIATE;
GO

set nocount on;
set XACT_ABORT on;

declare @dateBegin datetime
declare @dateDBCCCompleted datetime
declare @dateFullCompletion datetime

declare @strLog varchar(80)
declare @DATE_STYLE int

declare @NUMBER_OF_COLUMNS int
declare @CHAR_LINEBREAK varchar(600)

set @NUMBER_OF_COLUMNS = 120
set @DATE_STYLE = 100
set @CHAR_LINEBREAK = replicate('=', @NUMBER_OF_COLUMNS)

begin tran

	print @CHAR_LINEBREAK
	set @dateBegin = getdate()
	set @strLog = 'Date DBCC Began :- ' + convert(varchar(20), @dateBegin, @DATE_STYLE)
	print @strLog

	print @CHAR_LINEBREAK


		DBCC CHECKDB
		(
			  [HRDB]
		   ,  REPAIR_REBUILD 
		)
		with
			    ALL_ERRORMSGS 
			  , no_infomsgs
			  , data_purity


	print @CHAR_LINEBREAK

	set @dateDBCCCompleted = getdate()
	set @strLog = 'Date DBCC Completed :- ' + convert(varchar(20), @dateDBCCCompleted, @DATE_STYLE)
	print @strLog

	print @CHAR_LINEBREAK

rollback tran

	set @dateFullCompletion = getdate()
	set @strLog = 'Date Full Completion :- ' + convert(varchar(20), @dateFullCompletion, @DATE_STYLE)
	print @strLog

	print @CHAR_LINEBREAK

go

ALTER DATABASE [HRDB]
	SET MULTI_USER
		WITH ROLLBACK IMMEDIATE;
GO


 

 

Output

Image

DBCCCheckDB_ProcessLog_20170303_0559PM

 

Textual

 
========================================================================================================================
Date DBCC Began :- Mar 3 2017 5:56PM
========================================================================================================================
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 12 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 13 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 14 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 59 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 60 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 61 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 62 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 63 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
CHECKDB found 0 allocation errors and 8 consistency errors in table 'usr_Portfolio_Dirty_Data' (object ID 1549248574).
Msg 2570, Level 16, State 3, Line 35
Page (1:1781040), slot 0 in object ID 1585141138, index ID 1, partition ID 72057616957440000, alloc unit ID 72057616965697536 (type "In-row data"). Column "Value" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1781040), slot 1 in object ID 1585141138, index ID 1, partition ID 72057616957440000, alloc unit ID 72057616965697536 (type "In-row data"). Column "Value" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'mkt_FactorData13' (object ID 1585141138).
CHECKDB found 0 allocation errors and 10 consistency errors in database 'HRDB'.
========================================================================================================================
Date DBCC Completed :- Mar 3 2017 5:58PM
========================================================================================================================
Date Full Completion :- Mar 3 2017 5:58PM
========================================================================================================================
 

 

 

Explanation

  • Two tables and columns combination cited
    • Table :- usr_Portfolio_Dirty_Data, Column :- Holding
    • Table :- mkt_FactorData13, Column :- Value
  • All cited columns are defined as having the real datatype
  • Message reads
    • Column “Holding” value is out of range for data type “real”.  Update column to a legal value.
      The system cannot self repair this error.
    • Column “Value” value is out of range for data type “real”.  Update column to a legal value.
      The system cannot self repair this error.

 

 

Data Purity Check

The data purity check cited that some values are not legal.

The data type for the columns cited is real.

 

Validation

 

[constant].[numberMax]

Code


use [master]
go

if schema_id('CONSTANT') is null
begin

	exec('create schema [CONSTANT] authorization [dbo]')

end
go

if object_id('[constant].[numberMax]') is null
begin

	exec('create view [constant].[numberMax] as select [shell] = 1/0 ')

end
go

alter view [constant].[numberMax] 
with schemabinding
as 
	select 

		/*
			real
				a) - 3.40E + 38 to -1.18E - 38
				b) 0 
				c) 1.18E - 38 to 3.40E + 38	
				
				Size
					:- 4 Bytes

		*/
		  [realNumber1Low] 
			= cast(-3.40E+38 as real)
		
		, [realNumber1High] 
			= cast(-1.18E-38 as real)

		, [realNumber2Low] 
			= cast(1.18E-38 as real)

		, [realNumber2High] 
			= cast(3.40E+38 as real)

go

grant select on [constant].[numberMax] to [public]
go

[dbo].[fn_DecimalPlaces]

Code


use master
go

IF NOT EXISTS 
(
	SELECT * 
	FROM   sys.objects tblSO
	WHERE  tblSO.object_id = object_id('[dbo].[fn_DecimalPlaces]')

)
begin

	exec('create function [dbo].[fn_DecimalPlaces]() returns tinyint as begin return (-1) end ');

end

GO

ALTER FUNCTION [dbo].[fn_DecimalPlaces]
(
	@A float
)
RETURNS int
with schemabinding
AS
BEGIN

	/*
		Topic  :- Count Decimal Places
		Author :- Sergiy
		URL    :- https://www.sqlservercentral.com/Forums/Topic314390-8-1.aspx
	*/
	declare @R int

	IF ( @A IS NULL )
	begin

		RETURN NULL

	end


	set @R = 0

	while @A - str(@A, 18 + @R, @r) <> 0
	begin 

		SET @R = @R + 1

	end

	RETURN @R

END
GO

grant execute on [dbo].[fn_DecimalPlaces] to public
go


 

dbo.Holding

Code



SELECT
		top 15
		  [Holding]
		--, [HoldingTryParse] = TRY_PARSE([Holding] as real) 
		, [HoldingAsFloat] = cast([Holding] as float)
		, [Length] = len([Holding])
		, [NumberofDecimalPlaces]
			= dbo.fn_DecimalPlaces([Holding])

from   [dbo].[usr_Portfolio_Dirty_Data] tblPDD

cross apply [constant].[numberMax] vwCNM

where  (

			  ( [Holding] <> 0.0 )

		/*
			AND 
				(
					    ( [Holding] < CONVERT(real,1.18E-38) OR [Holding] > CONVERT(real,3.40E+38) ) 
					AND ( [Holding] < CONVERT(real,-3.40E+38) OR [Holding] > CONVERT(real,-1.18E-38) )
				)

		*/

			AND 
				(
					(
							  ( [Holding] < [realNumber2Low] ) OR ( [Holding] >  [realNumber2High] )

					)

					AND
					(
							  ( [Holding] < [realNumber1Low] ) OR ( [Holding] >  [realNumber1High] )

					)

				)

	)		



Output

validatedata_holding_20170304_1201m

dbo.mkt_FactorData13

Code

 

 
SELECT  top 5 
		  [WSCode]
		, [DataDate]
		, [Value]
		--, [ValueTryParse] = TRY_PARSE([Value] as real) 
		, [ValueAsFloat] = cast([Value] as float)
		, [ValueIsNumeric] = IsNumeric([Value])
		, [Length] = len([Value])
		, [NumberofDecimalPlaces]
			= [master].dbo.fn_DecimalPlaces([Value])

from   [dbo].[mkt_FactorData13] tblMKT

cross apply [constant].[numberMax] vwCNM

where  (

			  ( [Value] <> 0.0 )
			
			/*
				WHERE col2<>0.0 
				AND (col2 < CONVERT(real,1.18E-38) OR col2 > CONVERT(real,3.40E+38)) 
				AND (col2 < CONVERT(real,-3.40E+38) OR col2 > CONVERT(real,-1.18E-38)) 
			*/

			/*
			AND 
				(
					    ( [Value] < CONVERT(real,1.18E-38) OR [Value] > CONVERT(real,3.40E+38) ) 
					AND ( [Value] < CONVERT(real,-3.40E+38) OR [Value] > CONVERT(real,-1.18E-38) )
				)

			*/

			AND 
				(
					(
							  ( [Value] < [realNumber2Low] ) OR ( [Value] >  [realNumber2High] )

					)

					AND
					(
							  ( [Value] < [realNumber1Low] ) OR ( [Value] >  [realNumber1High] )

					)

				)
	)		



 

 

Output

validatedata_marketing_20170304_1155am

Workarounds

Outline

There are a couple of workarounds

  1. Skip puritch check
  2. Change column datatype from real to float, currency if it is a currency field

Skip Purity Check

Ola Hallengren

Original


sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d AdminDB -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES', @LogToTable = 'Y' " -b

Revision


sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d AdminDB -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES', @LogToTable = 'Y', @PhysicalOnly = 'Y'  " -b

Explanation

  1. Add @PhysicalOnly=Y

Connect

Opened up a Connect Item.

  1. DBCC CHECKDB/show Purity cites data type of float as incorrect even while correct – by Daniel Adeniji
    Item Number: – 3126630
    Link :- Link
    Type :- Bug
    Status :- Active
    Date Opened :- 3/5/2017 2:32:29 PM

GitHub

Uploaded files to GitHub.
Link is here

 

Summary

It seems that on this particular version of SQL Server, SQL sometimes has problems running purity checks against certain records.

The failing column are defined as having the float datatype.

BTW, the version# is

SQL Server Version :- Microsoft SQL Server 2008 R2 (SP3) – 10.50.6000.34 (X64)
Aug 19 2014 12:21:34
Copyright (c) Microsoft Corporation
Enterprise
Product Level :- SP3
Product Version :- 10.50.6000.34
Edition :- Enterprise Edition (64-bit)

References

  1. DBCC CheckDB
    • Developer Network
    • Support.microsoft.com
      • Troubleshooting DBCC error 2570 in SQL Server 2005 and later versions
        Link
    • Sergessqlnotes’s Blog
      • DBCC CHECKDB Msg 2570 Data Purity Errors
        Link
  2. DataType
    • Developer Network
      • Transact-SQL Reference (Database Engine) Data Types (Transact-SQL)  Numeric Types
        • float and real (Transact-SQL)
          Link
    • Tech Republic
      • 10+ common questions about SQL Server data types
        Link
  3. Microsoft Developer
    • SQL with Manoj
      • Use new TRY_PARSE() instead of ISNUMERIC() | SQL Server 2012
        Link

 

SQL Server – ErrorLog – [INFO] HkHostLogCheckpointRecord()

Background

Taking a looking at the error log of one of our SQL Server Instances and found an entry that I wanted to know about.

 

Entry

The entry takes different variables based on timestamp but, it looks like this:

GUI

logfileviewer

Text

[INFO] HkHostLogCheckpointRecord(): Database ID: [10]. Logged XTP checkpoint (v4) for timestamp 2748041, serialize timestamp 2748043: { Root ==> GUIDs = { Rowset {DC99368D-9675-46D7-B97F-EA8D78BA5FD0}, Column {808DBB15-5B8E-4C43-88C1-2611B813FC29} }, Recovery LSN = 00018717:00001FC6:002A, Previous complete checkpoint LSN = 00018717:0000207B:0050, Close LSN = 0001874A:00001F44:000E, Complete LSN = 0001874A:00001F89:0002, Root LSN = 0001874A:00001F44:000D, MRT LSN = 00000000:00000000:0000, Delta Watermark LSN = 00000000:00000000:0000, Backup File Collection LSN = 0001874A:00001F89:0001 }

Explanation

  1. HkHostLogCheckpointRecord()
    • Something about checkpoint
  2. Logged XTP checkpoint
    • XTP points to InMemory

 

TroubleShooting

Metadata

Server Version

Let us confirm the Version and Service Pack that we are running…

Code

SELECT 
			  [version]
				= @@version

			, [productVersion]
				= serverproperty('ProductVersion')			

			, [IsXTPSupportedServer]
			  = serverproperty('IsXTPSupported')


 

Output

serverversion

Explanation

  1. @@server is Server Version is v2014 SP2
  2. Product Version is 12.0.5000.00 ( v2012 SP 2)
  3. ServerProperty-IsXTPSupported is 1

 

Processes that are running

Let us check on the processes that are running and see if we can track which one is emitting the messages

Code


select 
		  tblSP.spid

		, tblSP.[dbid]

		, [database]
			= db_name(tblSP.[dbid])

		, [IsXTPSupported]
			= databasepropertyex
			(
				  db_name(tblSP.[dbid])
				, 'IsXTPSupported'
			)

		, [IsMemoryOptimizedElevateToSnapshotEnabled]
			= databasepropertyex
			(
				  db_name(tblSP.[dbid])
				, 'IsMemoryOptimizedElevateToSnapshotEnabled'
			)

		, tblSP.cmd
		, tblSP.cpu
		, tblSP.memusage
		, tblSR.granted_query_memory
		, tblSP.net_library
		, tblSP.waittype
		, tblSP.lastwaittype
		, tblSP.waitresource
		, tblSP.physical_io
		, tblSR.reads
		, tblSR.logical_reads
		, tblSR.writes


from   master.dbo.sysprocesses tblSP

left outer join sys.dm_exec_requests tblSR

		on tblSP.spid = tblSR.[session_id]
 

where  (

			(
				tblSP.cmd in 
					( 
						'XTP_OFFLINE_CKPT'
					)
			)

			or
			(
				( tblSP.cmd like '%XTP%' )
			)

	   ) 

order by
		tblSP.[spid] asc

 

Output

sysprocesses

 

Explanation

  1. We looked for processes whose payload contains XTP
  2. And, we found the following
    • XTP_THREAD_POOL
    • XTP_CKPT_AGENT
    • XTP_OFFLINE_CKPT
  3. Last Wait Types ( lastWaittypes ) are
    • DISPATCHER_QUEUE_SEMAPHORE
    • WAIT_XTP_HOST_WAIT
    • WAIT_XTP_OFFLINE_CKPT_NEW_LOG

 

Stumbling Blocks

As always ran into a couple of stumbling blocks.

DatabasePropertyEx

IsXTPSupported

Starting with v2014, the DatabasePropertyEx function takes an argument, IsXTPSupported, that one can query to determine whether the targeted database supports XTP (In Memory).

The documentation reads:

Text

Indicates whether the database supports In-Memory OLTP, i.e., creating and using memory-optimized tables and natively compiled modules.

Image

isxtpsupporteddocumentation

Explanation

  1. Unfortunately, IsXTPSupported is returning Null even though our databases contain “In Memory” Objects

 

IsMemoryOptimizedElevateToSnapshotEnabled

Textual

Memory-optimized tables are accessed using SNAPSHOT isolation when the session setting TRANSACTION ISOLATION LEVEL is set to a lower isolation level, READ COMMITTED or READ UNCOMMITTED.

Image

ismemoryoptimizedelevatetosnapshotenableddocumentation

 

Lab

We triggered Snapshot Isolation by issuing “MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT”  on one of the databases that contain In_memory objects

Code

use [QA_rbpivr1]
go

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON
GO

Output
ismemoryoptimizedelevatetosnapshotenabledsampled
Explanation

It is a good thing we triggered Snapshot as the above shows that “IsMemoryOptimizedElevateToSnapshotEnabled” reports 1 or On.

While IsXTPSupported continues to be stuck at NULL.

Null means non-applicable.

 

Database Property

Database – QA_rbpivr1

In the screenshot pasted below, we can deduct that the targeted database, QA_rbpivr1, is allocated and has memory assigned to it.

databaseproperties-qa_rbpivr1

Database – ppsivr

databaseproperties-ppsivr

 

Microsoft Connect

Opened up a Connect Item to Track.

  1. Title :- databasepropertyex([dbname], IsXTPSupported) incorrectly returns null
    Number :- 3120570
    Type :- Bug
    Date :- 2/6/2017
    Status :- Active
    Link : Link

 

References

  1. Waittype
    • Microsoft
      • Description of the waittype and lastwaittype columns in the master.dbo.sysprocesses table in SQL Server 2000 and SQL Server 2005
        Link
    • Pediy.com
      • Windows System Call Table (NT/2000/XP/2003/Vista)
        The Metasploit Project
        Link

SQL Server – Error – “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.sysowners’ and the index name ‘nc1’. The duplicate key value”

 

Background

Trying to add Reporting Services to an existing SQL Server v2014 Install this afternoon, and finally ran into a problem that is worthy of talking about.

 

Steps

We are on MS SQL Server 2014 Standard Edition

  1. Added Reporting Services to an existing install
  2. And, now going through the Change Database Steps
    • Specifically, the Progress and Finish Step
    • While processing the SQL Code within the “Running database script” step, we ran into an Error

 

reportdatabasedatabaseconfigured-progressandfinish-20170130-0140pm

 

 

Error

Here is the error message

Error Image

exceptiondetails

Error Text

 


System.Data.SqlClient.SqlException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.sysowners' and the index name 'nc1'. The duplicate key value is (spotlight).
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(SqlConnection conn, String script, ICommandWrapperFactory commandWrapper)
   at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script)
   at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)

 

TroubleShooting

SQL Server Profiler

Ran SQL Server Profiler and captured the error pasted below:

Trace

Image

sqlserverprofiler-20170130-0154pm-brushed-up

 

Textual

The create unique Index Statement terminated because a duplicate key was found for the object ‘dbo.sysowners’ and the index named ‘nc1’.  
The duplicate key value is (spotlight).

 

msdb

Knowing that msdb is the foundational database took a look at it.

sysusers

Code


use [msdb]
go

select *

from   sysusers tblSU

--where  lower(tblSU.[name]) like '%spot%'

order by 
		lower(tblSU.[name])


 

Output

sys-sysusers-20170130-0321pm

 

Explanation

  1. In the msdb database
    • We have two distinct users Spotlight and spotlight
    • This is possible as out server collation

 

Validate Server Collation

Code


print 'Collation: ' + cast( serverproperty('collation') as varchar(60))

Output

servercollation-20170130-0324pm

 

Validate Database ( msdb ) Collation

Code


print 'Collation - SQL Instance: ' + cast( serverproperty('collation') as varchar(60))

print 'Collation - Database - msdb - ' + CONVERT (varchar, DATABASEPROPERTYEX('msdb','collation'));  

print 'Collation - Database - ReportServer - ' + CONVERT (varchar, DATABASEPROPERTYEX('ReportServer','collation'));  

print 'Collation - Database - ReportServerTempdb - ' + CONVERT (varchar, DATABASEPROPERTYEX('ReportServerTempDB','collation'));  


Output

collation-view-20170130-0330pm

 

Explanation

  1. On our embryonic database, msdb, it is OK to have users whose name only vary based on case
    • BTW, for this server whose collation is binary, case materializes
  2. But, on new to become database which is case-insensitive, it is not OK

 

 

Listening

To me it is a cute problem, but don’t ask my lover

As, she is the only one who makes herself pretty for me

Bellamy Brothers
Let Your Love Flow
Link

Microsoft Connect

Opened up a Connect Item

  1. Title :- Reporting Services – Configuration – Index Creation error on table dbo.sysowners – The index name nc1
    ID :- 3120021
    Date Created :- 2107-Jan-30th
    Status :- Active
    Link