locked
How to edit trace info FN_TRACE_GETINFO('1') RRS feed

  • Question

  • Hello all,

    Do someone know how to edit trace info from query below, for example, I want to edit value of property from 2 to 4 for security reason, thanks so much

    select *  FROM ::FN_TRACE_GETINFO('1')

    traceid property value

    1    1    2
    1    2    C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_4.trc
    1    3    20
    1    4    NULL
    1    5    1

    Friday, January 10, 2014 9:54 AM

Answers

  • Note that FN_TRACE_GETINFO is deprecated.  I suggest you use sys.traces DMV instead.  It's much easier to interpret.

    Is your question how to specify the 'shutdown on error' option for the default trace?  The short answer is that you can't.  However, you can create your own functionally identical trace with that option and then turn off the default trace using sp_configure.  Importantly, you will want to specify option 6 rather than 4.  That is a bitmask that will specify both rollover file and shutdown options.  If you specify only 4, the server will shutdown as soon as the file reaches the specified size limit.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Sunday, January 12, 2014 3:36 PM grammar
    • Proposed as answer by Sofiya Li Monday, January 13, 2014 6:30 AM
    • Marked as answer by Sofiya Li Sunday, January 19, 2014 1:32 PM
    Sunday, January 12, 2014 3:35 PM

All replies

  • You are talking about the Trace ID

    I really wonder what your "security policy2 may look like. If someone looks for ID = 1 and doesn't find it, does he stop looking?

    Whyt prevents him to simply search for all traces? To look at the Trace Folder/Filename to identify the default trace?

    That's "a bit of obscurity", not "security"

    But if you really care doing that, you could start u some other traces until you are at numer 4 and stop & delete then first 3 Traces. This can be automated with very little work for each startup.


    Andreas Wolter
    Microsoft Certified Master SQL Server 2008
    Microsoft Certified Solutions Master SQL Data Platform, SQL Server 2012
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Friday, January 10, 2014 12:17 PM
  • 1) You can import the data into a table  and edit there 

    select *  into #tmp FROM ::FN_TRACE_GETINFO('1')

    http://technet.microsoft.com/en-us/library/ms173875.aspx



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, January 12, 2014 9:39 AM
  • Note that FN_TRACE_GETINFO is deprecated.  I suggest you use sys.traces DMV instead.  It's much easier to interpret.

    Is your question how to specify the 'shutdown on error' option for the default trace?  The short answer is that you can't.  However, you can create your own functionally identical trace with that option and then turn off the default trace using sp_configure.  Importantly, you will want to specify option 6 rather than 4.  That is a bitmask that will specify both rollover file and shutdown options.  If you specify only 4, the server will shutdown as soon as the file reaches the specified size limit.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Sunday, January 12, 2014 3:36 PM grammar
    • Proposed as answer by Sofiya Li Monday, January 13, 2014 6:30 AM
    • Marked as answer by Sofiya Li Sunday, January 19, 2014 1:32 PM
    Sunday, January 12, 2014 3:35 PM