Post reply

Query System Tables for Blocking and Replica States

  • March 8, 2017 at 2:36 am

    #335605

    Hi Guys,

    I need a query that returns the number blocked processes/waiting processes, however I want to exclude those that are secondary replicas. This script will run across 3-4 servers where there will be some secondary replicas and some primary and also some that not in any group at all.

    My query that I do have is not bringing back the right results, I know why this is, but not sure I can get to how to fix it. 

    select
     count(blocked) as total_behind_this,
     blocked as blocked_by_spid,
     lastwaittype as last_wait,
     ltrim(rtrim(loginame)) as login_name,
     databases.name as [database_name]
     from sys.sysprocesses sysprocesses
     inner join sys.dm_hadr_database_replica_states database_replica
    on sysprocesses.dbid=database_replica.database_id
     inner join sys.sysdatabases databases on  sysprocesses.dbid=databases.dbid
     where blocked > 0
     and  is_primary_replica =1
      group by blocked, lastwaittype, loginame, databases.name
     order by count(blocked) desc

    The inner join  sys.dm_hadr_database_replica_states means its only returning those that ARE in a avaibility group. Where as I want those that ARE in a AG AND is_primary_replica=1 AND those that are not in a group at all.

    Could some, if possible, point me in the right direction?

    Cheers
    Alex

  • March 8, 2017 at 8:03 am

    #1932312

    Actually, I think I have it sussed... would someone mind double checking -- it *seems* to return the results I would expect, cross checking against the databases manually...

     GO
     with NormalDatabaseAndPrimaryReplica as
     (
     select
      databases.dbid,
      databases.name as database_name
     from master.sys.sysdatabases databases
     full outer join master.sys.dm_hadr_database_replica_states replicas
     on databases.dbid = replicas.database_id
     where (replicas.is_primary_replica = 1 OR replicas.database_id is null)
     ) select
     count(blocked) as total_behind_this,
     blocked as blocked_by_spid,
     lastwaittype as last_wait,
     ltrim(rtrim(loginame)) as login_name,
     validDatabases.database_name as [database_name]
     from sys.sysprocesses sysprocesses
     inner join NormalDatabaseAndPrimaryReplica validDatabases on sysprocesses.dbid = validDatabases.dbid
     where blocked > 0
     group by blocked, lastwaittype, loginame, validDatabases.database_name
     order by count(blocked) desc

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply