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: 259015 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: 962 More actions · Answer 3

Vets5

SSC Eights!

Points: 962

July 9, 2014 at 6:01 am

#1728001

Could try Change Data Capture?

Grant Fritchey SSC Guru Points: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

SQLRNNR SSC Guru Points: 281324 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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

eobiki10 SSCrazy Points: 2390 More actions · Answer 9

Ok. Thanks Grant and also to everyone that contributed.

EO

SQLRNNR SSC Guru Points: 281324 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