How to know transaction log file usage by all the SPID(process)

Question

How to know transaction log file usage by all the SPIDs(process)?

In a database transaction log file is often filling. I want to know the LDF files usage by all the open transactions.

Can anyone know this ?

Answer 1

HI,

You can run the following queries to get the info which you are looking for, it will give the info about the logfile usage by all the open transactions with respect to each database on that server.

DBCC SQLPERF('LOGSPACE')

You can run DBCC LOGINFO('DBNAME') to find the active and inactive VLF(can say as Internal pages of Logfile). If the files are active by its status 2 then the there are active transactions on that database.

You can find the active transactions by running DBCC OPENTRAN on a particular database. If active transacations are there then schedule/take a logbackup this will make the log under control.

---

Thanks & Regards, Pramilarani.R

Answer 2

Thanks Pramilarani for your time and reply. I know these three DBCCs. But my question is to know certain amount of space that is associated with a particular SPID in the LDF.

For example like below. I'm seraching if the below kind of result can be get by using combination of any DBCC comnds or DMVs etc.....

SPID  SpaceUsedInLDF

72      3873kb

83     398kb

Answer 3

Hi,

As we all know log file of a database helps us to track all the transaction happening on a particular database with respect to time, the size of the logfile is purely depends with transaction/actions happening on particular database. We cannot find the Log file(LDF File) usuage as per a specific SPID.

We can find the memory usuage, I/O Read/Write etc for a SPID but not the LDF file usuage.

---

Thanks & Regards, Pramilarani.R

Answer 4

SELECT tst.session_id,tdt.*
FROM sys.dm_tran_database_transactions tdt
INNER JOIN sys.dm_tran_session_transactions tst
ON tdt.[transaction_id] = tst.[transaction_id]

---

Jon