Dashboard not loading on Octopus UI

mikepower79 · 8 November 2022 14:11

Hi,
We are seeing an issue where the dashboard for a space is not loading.
We see the below issue:

Nothing is showing up on the Event Viewer on the Octopus server.
Our DBA team are also seeing no issues on the DB side.

Seeing this error in the Diagnostics:

An error occurred while trying to trying fetch the number of active SQL transactions: "Error while executing SQL command in transaction ‘SqlTransactionMetricsProducer.UpdateMetrics’: VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
The user does not have permission to perform this action.
The command being executed was:
SELECT COUNT() FROM sys.dm_tran_active_transactions" System.Exception: Error while executing SQL command in transaction ‘SqlTransactionMetricsProducer.UpdateMetrics’: VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
The user does not have permission to perform this action.
The command being executed was:
SELECT COUNT() FROM sys.dm_tran_active_transactions
—> Microsoft.Data.SqlClient.SqlException (0x80131904): VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
The user does not have permission to perform this action.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption) at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, Boolean isInternal, String endMethod) at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult) at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult) at System.Threading.Tasks.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2 endFunction, Action1 endAction, Task1 promise, Boolean requiresSynchronization) --- End of stack trace from previous location --- at Nevermore.Transient.DbCommandExtensions.<>c__DisplayClass5_0.<<ExecuteScalarWithRetryAsync>b__0>d.MoveNext() --- End of stack trace from previous location --- at Nevermore.Transient.DbCommandExtensions.<>c__DisplayClass5_0.<<ExecuteScalarWithRetryAsync>b__0>d.MoveNext() --- End of stack trace from previous location --- at Nevermore.Transient.RetryPolicy.ExecuteActionAsync[TResult](Func1 func)
at Nevermore.Transient.DbCommandExtensions.ExecuteScalarWithRetryAsync(DbCommand command, RetryPolicy commandRetryPolicy, RetryPolicy connectionRetryPolicy, String operationName, CancellationToken cancellationToken)
at Nevermore.CommandExecutor.ExecuteScalarAsync(CancellationToken cancellationToken)
ClientConnectionId:2a82df1d-eb3f-4e77-b5c5-4b95580bbc43
Error Number:300,State:1,Class:14
— End of inner exception stack trace —
at Nevermore.CommandExecutor.ExecuteScalarAsync(CancellationToken cancellationToken)
at Nevermore.Advanced.ReadTransaction.ExecuteScalarAsync[TResult](PreparedCommand preparedCommand, CancellationToken cancellationToken)
at Octopus.Core.RelationalStorage.RawRelationalTransaction.ExecuteScalarAsync[TResult](String query, CancellationToken cancellationToken, CommandParameterValues args, RetriableOperation retriableOperation, Nullable`1 commandTimeout) in ./source/Octopus.Core/RelationalStorage/RawRelationalTransaction.cs:line 182
at Octopus.Core.Infrastructure.OpenTelemetry.MetricProducers.SqlTransactionMetricsProducer.UpdateMetrics(CancellationToken cancellationToken) in ./source/Octopus.Core/Infrastructure/OpenTelemetry/MetricProducers/SqlTransactionMetricsProducer.cs:line 36
November 8th 2022 13:57:22Error
An error occurred while trying to trying fetch the number of active SQL transactions: “Error while executing SQL command in transaction ‘SqlTransactionMetricsProducer.UpdateMetrics’: VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
The user does not have permission to perform this action.
The command being executed was:
SELECT COUNT(*) FROM sys.dm_tran_active_transactions” System.Exception: Error while executing SQL command in transaction ‘SqlTransactionMetricsProducer.UpdateMetrics’: VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
The user does not have permission to perform this action.

Any idea why the Dashboard is not loading? Is the diagnostic error related to the dashboard not loading?

Kind Regards,
Micheál Power

donny.bell · 8 November 2022 15:49

Hi @mikepower79,

Thank you for contacting Octopus Support. I’m sorry that you are running into this issue.

In order to get a better idea of what is causing this, could you generate a System Diagnostics Report and share it via this secure link?

Let me know once you have uploaded and I’ll take a look.

Best Regards,
Donny Bell

mikepower79 · 9 November 2022 09:50

Hi @donny.bell,
I have uploaded the Diagnostics report.

Kind Regards,
Micheal Power

donny.bell · 9 November 2022 12:18

Hi @mikepower79,

Thank you for providing that.

I’ll have a look and let you know what I find shortly.

Best Regards,
Donny

mikepower79 · 9 November 2022 13:05

Hi @donny.bell,
We found this query that takes 5 minutes to run. \Its suggests Indexes to add but even after we do that we the query still takes nearly 5 minutes to run.

We are currently on version 2021.3.12372, so that maybe the issue too.

This is the query:
– Variable setup for when you want to run this query manually
– DECLARE @dataVersion timestamp
– SELECT @dataVersion = DataVersion FROM ServerTask WHERE Id =
– DROP TABLE #Latest

                                             -- This value is equal to the lowest DataVersion that is used by an uncommitted insert/update
                                             -- It is guaranteed no record can be inserted/update and have a DataVersion lower than this value
                                             -- When there are no open transactions, this value will be larger than the largest DataVersion in the DB
                                             SELECT MIN_ACTIVE_ROWVERSION()

                                             DECLARE @ProjectIds as TABLE (ProjectId nvarchar(400))

                                             -- Narrowing this criteria any further makes it hard to invalidate the cache
                                             INSERT INTO @ProjectIds
                                             SELECT DISTINCT ProjectId
                                             FROM ServerTask t
            WHERE (t.[SpaceId] = @_SpaceId_0_2)
                                                 AND t.ProjectId is not null
                                                           AND t.DataVersion >= @dataVersion

                                             -- Detect changes to the Release (in case the version number changes)
                                             INSERT INTO @ProjectIds
                                             SELECT DISTINCT ProjectId
                                             FROM Release t
            WHERE (t.[SpaceId] = @_SpaceId_0_2)
                                                 AND t.DataVersion >= @dataVersion

                                             -- Detect the deletion of Projects, Releases and Deployments
                                             INSERT INTO @ProjectIds
                                             SELECT DISTINCT ProjectId
                                             FROM Event t
            WHERE (t.[SpaceId] = @_SpaceId_0_2)
                                                 AND t.Category = 'Deleted'
                                                 AND t.ProjectId is not null
                                                           AND t.DataVersion >= @dataVersion

                                             SELECT ProjectId FROM @ProjectIds

                                             DECLARE @cnt int
                                             SELECT @cnt = count(*) FROM  @ProjectIds
                                             IF @cnt = 0
                                                           RETURN

                                             SELECT *
                                             INTO #Latest
                                             FROM (
                                                            SELECT
                                                                           'C' AS CurrentOrPrevious,
                                                                           d.Id as DeploymentId,
                                                                           d.Created,
                                                                           d.ProjectId,
                                                                           d.EnvironmentId,
                                                                           d.ReleaseId,
                                                                           d.TaskId,
                                                                           d.TenantId,
                                                                           d.ChannelId,
                                                                                      d.SpaceId,
                                                                           t.[State],
                                                                           t.HasPendingInterruptions,
                                                                           t.HasWarningsOrErrors,
                                                                           t.ErrorMessage,
                                                                           t.QueueTime,
                                                                           t.StartTime,
                                                                           t.CompletedTime,
                                                                           r.[Version],
                                                                           ROW_NUMBER() OVER (PARTITION BY d.EnvironmentId, d.ProjectId, d.TenantId, d.ReleaseId ORDER BY Created DESC) as [Rank]
                                                            FROM Deployment d
                                                                           INNER JOIN ServerTask t ON t.Id = d .TaskId
                                                                           INNER JOIN [Release] r on r.Id = d.ReleaseId
                WHERE (t.[SpaceId] = @_SpaceId_0_2)
                                                                AND NOT ((t.State = 'Canceled' OR t.State = 'Cancelling') AND t.StartTime IS NULL)
                                                                           AND d.ProjectId in (SELECT ProjectId FROM @ProjectIds)
                                             ) s
                                             WHERE s.Rank = 1

                                                        CREATE INDEX IX_Latest_DeploymentId ON #Latest(DeploymentId)

                                             SELECT *
                                             FROM #Latest
                                             UNION ALL
                                                            SELECT *
                                                            FROM (
                                                                           SELECT
                                                                                          'P' AS CurrentOrPrevious,
                                                                                          d.Id as DeploymentId,
                                                                                          d.Created,
                                                                                          d.ProjectId,
                                                                                          d.EnvironmentId,
                                                                                          d.ReleaseId,
                                                                                          d.TaskId,
                                                                                          d.TenantId,
                                                                                          d.ChannelId,
                                                                                                     d.SpaceId,
                                                                                          t.[State],
                                                                                          t.HasPendingInterruptions,
                                                                                          t.HasWarningsOrErrors,
                                                                                          t.ErrorMessage,
                                                                                          t.QueueTime,
                                                                                          t.StartTime,
                                                                                          t.CompletedTime,
                                                                                          r.[Version],
                                                                                          ROW_NUMBER() OVER (PARTITION BY d.EnvironmentId, d.ProjectId, d.TenantId, d.ReleaseId ORDER BY Created DESC) as [Rank]
                                                                           FROM [Deployment] d
                                                                                          INNER JOIN [ServerTask] t on t.Id = d.TaskId
                                                                                          INNER JOIN [Release] r on r.Id = d.ReleaseId
                                                                           WHERE (t.[SpaceId] = @_SpaceId_0_2)
                                                                                          AND d.Id NOT IN (SELECT DeploymentId FROM #Latest)
                                                                                          AND t.State = 'Success'
                                                                                          AND d.ProjectId in (SELECT ProjectId FROM @ProjectIds)
                                                            ) s
                                                            WHERE s.[Rank] = 1

Kind Regards,
Micheal Power

donny.bell · 9 November 2022 13:22

Hi @mikepower79,

Thank you for getting back to me.

I don’t see anything obvious yet. But I can certainly tell this is a busy Octopus instance by the size of the logs.

Would you be able to run the following SQL query to gather the table sizes and row counts in your Octopus Server db?

-- Gets the size of all the tables
SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    2 desc

In addition, could you run the following SQL to check the fragmentation of the db?

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc

You may share the results of that query in .CSV format via the secure link.

I look forward to hearing back from you.

Best Regards,
Donny Bell

mikepower79 · 9 November 2022 14:18

Hi @donny.bell,
I have uploaded the requested files.

We are also going to upgrade the version of Octopus to 2022.2.8011, which matches the version we have in the cloud. Hoping that this will improve performance issues.

Kind Regards,
Micheal Power

donny.bell · 9 November 2022 14:32

Hi @mikepower79,

Thank you for getting back to me.

Upgrading is not a bad idea as I didn’t see anything too horrible in the SQL query results. Of note was IX_ServerTask_TaskQueue_PopTask at over 15% fragmentation and PK_VariableSet_Id at over 11%. Ideally, it is obviously better to have lower fragmentation, but I don’t think these are your limiting factor. Upgrading will force a re-index and improve these numbers anyway, however.

If upgrading doesn’t yield better results, would you mind checking the SQL server metrics to confirm everything looks good with CPU/RAM/DISK?

Let me know how it goes once you get a chance to upgrade at your earliest convenience.

Best Regards,
Donny Bell

mikepower79 · 9 November 2022 16:47

Hi @donny.bell,
We have upgraded to the latest version 2022.3.10723.
The dashboard now appears and have to wait until teams test deployments before I can be confident that the issue is fixed.

Since the upgrade I noticed this message:

What does this message mean?

Kind Regards,
Micheál Power

Justin_Walsh · 9 November 2022 18:08

Hi @mikepower79!

Just jumping in for Donny here, as he’s wrapped up for the day as a member of our UK team.

We added that little helper note recently to help head-off issues where higher task caps would lead to connection pool exhaustion in some scenarios. This basically looks at your current task cap and compares it to the connection pool size (which is 100 by default). This is generally a good recommendation to prevent future issues, but not necessarily a pressing one if you are not having connection pool exhaustion issues currently.

When you do have time to fix it, adding the Max Pool Size = 120 directive to your node’s SQL connection string will increase this to the recommended level.

I hope this helps, and please let us know if you have any further questions!

mikepower79 · 10 November 2022 08:28

Hi @donny.bell,
We have upgraded to the latest Octopus version 2022.3.10723.
However we are still getting errors:

I have requested the SQL metrics for CPU/RAM/DISK.

Kind Regards,
Micheál Power

mikepower79 · 10 November 2022 09:55

Hi @donny.bell, @jason.gallup
Since we upgraded the Octopus version we are seeing this error message in the Diagnostics.
There are a number of Missing index’s.

At the moment this is blocking all of our deployments to production.
We are engaging with our DBA team also to try and get the issue resolved.

Kind Regards,
Micheál Power

donny.bell · 10 November 2022 09:59

Hi @mikepower79,

Thank you for getting back to us.

If you can provide me with the raw task log from the System Integrity Check, I can provide you with a SQL script to restore the indexes.

You may upload the raw task log via the secure link.

Let me know once you have uploaded and I’ll get the SQL script generated for you.

Best Regards,
Donny Bell

donny.bell · 10 November 2022 10:17

Hi @mikepower79,

Just letting you know that I see the raw task log and I am working on this for you right now.

Please give me a few minutes and I’ll provide you with a SQL script to clear these up.

Best Regards,
Donny Bell

donny.bell · 10 November 2022 10:55

Hi @mikepower79,

Thank you for your patience. I apologize that it took me a little longer than I anticipated as I need to update my local test instance to match your current version.

I have DM’d you the SQL script to resolve the missing indexes. Please run a fresh System Integrity Check once the script has been run against the db.

Let me know how it goes at your earliest convenience.

Best Regards,
Donny Bell

mikepower79 · 10 November 2022 14:27

Hi @donny.bell,
Thanks for the SQL script.
I ran the script and the missing indexes are resolved.

Thanks for your help.

Kind Regards,
Micheál Power

system · 11 December 2022 14:27

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.