Is there a way to know what process is consuming the log files?

Question

Is there a way to know what process is consuming the log files?

ricardo_chicas

SSCertifiable

Points: 5024
More actions
October 29, 2013 at 12:07 pm

#395738

Hello All
I am trying to build a few alerts and I want to detect when a process is misbehaving and creating a log file that is too big, we have a very large multi database enviroment so I just want to add some preventions tools
The idea is not only for the tempdb log it is also for the log files of each database, I am not sure if it is possible or what tables to use, so I will appreciate any help on this
We are using sql server 2008 R2 on the latest CU
and a few 2012 sp1
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

The transaction log is the log for an entire database. Anything the changes the data in the tables (insert, update, delete, bulk insert, alter index, etc.) will generate log entries and cause the log to grow. If you logs are growing out of control, I have to ask if you're taking regular transaction log backups between your full backups.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 2

they are not growing out of control regularly, I just want to take prevention measures

and the databases are in simple mode, we have another way to take backups

Now what I really want is to identify what processes are consuming it, I know how to do that on tempdb but not on each database

For example we can see an index rebuild consuming the log file, I want to know that the index rebuild is the culprit

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

There isn't really a way. You could run traces or extended events sessions and correlate the times of log growth to operations, but that's impractical for long periods.

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

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 4

ok, thanks!

I will just try doing it from another direction

dan-572483 SSCoach Points: 15870 More actions · Answer 5

Sometimes a stuck or orphaned transaction will prevent the log file from overwriting inself and cuase ti to grow indefinately. If you suspect this is happening, you can run

DBCC OPENTRAN.

If this produces a process ID that has been open from just prior to when the log file growth

began, you can run

sp_who2 <proccessID>

and hepefully learn where the transaction originated from. If you determine that it is orphaned, you can kill the process, and growth should then stop as the log begins to be overwritten from the beginning.