Post reply

How to monitor/list outDML queries fired on any table

  • September 2, 2008 at 11:04 pm

    #197258

    hi,

    i want to monitor list of queries fired on any table ...a kind og log

    i know this could be done with the help of trigger ...but dont know HOW

    Please help me

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • September 3, 2008 at 12:06 am

    #866344

    Have you tried to use the PROFILER?.

    With this tool you can monitor all the activity in a server, and filter it with a lot of parameters:

    - DatabaseID

    - Login Name

    - use of CPU

    - Duration of the sentences

    - Reads

    - Writes

    - Part of the sentence

    - ...

    And you can gert another valuable information:

    - Plan of execution

    - information about deadlocks

    - ...

  • September 3, 2008 at 12:59 am

    #866352

    i want result as sql query:)

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • September 4, 2008 at 8:06 am

    #867160

    With SS2005 you can use the Dynamic Management Views and Functions to pull the queries executed, as well as execution performance on them, without necessarily resorting to profiler. It may not give you a row-by-row, but it should work for frequency etc.

  • September 4, 2008 at 9:14 pm

    #867538

    Can anybody tell me which dynamic managemant view or function will be used to get information ??

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • September 5, 2008 at 5:25 am

    #867671

    This should give you a good starting point:

    SELECT *

    FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp

  • September 5, 2008 at 5:42 am

    #867673

    Thanks ...but have u run this ..it only giviing system relevant information..

    But buddy i want user databasee specific information

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • September 5, 2008 at 12:49 pm

    #868002

    Sounds like a standard Audit solution on a database?

    Something like this: http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker

Viewing 8 posts - 1 through 7 (of 7 total)

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