Post reply

getting traceflags on remote servers

  • December 31, 2018 at 2:57 pm

    #376709

    Hey everyone,

    I'm struggling to capture traceflags on other servers from a central one without having to resort to using DTC. I've tried using OpenQuery which flat out does not seem to work with DBCC TRACESTATUS, saying it doesn't support metadata discovery and even though I'm able to select the data using EXEC AT, it won't let me put it into a temp table without a distributed transaction. Does anyone have a solution or suggestion for this?

    Thanks in advance!

  • January 3, 2019 at 3:18 am

    #2017831

    diglot - Monday, December 31, 2018 2:57 PM

    Hey everyone,

    I'm struggling to capture traceflags on other servers from a central one without having to resort to using DTC. I've tried using OpenQuery which flat out does not seem to work with DBCC TRACESTATUS, saying it doesn't support metadata discovery and even though I'm able to select the data using EXEC AT, it won't let me put it into a temp table without a distributed transaction. Does anyone have a solution or suggestion for this?

    Thanks in advance!

    You can query the execution plans XML and list out the traceflags from there.
    😎

    This example lists out the traceflags from a single execution plan XML stored in a variable

    ;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
    SELECT
      TRFL.DATA.value('@Value','INT')   AS TRFL_VALUE
     ,TRFL.DATA.value('@Scope','VARCHAR(20)') AS TRFL_SCOPE
    FROM @EXPLAN.nodes('//TraceFlags/TraceFlag') TRFL(DATA);

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

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