Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation (database in SIMPLE mode)?

share|improve this question
add comment

1 Answer 1

up vote 7 down vote accepted

No.

If you try the following from two different connections then the second one will be blocked by the first (visible in sys.dm_os_waiting_tasks) but neither will result in any transaction log activity and running DBCC OPENTRAN will report "No active open transactions" (assuming no other activity).

SELECT COUNT_BIG(*)
FROM master..spt_values v1 WITH (TABLOCKX),
     master..spt_values v2 WITH (TABLOCKX),
     master..spt_values v3 WITH (TABLOCKX),
     master..spt_values v4 WITH (TABLOCKX)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.