Post reply

SQL Profiler

  • December 18, 2005 at 5:21 am

    #113046

    1. In order to keep auditing of SQL Server on, I want to disallow non- administrators windows/SQL user to stop a trace. How this can be achived through my application (I start the trace through my application)

    2. I am writing traced event in a database table. Every time sql server is started my trace is overwriting the previously logged entries. How I can add the entries in append mode.

    3. I want to fire some stored procedures when user is either stopping SQL Server or shutting down the machine.

     

     

  • December 18, 2005 at 7:42 pm

    #610253

    1. Only a member of the sysadmin fixed server role within SQL Server can stop a trace. So I'm not sure what you mean by this.

    2. What you might be able to do is create a stored procedure that runs when SQL Server starts. Have it check the contents of your trace table and if there are any rows, have it move the rows to a history table. However, the best option is to write to a trace file and then import the trace file at a later time. This, too, will ensure you don't have the overwrite problem (so long as the application is smart enough to handle dynamically change the name on the trace file).

    3. This isn't something that is a great idea. SQL Server can be shutdown in a mode called SHUTDOWN WITH NOWAIT which terminates all user connections immediately and rolls back all active transactions. Databases are NOT checkpointed.

    K. Brian Kelley
    @kbriankelley

  • December 20, 2005 at 1:43 am

    #610522

    Thanks for reply. What I mean by issue no 1 is that a windows user can end a running trace by pressing Crtl+Alt+Del. I want to disallow certain users rights to terminate a process/ event by pressing Crtl+Alt+Del

  • December 20, 2005 at 7:35 am

    #610569

    Look at creating a server side trace using the SQL Server stored procedures and functions. This will run and can write to a trace file on the server without ever using Profiler. Actually, Profiler uses these trace stored procedures itself. Take a look at the sp_trace_* stored procedures in Books Online to get yourself started.

    If you're having trouble defining the trace exactly, do the following, start up SQL Profiler but edit the filter so it does not filter out SQL Profiler. Then, start up another instance of SQL Profiler and set the events, data columns, and filters that you want. When you start the trace in the second instance of SQL Profiler, you'll see the exact trace commands in the first instance.

    K. Brian Kelley
    @kbriankelley

  • December 21, 2005 at 3:08 am

    #610783

    Thanks, I am confident that I can do it now.

     

  • December 21, 2005 at 2:53 pm

    #610971

    I posted an example of the trace that uses sp_trace_ procedures when answering someone' else's question here several days ago. The script is commented, so it is easy to modify it. My post also says how to use SQL Select statement to select from the trace file if you would follow Brian's advice and log to the trace file.

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=243614#bm244298

     

    Regards,Yelena Varsha

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

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