Post reply

Enabling traceflag 1222

  • September 29, 2009 at 9:19 am

    #226683

    We've got a SQL 2005 database where we're experiencing some blocks once or twice a day. I've enabled the 1222 traceflag via DBCC TRACEON(1222,-1) using SQL Server Query Analyzer. System accepted the command, so far so good.

    My question is - the various BOL and other articles are a little hazy on whether the traceflag will remain in effect after I disconnect fro QA?

    We don't have the ability right now to use the -T in startup as we'd need to recycle the service.

    Thanks!

  • September 29, 2009 at 9:24 am

    #1059549

    AFAIK, you trace flag(s) will remain in place until you stop the service...when you stop and restart, only the flags that are part of the command line for starting the service get turned on, and any of the others you started previously using DBCC would not exist, and have to be re done manually again in a script.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 29, 2009 at 9:40 am

    #1059562

    Good, thanks Lowell.

  • September 29, 2009 at 11:20 am

    #1059626

    Glen Moffitt-317424 (9/29/2009)

    We've got a SQL 2005 database where we're experiencing some blocks once or twice a day. I've enabled the 1222 traceflag via DBCC TRACEON(1222,-1) using SQL Server Query Analyzer.

    Blocks or deadlocks? Traceflag 1222 has nothing to do with blocks, it outputs the deadlock graph to the error log when (if) a deadlock is detected.

    When enabled globally (the -1), the traceflag will remain enabled until someone disables it (DBCC TRACEOFF) or the service is stopped.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 29, 2009 at 5:03 pm

    #1059768

    Gail,

    Thanks for pointing out that distinction. For tracking/gathering blocking information what method would you suggest? We seem to have minor blockages, more in the afternoon, that generally clear (I've got a performance monitor alert that sends an email where we have a lock wait time > 60 seconds..i.e. SQLServer:Locks, Instance = _Total).

    I also have activity monitor up and refreshing on connections on the database for blocked or blocking processes. So once in a while we get a notification, by the time I've gotten to my workstation to look the locks have cleared out. Occasionally though the blocking process does not clear and I have to manually kill it.

    I try and capture screenshots, etc of the blocking process and blocked, for others to review. However I'd like more detailed information, sort of like what is captured with the above mentioned trace flag. We can then send this information to the Vendor for further analysis.

    Thanks,

    Glen

    Followup: I see you are correct, and further reading finds the 'blocked process threshold' in advanced options. So I'll look at implementing that and then use the Blocked Process Report event in Profiler, write that to a log file and examine. We had a few alerts this afternoon from the alert I set up, however by the time I got to it the blocks had cleared. So I've set up the threshold and am running a profiler on it.

  • September 30, 2009 at 1:05 am

    #1059820

    Glen Moffitt-317424 (9/29/2009)

    Thanks for pointing out that distinction. For tracking/gathering blocking information what method would you suggest?

    Query sys.dm_exec_requests. That will show you which connections are blocked and which are blocking. See the wait type and wait resource for what kind of locks and on what. It's relatively easy to track down the start of the blocking chain. Use sys.dm_exec_sql_text to get the commands that the blocked and blocking processes are running.

    If you want more info on the locks held, look at the sys.dm_tran_locks DMV

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

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