August 12, 2017 at 3:22 pm
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?
August 13, 2017 at 6:05 am
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
August 14, 2017 at 6:06 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply