Hello again folks. 

Weve had some pretty grim Ops DB performance over the last few days causing console slowdowns, los off data, the works.

Our DBA has identified the following query causing the locks for several minutes at a time.

CREATE PROCEDURE [dbo].[p_EntityTransactionLogBegin]

(

    @DiscoverySourceId uniqueidentifier,

    @ContextGenerated nvarchar(255) = NULL,

    @TransactionId bigint = NULL OUTPUT

)

AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @Err int;

    DECLARE @LastModified datetime;

    UPDATE [dbo].[DiscoverySource]

    SET [TimeGeneratedOfLastSnapshot] = [TimeGeneratedOfLastSnapshot]

    WHERE [DiscoverySourceId] = '85AB926D-6E0F-4B36-A951-77CCD4399681'

    SELECT @Err = @@ERROR;

    IF (@Err <> 0) 

       GOTO Error_Exit;

      

    SET @LastModified = GETUTCDATE();

    INSERT INTO dbo.[EntityTransactionLog]

    (

        [DiscoverySourceId],

        [ContextGenerated],

        [LastModified],

        [TimeAdded],

        [IsCommitted]

    )

    VALUES

    (

        @DiscoverySourceId,

        @ContextGenerated,

        @LastModified,

        @LastModified,

        0

    );

    SELECT @Err = @@ERROR;

    SELECT @TransactionId = @@IDENTITY;

    IF (@Err <> 0)

        GOTO Error_Exit;

    IF OBJECT_ID('tempdb..#EntityTransaction') IS NOT NULL

    BEGIN

        INSERT #EntityTransaction

        (TransactionId)

        VALUES

        (@TransactionId);

    END

    RETURN 0;

Error_Exit:

    RETURN 1;

END

GO

This seems to be trying to insert half a million rows a time into the DB - no idea why?

Does anyone know how I can locate that discovery ID in the SCOM DB? Ive tried get-scommonitoring object -id without success.

Hi,

Try following:

Get-SCOMDiscovery -Id 85AB926D-6E0F-4B36-A951-77CCD4399681

Cheers

I get no results I'm afraid. Is it possible that this is a temporary GUID?

Hi,

My fault :)

Execute following SQL Query:

SELECT * 
FROM [SCOM].[dbo].[DiscoverySource]
WHERE DiscoverySourceId = 'YourID'

And execute the Powershell Code with the Discovery Rule ID from the Result

Cheers

Update:

I think this issue the same you have.

http://nocentdocent.wordpress.com/2012/05/25/the-road-to-operations-manager-2012-sites-and-gateways/

See following Section there:

Option 1 should be clear by now, to implement option 2 we need to unlink the site from the discovery rule, this way Operations Manager knows it must remove the managed entity (the site itself) and replicate the removal to the data warehouse. To do this we must use the p_RemoveEntityFromDiscoverySourceScope stored procedure and pass the TypedManagedEntityId and DiscoverySOurceId for our site. Incidentally the DiscoverySourceId is sdk generated so you wont find any actual discovery rule associated (Discovery table join on DiscoveryId field), this is appears to be a constant 85AB926D-6E0F-4B36-A951-77CCD4399681, getting the TypeManagedEntityId is then just a matter of proper table joining

Looks complicated.............but I'll give it a try. I dont think we have an orphaned site issue, but that GUID does indeed pop up.

   UPDATE [dbo].[DiscoverySource]

   SET [TimeGeneratedOfLastSnapshot] = [TimeGeneratedOfLastSnapshot]

   WHERE [DiscoverySourceId] = '85AB926D-6E0F-4B36-A951-77CCD4399681'

The query updates TimeGereratedOfLastSnapshot with itself. Why?

Same in p_EntityChangeLogSnapshot. The p_EntityChangeLogSnapshot procedure executes 21.500 times every hour, 93% of the time it takes to execute its locked. For me the update of TimeGeneratedOfLastSnapshot inside p_EntityChangeLogSnapshot seems meaningless.

Hi,

Just out of curiosity, have you installed UR5 by any chance?

/Per

Hi,

I have the same exact issue in my environment. We don't have any orphaned sites as we didn't create any.

We are running OPSMGR 2012 R2 UR4, have not upgraded to UR 5.

The above mentioned  sproc constantly blocks UPDATE to the OPSDB. The console becomes very very slow, even the webconsole.

We dont have the issue in our LAB environment, which is a copy of production.

Any feedback will be appreciated.

Regards

 

I am also seeing the very same issue.  

I have recently upgraded to UR5 and also having the same issue here. I have currently an ongoing case with Premiere support

I am having the same issue.  Constant blocking being caused by p_EntityTransactionLogBegin.  I also see a ton of executions from p_EntityChangeLogSnapshot.

Speaking with Microsoft support, they have no solution.

Has anyone come up with a solution within their environment for this?