DBA Dash Error for one of the data type

[IndrajitMurgude]

I am getting below error for one of the target server, I see these error in DBA Dash Errors (24 Hrs)
Microsoft.Data.SqlClient.SqlException (0x80131904): The conversion of a datetimeoffset data type to a datetime data type resulted in an out-of-range value.
I am unable to find for which data collection, this is happening and how this can be fixed.

[DavidWiseman]

Hi,

Sorry I missed this discussion post. When you drill down and it loads the DBA Dash ErrorLog tab, can you provide me with the following info:

• Source
• Error Context
• Message (Click the link and it will open in notepad. Copy/paste the full exception here)

The source should provide the name of the collection which will narrow things down. The error context will let me know if the issue is occuring on the monitored instance or if it occurs when importing into the repository DB. The full error message helps pinpoint where the issue occurs in code.

Thanks

[IndrajitMurgude]

Sorry, I missed you reply too. here are the details -

1. Source - ServerExtraProperties
2. Error Context - Collect and Collect[Retrying]
3. Message -
   Microsoft.Data.SqlClient.SqlException (0x80131904): The conversion of a datetimeoffset data type to a datetime data type resulted in an out-of-range value. at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, 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.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, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(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.GetDT(String tableName, String SQL, Int32 commandTimeout, SqlParameter[] param) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 1226 at DBADash.DBCollector.AddDT(String tableName, String sql, Int32 commandTimeout, SqlParameter[] param) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 1235 at DBADash.DBCollector.CollectServerExtraProperties() in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 1200 at DBADash.DBCollector.ExecuteCollection(CollectionType collectionType) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 924 at DBADash.DBCollector.<>c__DisplayClass62_0.<Collect>b__0(Context _) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 551 at Polly.Policy.<>c__DisplayClass138_0.<Implementation>b__0(Context ctx, CancellationToken token) at Polly.Retry.RetryEngine.Implementation[TResult](Func3 action, Context context, CancellationToken cancellationToken, ExceptionPredicates shouldRetryExceptionPredicates, ResultPredicates1 shouldRetryResultPredicates, Action4 onRetry, Int32 permittedRetryCount, IEnumerable1 sleepDurationsEnumerable, Func4 sleepDurationProvider)
   at Polly.Retry.RetryPolicy.Implementation[TResult](Func3 action, Context context, CancellationToken cancellationToken) at Polly.Policy.Implementation(Action2 action, Context context, CancellationToken cancellationToken)
   at Polly.Policy.Execute(Action2 action, Context context, CancellationToken cancellationToken) at DBADash.DBCollector.Collect(CollectionType collectionType) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 547 ClientConnectionId:fad9712a-6c05-4e9b-b252-92dcdda39d63 Error Number:242,State:3,Class:16

[DavidWiseman]

@IndrajitMurgude I think the error is occurring here:

IF OBJECT_ID('sys.dm_server_memory_dumps') IS NOT NULL
BEGIN
	select @LastMemoryDump=MAX(creation_time),@DumpCount=COUNT(*) 
	from sys.dm_server_memory_dumps
END

The creation_time is a DATETIMEOFFSET and @LastMemoryDump is DATETIME. I think you might have a memory dump returned without a proper date - something outside the scope of DATETIME like '0001-01-01'. You could check on your monitored instance where the error is occurring by running SELECT * FROM sys.dm_server_memory_dumps. It would be interesting to know what the value is that's causing the error but I should have enough info now to fix the problem.

[IndrajitMurgude]

here is the output you asked -
filename creation_time size_in_bytes
/var/opt/mssql/log/SQLDump0001.mdmp 1601-01-01 00:00:00.0000000 +00:00 20172579

[DavidWiseman]

I've got a fix which will be included in the next release. Maybe the date doesn't get recorded properly on Linux instances for some reason.

[DavidWiseman]

Issue #855

[IndrajitMurgude]

Hi David,
I recently updated to version 3.4.2, now, when I click the main DBADash root folder in the UI tool, I get attached error.
I believe this is similar and related to the one server where we have this issue.

Can you please check. Thank you !

[DavidWiseman]

@IndrajitMurgude If you run this in the repository database it should fix the problem:

ALTER TABLE dbo.Summary
ALTER COLUMN LastMemoryDump DATETIME2 NULL

ALTER TABLE dbo.Summary
ALTER COLUMN LastMemoryDumpUTC DATETIME2 NULL

Let me know if the fix works. I'll include it in the next release.

[IndrajitMurgude]

@DavidWiseman, it worked. Thank you !

[DavidWiseman]

Great. Thanks for letting me know.

[DavidWiseman]

This is fixed in 3.5.0