Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Philippines
    Posts
    6

    Unanswered: Capturing sql statement with monitor and historical server

    I've been trying to capture sql statements issued by any users for security reasons. i dont want to use/turn on the security audit cause it has a 25% markup on the ASE performance. Would someone help me to create a view. this is what i've been testing on. if i issue a single insert statement it wouldn't capture it but if I re-run the insert using go 1000 it could capture the statement. i have set the 'event buffers per engine' ,20000 and 'max SQL text monitored',4096. is there any data item i should use?

    hs_create_view sql_text,
    "Process ID" , "Value for Sample",
    "Kernel Process ID" , "Value for Sample",
    "Login Name" , "Value for Sample",
    "Current Stmt Start Time" , "Value for Sample",
    "Current Stmt Elapsed Time" , "Value for Sample",
    "Current Stmt Logical Reads" , "Value for Sample",
    "Current Stmt Physical Reads", "Value for Sample",
    "Current Stmt Page Writes" , "Value for Sample",
    "Current Stmt Batch Text" , "Value for Sample"

    go

  2. #2
    Join Date
    Oct 2003
    Posts
    13
    Why don't you try dbcc sqltext(), this undocumented/unsupported dbcc command will give you a part (may be whole, depending on the size of the sql) of the sql text. If this does not work for you then you can work on monitor server.

  3. #3
    Join Date
    Mar 2004
    Location
    Philippines
    Posts
    6
    Originally posted by gpadhy
    Why don't you try dbcc sqltext(), this undocumented/unsupported dbcc command will give you a part (may be whole, depending on the size of the sql) of the sql text. If this does not work for you then you can work on monitor server.

    I don't want the server errorlog to increase. I just want to monitor the statement(s) issued by a user directly connecting to the DB using isql,sql advantange etc using historical server. I have tried combining data types just to trigger the capturing of issued statements but no luck.

  4. #4
    Join Date
    Oct 2003
    Posts
    13
    philuser,

    dbcc sqltext() can be run with dbcc traceon (3604) to get the results directly by the user instead of going to the error log.

    It depends on what is your objective, if you want capture sql text of one user at a given point of time then dbcc sqltext() is what you need. But, if want to capture all the sql texts for a period of time then monitor server is what you need.

  5. #5
    Join Date
    Mar 2004
    Location
    Philippines
    Posts
    6
    I want to monitor all activities for a period of time. what i have noticed is that if a simple select statement issued on a table less than 1K of rows if could not capture but if the table is more than 5K of rows it could. same thing with the insert. I've been doing all the combinations of views just for the purpose. I have lessen the scan_interval of monitor server to 20ms. is there something that i have to configure in the ase buffers etc?

Posting Permissions

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