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

Posted on 2013-10-01
Medium Priority
Last Modified: 2013-10-01
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.
Question by:dakota5
  • 2
  • 2
LVL 75

Accepted Solution

Aneesh Retnakaran earned 2000 total points
ID: 39537121
You can run the sql server profiler during that period and analyse them

Author Comment

ID: 39537754
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?
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39537971
File -> new trace ->
 remove the Audit entries

Show all columns
Move right on the above pane  and choose LoginSID, NTDomainName , sessionLoginName

Author Closing Comment

ID: 39538156
got it.  Thank you.  The default selection of events for Server Type = server 2008 is fine.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question