Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Where does my data comes from? Expand / Collapse
Author
Message
Posted Wednesday, July 9, 2014 4:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:36 AM
Points: 87, Visits: 348
Hi Folks,

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

Thanks,

E.O
Post #1590660
Posted Wednesday, July 9, 2014 4:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:57 AM
Points: 13,640, Visits: 11,511
Can you be a bit more specific? Do you mean like auditing?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1590665
Posted Wednesday, July 9, 2014 4:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:36 AM
Points: 87, Visits: 348
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
Post #1590668
Posted Wednesday, July 9, 2014 6:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 5:09 AM
Points: 10, Visits: 115
Could try Change Data Capture?
Post #1590692
Posted Wednesday, July 9, 2014 6:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:22 AM
Points: 14,205, Visits: 28,534
If I understand what you're asking, then I'd suggest either using extended events or a server-side trace. 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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1590712
Posted Wednesday, July 9, 2014 7:17 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, December 19, 2014 3:20 PM
Points: 18,064, Visits: 16,099
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
Post #1590724
Posted Wednesday, July 9, 2014 7:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:22 AM
Points: 14,205, Visits: 28,534
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1590726
Posted Wednesday, July 9, 2014 7:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:36 AM
Points: 87, Visits: 348
Thanks for the information. Can I be helped with the script for such task using extended event.

E.O
Post #1590736
Posted Wednesday, July 9, 2014 7:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:22 AM
Points: 14,205, Visits: 28,534
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1590743
Posted Wednesday, July 9, 2014 7:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:36 AM
Points: 87, Visits: 348
Ok. Thanks Grant and also to everyone that contributed.

EO
Post #1590753
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse