locked
Please suggest about Peformance deatails for database issues RRS feed

  • Question

  • Hello Friends,

    Need your help on below things:

    1.is it possible to monitor the application activity at the table or field level.
    2.Is it possible the instance was modified by an individual or backend process or operation/application
    3.is it possible to know who and what was connected to the sql server instance for last 10 days.

    As of now we are not using any extended events and not sure the last option would be the sql server profiler to track the bove mentioned the things.

    Please suggest me on how to proceed further on this and this data is required for application owner.

    Thanks in advance!! 
    Wednesday, June 22, 2016 11:48 AM

Answers

  • Hello,

    1) your best option is to configure the audit system, far better than triggers, because the triggers are synchronous processes so they generate overhead and if they fail the whole query will fail, so go with audit.

    2) I've not understood what you want to do

    3) configure a server side trace to capture login events or use an extended event session

    Wednesday, June 22, 2016 3:15 PM
  • Generally, if you have not configured any auditing, there will not be much. SQL Server is not automagically understand that you ten days later will be asked to provide the information.

    As for the connections for the last ten days, check the SQL Server error log as there is an option for logging successful logins. (But default is to only log login failures.)

    Wednesday, June 22, 2016 9:49 PM

All replies

  • There are certain options for tracking changes to table

    1. DDL, DML triggers

    2. Change tracking

    3 Change data capture

    For 3 rd question unless you store information somewhere there is no option. You can check default trace but I dont know for sure 


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, June 22, 2016 12:12 PM
  • Thank you for your quick response.

    Please note we are not enabled the CDC and extended Events..so how to provide the above information to application team.is it possible or not? if its possible how to do it Please guide me on this.

    i will check for default trance if its enabled.

    Thanks in advance!!

    Wednesday, June 22, 2016 1:39 PM
  • Hello,

    1) your best option is to configure the audit system, far better than triggers, because the triggers are synchronous processes so they generate overhead and if they fail the whole query will fail, so go with audit.

    2) I've not understood what you want to do

    3) configure a server side trace to capture login events or use an extended event session

    Wednesday, June 22, 2016 3:15 PM
  • Generally, if you have not configured any auditing, there will not be much. SQL Server is not automagically understand that you ten days later will be asked to provide the information.

    As for the connections for the last ten days, check the SQL Server error log as there is an option for logging successful logins. (But default is to only log login failures.)

    Wednesday, June 22, 2016 9:49 PM