Where does my data comes from?

  • Hi Folks,

    Can someone help me with a script or how to know where my data in a table comes from.

    Thanks,

    E.O

  • Can you be a bit more specific? Do you mean like auditing?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Yes, something like that. I have a table that was created by previous developers. Current development shows that some of the data from the table is incorrect. So I want to know where they pull the data from so I can start troubleshooting.

    Thanks.

    E.O

  • Could try Change Data Capture?

  • If I understand what you're asking, then I'd suggest either using extended events or a server-side trace[/url]. You can capture the queries and with the queries you can tell what tables are being accessed.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Just curious, have you tried talking to the dev teams?

    Another quick option is to query the system catalog sys.sql_modules for any procs that have the table name in the definition?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Yeah, or sys.dm_exec_sql_text to see the queries currently in cache. You'll want to combine that with sys.dm_exec_query_stats just to make things a little easier to put together.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks for the information. Can I be helped with the script for such task using extended event.

    E.O

  • I'd suggest going to the link I provided and just following the instructions there. Here's a sample:

    CREATE EVENT SESSION [QueryMetrics] ON SERVER

    ADD EVENT sqlserver.rpc_completed(

    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2012'))),

    ADD EVENT sqlserver.sql_batch_completed(

    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2012')))

    ADD TARGET package0.event_file(SET filename=N'C:\Data\MSSQL11.RANDORI\MSSQL\Log\QueryMetrics.xel',max_file_size=(5120),max_rollover_files=(2))

    GO

    That captures two events, rpc_completed and sql_batch_completed. I have filters in place so I only capture information for one database. The target is output to a file that's limited to 5gb and 2 files. You can adjust as needed. That will output XML and you'll need to query that or load it into a table to be queried. Again, the link I provided has tons and tons of information on how to get all this done.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Ok. Thanks Grant and also to everyone that contributed.

    EO

  • slight mod on Grants

    CREATE EVENT SESSION [QueryMetrics] ON SERVER

    ADD EVENT sqlserver.rpc_completed(

    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2012'))),

    ADD EVENT sqlserver.sql_batch_completed(

    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2012'))

    AND sqlserver.client_app_name <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')

    ADD TARGET package0.event_file(SET filename=N'C:\Data\MSSQL11.RANDORI\MSSQL\Log\QueryMetrics.xel',max_file_size=(5120),max_rollover_files=(2))

    GO

    Intellisense Creates a ton of noise.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 11 posts - 1 through 10 (of 10 total)

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