none
Enable DBCC Tracestatus on production server

    Question

  • Hello

    We have many production SQL Server 2014 servers running on the field.

    I found that the DBCC Trace status for catching DEADLOCK was not enabled.

    Can the following script works on our next database update? or Do I need specify anything else in particular?

    DBCC TRACEON(1204, -1)
    DBCC TRACEON(1222, -1)
    GO





    • Edited by SimplyS Friday, February 17, 2017 7:51 PM
    Friday, February 17, 2017 7:08 PM

All replies

  • That should do it.

    Here's a nice resource explanation, in case you haven't already seen it:
    Detecting and Ending Deadlocks

    Another option for detecting deadlocks on later versions of SQL is with Extended Events:
    Finding Blocked Processes and Deadlocks Using SQL Server Extended Events

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Friday, February 17, 2017 8:32 PM
  • Thanks and May I know how do I persist this flags after the SQL Server restart? (without modifying the SQL Server startup parameters).

    Is there any default option available for SQL Server 2014?





    • Edited by SimplyS Saturday, February 18, 2017 12:47 AM
    Friday, February 17, 2017 10:48 PM
  • In SQL 2014, the system_health ExtendedEvent session includes the xml_deadlock_report event. So deadlock information should be available by default.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, February 17, 2017 11:58 PM
    Moderator
  • DBCC TRACEON(1204, -1)
    DBCC TRACEON(1222, -1)
    GO

    Hi,

    Yes, the following command will do the job.

    To persist them on reboot, create a SQL Agent job that's scheduled to run on SQL Agent restart

    


    Saturday, February 18, 2017 1:57 AM
  • Those TF are old and might not catch all/complete deadlock graphs as suggested you should rely on extended events and since you have SQL Server 2014 EE would be the best source to get deadlock from. You do not have to enable any TF to get the deadlock information.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Saturday, February 18, 2017 4:48 AM
    Moderator
  • DBCC TRACEON(1204, -1)
    DBCC TRACEON(1222, -1)
    GO

    Hi,

    Yes, the following command will do the job.

    To persist them on reboot, create a SQL Agent job that's scheduled to run on SQL Agent restart

    


    Hallo Sebastian,

    and what will happen if SQL Agent won't start :)

    Two options are available:

    • Startup Parameters
    • Startup Procedure

    I prefer the startup procedure (https://technet.microsoft.com/en-us/library/ms191129.aspx) for implementation because I will fulfill "segregation of duty" because I don't reconfigure any services.

    We enable the following TF (<= SQL 2014) as default on all instances:

    • 1117
    • 1118
    • 2371
    • 3226 (optional)

    For Deadlocks it would be better (IMHO) to use XE instead of traceflags :)


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Tuesday, February 21, 2017 1:57 PM