Hi , Recently my transaction log in QA environment became full and caused an issue. When I looked in to that I found an issue with a stored procedure.The SP is executed for each Employerid (say a 50 times in a for each loop) and does an insert from Stage table to Maintable. The insert has a Begin Transaction with no commit statement. The no of records processed would be maximum 100. As a next step there is process which reads the data from staging and moves that to Archival table. My understanding is this would cause a deadlock,But is it big enough a problem to crash the transaction log space which is around 4 gb. Thanks, Kannan
Well, you have a lot of open (uncommitted) transactions, they're piling up and the log cannot clear. Thus, you have a problem with a growing log which runs out of space at some point. You need to commit or rollback the begin transaction statements!