locked
SQL Server 2014 track table activity history ? RRS feed

  • Question

  • Hello, is there a way to track query history/activity on particular table(s) within a specific User DB out of the box natively...perhaps cached memory or something else?

    Friday, December 7, 2018 2:35 AM

Answers

  • You can setup a SQL Server Audit to track the activity on a particular table. Below blog has some good info regarding how to do that:

    https://www.sqlshack.com/understanding-sql-server-audit/

    Friday, December 7, 2018 2:49 PM
  • Uri,

    While setting up Audit you can filter users if you want it to be logged for a specific user or users.

    We have it configured for all the users other than Logins used in application and other service account.We dont want to log those activities.Below is the script you can use 

    CREATE SERVER AUDIT [Audit]
    TO FILE 
    ( FILEPATH = N'F:\Audits\'
    ,MAXSIZE = 2 MB
    ,MAX_ROLLOVER_FILES = 500
    ,RESERVE_DISK_SPACE = OFF
    )
    WITH
    ( QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'bb6fb2c3-e9a9-46df-9a8a-4e6bd4bdcbe6'
    )
    WHERE ([server_principal_name]<>N'ABC\UserId' AND [server_principal_name]<>N'[ATBilling]' AND [server_principal_name]<>N'FusionProd' 

    ALTER SERVER AUDIT [Audit] WITH (STATE = ON)
    GO

    Note-This script filter users which you want to exclude but if you want to log audits for specific user then replace [server_principal_name]<>  with [server_principal_name]= in above script.


    Wednesday, December 12, 2018 1:43 AM

All replies

  • You can setup a SQL Server Audit to track the activity on a particular table. Below blog has some good info regarding how to do that:

    https://www.sqlshack.com/understanding-sql-server-audit/

    Friday, December 7, 2018 2:49 PM
  • First option is ofcourse enabling "database audit specifications" and select Audit action type  as (Selects,Deletes,Inserts etc) and chose 'object class'- object and object -tables name.

    But if you dont want to enable auditing then follow below steps.

    You can use below query to get the list of queries which are hitting your database more frequently based on date and execution count.Once you have list of queries you can check dependencies which tables are getting used. 

    SELECT  top 100 QS.execution_count,ST.text,QS.last_execution_time
    FROM    sys.dm_exec_query_stats AS QS
            CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST
    where ST.dbid=(DATABASE_ID)

    order by QS.last_execution_time desc

    Note:Replace DATABASE_ID with actual ID of database you want to get the details from.


    Tuesday, December 11, 2018 3:27 AM
  • Aamir1985

    What is about the specific user as OP requested?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Teige Gao Thursday, December 13, 2018 5:42 AM
    Tuesday, December 11, 2018 5:35 AM
  • Uri,

    While setting up Audit you can filter users if you want it to be logged for a specific user or users.

    We have it configured for all the users other than Logins used in application and other service account.We dont want to log those activities.Below is the script you can use 

    CREATE SERVER AUDIT [Audit]
    TO FILE 
    ( FILEPATH = N'F:\Audits\'
    ,MAXSIZE = 2 MB
    ,MAX_ROLLOVER_FILES = 500
    ,RESERVE_DISK_SPACE = OFF
    )
    WITH
    ( QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'bb6fb2c3-e9a9-46df-9a8a-4e6bd4bdcbe6'
    )
    WHERE ([server_principal_name]<>N'ABC\UserId' AND [server_principal_name]<>N'[ATBilling]' AND [server_principal_name]<>N'FusionProd' 

    ALTER SERVER AUDIT [Audit] WITH (STATE = ON)
    GO

    Note-This script filter users which you want to exclude but if you want to log audits for specific user then replace [server_principal_name]<>  with [server_principal_name]= in above script.


    Wednesday, December 12, 2018 1:43 AM