How do I determine who or what is adding all the transactions to the transaction log

We use transaction log shipping every 15 minutes plus database backups sent to a backup SQL server as part of our disaster recovery plan.

If I look in the transaction log backup file (using RESTORE HEADERONLY FROM DISK)-- I can see the transaction log backup for any 15 minute period.

At one point in the evening, the transaction log backup jumps from 20mb to 5 gb, then back down to 20mb the next 15 minutes.

I've checked all our maintenance plans and scheduled jobs.  Nothing runs at that time.

I assume it is some user running a recurring job.   How can I investigate this?
Is there any history over a 24hour period that shows transactions attributed to particular users?

Thanks in advance.
dakota5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
You can run the sql server profiler during that period and analyse them
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dakota5Author Commented:
Perhaps I'm doing something wrong, but Server Profiler does not seem to provide that type of information.  I created a new template (server type MS SQL server 2008) but nothing in the events selection table lets you select things as simple as all reads, inserts, updates and deletions.

Anyone have specific experience using Server Profiler to look at user utilization?
0
Aneesh RetnakaranDatabase AdministratorCommented:
File -> new trace ->
 remove the Audit entries

Show all columns
Move right on the above pane  and choose LoginSID, NTDomainName , sessionLoginName
0
dakota5Author Commented:
got it.  Thank you.  The default selection of events for Server Type = server 2008 is fine.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.