July 9, 2014 at 4:36 am
Hi Folks,
Can someone help me with a script or how to know where my data in a table comes from.
Thanks,
E.O
July 9, 2014 at 4:53 am
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
July 9, 2014 at 4:59 am
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
July 9, 2014 at 6:01 am
Could try Change Data Capture?
July 9, 2014 at 6:56 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 9, 2014 at 7:17 am
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
July 9, 2014 at 7:25 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 9, 2014 at 7:36 am
Thanks for the information. Can I be helped with the script for such task using extended event.
E.O
July 9, 2014 at 7:43 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 9, 2014 at 7:53 am
Ok. Thanks Grant and also to everyone that contributed.
EO
July 9, 2014 at 7:53 am
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