Case sensitive DB issue

[BrubiSQL]

Hello,

I've stumbled up on a weird error regarding Case sensitivity of a database.
I have a DB witch is case sensitive, and there are some objects in it with same names but different casing.
And when DBA Dash is doing it's schema scans, his scan breaks when he stumbles on to one of those same named objects.

Error from DBA Dash:

Snapshot of database error - Import[Retrying]:

> System.Exception: DDLSnapshot:DB_name. Primary key violation.  This can occur if you have a case sensitive database collation that contains tables, SPs or other database objects with names that are no longer unique with a case insensitive comparison.
>  ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK__#A9141F5__CDB0B3FC84964B86'. Cannot insert duplicate key in object 'dbo.@ss'. The duplicate key value is (Super_Secret_SP_name, dbo, P  ).
> The data for table-valued parameter "@ss" doesn't conform to the table type of the parameter. SQL Server error is: 3602, state: 30
> The statement has been terminated.
>    at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
>    at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
>    at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
>    at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
>    at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
>    at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
>    at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
>    at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
>    at DBADash.DBImporter.UpdateSnapshot(String tableName, String databaseName) in D:\a\dba-dash\dba-dash\DBADash\DBImporter.cs:line 329
> ClientConnectionId:5d7d7901-ab70-4458-9923-b7237fc3f159
> Error Number:2627,State:2,Class:14
>    --- End of inner exception stack trace ---
>    at DBADash.DBImporter.UpdateSnapshot(String tableName, String databaseName) in D:\a\dba-dash\dba-dash\DBADash\DBImporter.cs:line 334
>    at DBADash.DBImporter.<>c__DisplayClass11_3.<Update>b__7(Context context) in D:\a\dba-dash\dba-dash\DBADash\DBImporter.cs:line 256
>    at Polly.Policy.<>c__DisplayClass110_0.<Execute>b__0(Context ctx, CancellationToken _)
>    at Polly.Policy.<>c__DisplayClass138_0.<Implementation>b__0(Context ctx, CancellationToken token)
>    at Polly.Retry.RetryEngine.Implementation[TResult](Func`3 action, Context context, CancellationToken cancellationToken, ExceptionPredicates shouldRetryExceptionPredicates, ResultPredicates`1 shouldRetryResultPredicates, Action`4 onRetry, Int32 permittedRetryCount, IEnumerable`1 sleepDurationsEnumerable, Func`4 sleepDurationProvider)
>    at Polly.Retry.RetryPolicy.Implementation[TResult](Func`3 action, Context context, CancellationToken cancellationToken)
>    at Polly.Policy.Implementation(Action`2 action, Context context, CancellationToken cancellationToken)
>    at Polly.Policy.Execute(Action`2 action, Context context, CancellationToken cancellationToken)
>    at DBADash.DBImporter.Update() in D:\a\dba-dash\dba-dash\DBADash\DBImporter.cs:line 255

Running queries - Collect:

System.Exception: Error collecting plans for Running Queries
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
 ---> System.ComponentModel.Win32Exception (258): The wait operation timed out.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at DBADash.DBCollector.CollectPlans() in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 523
   at DBADash.DBCollector.Collect(CollectionType collectionType) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 498
ClientConnectionId:881441b6-3391-4d1c-a120-dd3d2cc2978b
Error Number:-2,State:0,Class:11
   --- End of inner exception stack trace ---

ObjectExecutionStats - Collect[Retrying]:

Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
 ---> System.ComponentModel.Win32Exception (258): The wait operation timed out.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at Microsoft.Data.SqlClient.SqlDataReader.Read()
   at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at DBADash.DBCollector.GetDT(String tableName, String SQL, Int32 commandTimeout, SqlParameter[] param) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 1071
   at DBADash.DBCollector.AddDT(String tableName, String sql, Int32 commandTimeout, SqlParameter[] param) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 1081
   at DBADash.DBCollector.ExecuteCollection(CollectionType collectionType) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 906
   at DBADash.DBCollector.<>c__DisplayClass59_0.<Collect>b__0(Context context) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 475
   at Polly.Policy.<>c__DisplayClass110_0.<Execute>b__0(Context ctx, CancellationToken _)
   at Polly.Policy.<>c__DisplayClass138_0.<Implementation>b__0(Context ctx, CancellationToken token)
   at Polly.Retry.RetryEngine.Implementation[TResult](Func`3 action, Context context, CancellationToken cancellationToken, ExceptionPredicates shouldRetryExceptionPredicates, ResultPredicates`1 shouldRetryResultPredicates, Action`4 onRetry, Int32 permittedRetryCount, IEnumerable`1 sleepDurationsEnumerable, Func`4 sleepDurationProvider)
ClientConnectionId:881441b6-3391-4d1c-a120-dd3d2cc2978b
Error Number:-2,State:0,Class:11

[DavidWiseman]

The DBA Dash repository DB uses a case insensitive collation so there will be an issue if you have a case sensitive DB with objects that have the same names with different casing. If you have a choice I would recommend against using differences in casing to distinguish between different object - it's likely to confuse a human at some point.
The other option would be to exclude the database from the schema snapshot process.

The other two errors are timeouts. Those collections shouldn't normally timeout so if it occurs regularly it might need some investigation. If you want to run the queries manually to check how long they take to run:
RunningQueries
ObjectExecutionStats

It's also possible to adjust the query timeouts, but the RunningQueries and ObjectExecutionStats collections run frequently so its worth identifying the root cause if they are regularly slow.

There is an option in the service configuration tool "Log Internal Performance Counters". If this is enabled, it will log the execution times of all the collections and these will be available on the "Metrics" tab with the performance counters. The PerformanceCounters table will increase in size with more data collected.

[BrubiSQL]

Thank you David :)
Unfortunately it's legacy DB which I can not influence, so it is what it is... I've excluded schema scans for CS databases.
@timeout - thank you, didn't even notice that feature in the new version! :)