I have this annoying problem that one particular DB has its TLog filled up each week on the same day. After eliminating the usual suspects, I ran a profiler trace this week around the same time I expected it to happen. Sure enough, it did and I captured lots of activity. Now Im sorting through a 1.5 GB trace file. Ive identified the statement (actually there are several very similar statements). There is a commercial application involved and I see it running and some statements from it, I just cant prove that it issued the statements Im interested in - since they use a login of the service account, whereas the commercial app is using a dedicated sql login.
The events I captured were for SP (including RPC) and TSQL, along with audit login
So I see a statement like this which is one of the likely culprits , and the Login for it is the service account of the SQL instance itself, yet the ApplicationName is "Microsoft SQL Server Management Studio"
INSERT INTO [dbo].[table-name-here] SELECT * FROM [dbo].[table-name-here] option (maxdop 1)
The interesting thing about that line, is that the NestLevel = 1, so I think its safe to say it was contained within some other statement or procedure. The question is, which one and how can I find it. This was during a time when there was nobody at their workstations, so Im pretty confident nobody issues this query through SSMS, especially in light of the fact that its right in among a whole batch of statements at about the same time.
So what would be a general set of steps to identify the "owner" or parent of a particular statement like that above?