question

Hawkins_IT avatar image
Hawkins_IT asked

How to find how a table is populated?

So I have a report we use in SSMS, that pulls data from a table called Fact_JobLaborCosting_Weekly. The SSMS report still works fine for most of it. However, we found that some of our newer jobs we are not able to select a StartDate in the SSMS report.


So I queried the Fact_JobLaborCosting_Weekly table and noticed the most recent data is from 07-2022, so for some reason it stopped inserting data into the table after 07-2022. So I am trying to find the stored procedure that populates that table to see if there is a hard coded date range or something that needs to be edited.


Any advise or suggestions? Thank you.


Tried searching in sys.sql_modules and other random stored procedures with no luck.


stored-proceduresinsert

insert
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
JohnM avatar image
JohnM answered

I would maybe suggest looking at using a database audit to track any insert activity into that specific table. A profiler trace or extended event also might be useful, maybe.

13 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hawkins_IT avatar image Hawkins_IT commented ·
How do I do a database audit to track insert activity? Will that show me the stored procedures that insert into that table? Just curious since the last data in the table is from 7/22. Sorry for all the questions, I am an SQL novice.
0 Likes 0 ·
JohnM avatar image JohnM Hawkins_IT commented ·

Here's some documentation to get you started.

https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver16

The audit would track anything that does an INSERT statement into that specific table, which then would help you track anything outside of a procedure (like ad-hoc queries coming from an application) that is inserting data. I was thinking of just trying to capture anything that is currently inserting data that would allow you to trace back to a potential source.

If you are trying to find something in the past, that's essentially not possible unless you had some type of audit/logging already configured.

0 Likes 0 ·
Hawkins_IT avatar image Hawkins_IT JohnM commented ·
I am thinking if I can find the stored procedure or source that inserts into that table then I can troubleshoot why it stopped inserting data after 7/22. Thank you.
0 Likes 0 ·
Show more comments
Hawkins_IT avatar image
Hawkins_IT answered

Trying to do a Database Audit on INSERT but I do not have a dropdown option for Audit? See screenshot

sql-audit.png


sql-audit.png (29.6 KiB)
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
You have to specify a server audit first and then associate the database audit to that. The documentation should walk you through it all.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.