SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Wading through a profiler trace trying to connect the dots and find the source or parent of one...


Wading through a profiler trace trying to connect the dots and find the source or parent of one particular statement

Author
Message
shiftbit
shiftbit
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
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?

GilaMonster
GilaMonster
SSC Guru
SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)

Group: General Forum Members
Points: 299383 Visits: 46697
That looks like an index rebuild, and the maintenance plans do report Management Studio as their application.
Check maintenance plans. I bet you have an index maintenance job scheduled around the problem time

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


shiftbit
shiftbit
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
Awesome! I didn't even consider this. Yes, there is an index maintenance plan and its job. When I checked its schedule and saw that it runs at the same time that these problems crop up, I knew I had it nailed. Wow, thank you! This has been the bane of my existence for the last few weeks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search