1

To start I am new to digging into .ldf entries, I don't even know how to track any of these logs back to the objects creating them, if someone could explain that in their answer it would be appreciated.

Anyways, I have a SQL Server setup with a few databases, and ran into an issue where my hard drive had filled up unexpectedly.

After some searching I found that the log file (.ldf) for a database had expanded to immense size (500+ GB), while the database itself is only about 20 GB.

All databases are set for full transactional logging, but only this one database has this problem.

Taking a look at the logs and doing some quick type aggregation, I can see a few things.

  1. out of the top 10,000,000 rows in the log file, 55% are LOP_INSERT_ROW followed by 11% LOP_FORMAT_PAGE and 8% LOP_LOCK_XACT

  2. Of those LOP_INSERT_ROW logs, 52% are LCX_HEAP and 46% are LCX_INDEX_LEAF

  3. The unique thing about this database is it had the generic stored procedures sys.sp_MScdc_capture_job and sys.sp_MScdc_cleanup_job run on it at one point.

I disabled these jobs weeks ago but the log file is still gaining size rapidly.

How do I track down what's generating all of these log entries?

Zeckal
  • 145
  • 1
  • 9
  • Does this answer your question? [What is the LOP\_INSERT\_ROWS and LOP\_DELETE\_ROWS operation on a View object?](https://stackoverflow.com/questions/36879516/what-is-the-lop-insert-rows-and-lop-delete-rows-operation-on-a-view-object) – mxmissile Nov 19 '19 at 16:15
  • Not at all, My problem is occurring over weeks of time, not a single transaction. I also don't know how to do much of what was done in that article, like digging for the object ID and other identifying information. – Zeckal Nov 26 '19 at 20:45
  • I also need to start a few steps back from where that question starts. How do I even go about following a transaction log back to the object? – Zeckal Nov 26 '19 at 21:19

0 Answers0