I have been having an intermittent issue with Microsoft SQL 2005 SQL Server Management Studio (SSMS). For some reason, I have had about a 5% occurrence of the COMMIT command being successful but not committed.
SET PrintStatusID = 2
WHERE PrintStatusID = 1
I always use BEGIN TRAN on my updates in SSMS. I tend to verify the results then separately issue the COMMIT TRAN hence it is commented out. I run the COMMIT separately after the UPDATE/DELETE. So here is the issue on a simple Transaction like the one referenced. When I issue the COMMIT TRAN, it returns 'Command(s) completed successfully.'. However, in about 1 in 20 occurrences I get a report shortly after about Timeouts. Remembering I just did a COMMIT 5 or 10 minutes before, I reissue the COMMIT. I once again get back 'Command(s) completed successfully.'.
This makes no sense to me. I've been trying to have a habit now of hitting the COMMIT TRAN until I get the message:
'Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.'
The thing is it usually gives me that on the first COMMIT re-attempt, meaning there was only 1 Transaction. Any input on why this is happening in SSMS for 2005. I have never had this in previous Query Analyzer versions.
I'll be the first to admit when in a session I truly forgot to do the COMMIT, but these are not instances of forgetting to issue the COMMIT. I've been doing DB work for 16 years now. This has only occurred since I started using the SQL 2005 SSMS.
Thanks for any input you have.