Where does my data comes from?

Question

Where does my data comes from?

eobiki10

SSCrazy

Points: 2390
More actions
July 9, 2014 at 4:36 am

#309876

Hi Folks,
Can someone help me with a script or how to know where my data in a table comes from.
Thanks,
E.O

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

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

eobiki10 SSCrazy Points: 2390 More actions · Answer 2

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

Vets5 SSC Eights! Points: 982 More actions · Answer 3

Vets5

SSC Eights!

Points: 982

July 9, 2014 at 6:01 am

#1728001

Could try Change Data Capture?

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

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

eobiki10 SSCrazy Points: 2390 More actions · Answer 7

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

E.O

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 8

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

eobiki10 SSCrazy Points: 2390 More actions · Answer 9

Ok. Thanks Grant and also to everyone that contributed.

EO

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

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