You could try Activity Monitor, See which spid is using the most CPU/Memory
Use the dmv sys.sysprocesses
Script 1
Check Status of active transactions
SELECT
tat.transaction_id [TransactionID],
tat.transaction_begin_time [TranBeginTime],
CASE tat.transaction_type
WHEN 1 THEN 'Read/Write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction'
END [TranType],
CASE tat.transaction_state
WHEN 0 THEN 'Not completely initialized'
WHEN 1 THEN 'Initialized but not started'
WHEN 2 THEN 'Active'
WHEN 3 THEN 'Ended(read-only transaction)'
WHEN 4 THEN 'Commit initiated for distributed transaction'
WHEN 5 THEN 'Transaction prepared and waiting for resolution'
WHEN 6 THEN 'Committed'
WHEN 7 THEN 'Transaction is being rolled back'
WHEN 8 THEN 'Rolled back'
END [TranStatus],
tst.session_id [SPID],
tst.is_user_transaction [IsUserTransaction],
s.[text] [MostRecentSQLRun]
FROM
sys.dm_tran_active_transactions [tat]
JOIN sys.dm_tran_session_transactions [tst]
ON tat.transaction_id = tat.transaction_id
JOIN sys.dm_exec_connections [dec]
ON [dec].session_id = tst.session_id
CROSS APPLY sys.dm_exec_sql_text([dec].most_recent_sql_handle) s
ORDER BY
[TranBeginTime]
Script 2
SELECT request_session_id, COUNT (*) num_locks
FROM sys.dm_tran_locks
GROUP BY request_session_id
ORDER BY count (*) DESC
If it is using the logfile it will be deleting,updating or inserting so it must be locking resources.