Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Location
    NJ
    Posts
    2

    Unanswered: How to flush the buffer to trc file

    I want to trace the user logins by using a stored procedure. This script (sp_login_trace) is created by the SQL Profiler tool. (Once this procedure works well, I will use sp_procoption to run it automatically everytime the SQL Server startup.)

    After I successfully created sp_login_trace, I run it (exec sp_login_trace). The trace process is started and TraceID is 1. (I use select * from ::fn_trace_getinfo(default) to verify it). However the file size of login_trace.trc is always 0 even after I use Query Ananlysis or Eneterprise manager to let some users to login into the SQL Server instance. (when I use SQL Profiler to start a trace, the trace file size will increase along with users continaully login in). At that time if I use SQL Profiler to open the login_trace.trc file, the system will give me an error message: No data since Empty File.

    After I stop and delete the trace process, I find that the file size of login_trace.trc becomes 128K and I can see the login records caught by sp_login_trace if I use SQL Profiler to open this file again.

    How can I flush the buffer to trc file frequently without need of stopping trace process?

    Thanks for helps in advance.

    Leon

  2. #2
    Join Date
    May 2004
    Location
    NJ
    Posts
    2
    Here is the script of sp_login_trace (Leon)

    CREATE PROCEDURE sp_login_trace
    AS
    BEGIN
    /************************************************** **/
    /* The following statements contain the SQL Server Profiler-generated */
    /* script to create the trace with the required events and data columns. */
    /************************************************** **/
    -- Create a queue
    DECLARE @rc int
    DECLARE @TraceID int
    DECLARE @maxfilesize bigint
    SET @maxfilesize = 5
    EXEC @rc = sp_trace_create @TraceID output, 2,
    N'C:\Allprojects\SQL_Server_2000\login_trace',
    @maxfilesize, NULL
    IF(@rc != 0) GOTO error

    -- You can't script the client-side file and table.
    -- Set the events.
    declare @on bit
    set @on = 1
    exec sp_trace_setevent @TraceID, 14, 1, @on
    exec sp_trace_setevent @TraceID, 14, 6, @on
    exec sp_trace_setevent @TraceID, 14, 9, @on
    exec sp_trace_setevent @TraceID, 14, 10, @on
    exec sp_trace_setevent @TraceID, 14, 11, @on
    exec sp_trace_setevent @TraceID, 14, 12, @on
    exec sp_trace_setevent @TraceID, 14, 13, @on
    exec sp_trace_setevent @TraceID, 14, 14, @on
    exec sp_trace_setevent @TraceID, 14, 16, @on
    exec sp_trace_setevent @TraceID, 14, 17, @on
    exec sp_trace_setevent @TraceID, 14, 18, @on


    -- Set the Filters
    declare @intfilter int
    declare @bigintfilter bigint

    exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'


    -- Set the trace status to start.
    EXEC sp_trace_setstatus @TraceID, 1

    GOTO finish

    error:
    SELECT ErrorCode=@rc

    finish:
    END

    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •