Hot answers tagged troubleshooting
It's a bug. This Connect ticket has a workaround for it from Kendra Little: I was able to work around this issue by temporarily adding a scheduled to the Utility Information collection (@collection_set_id=4) with the SQL command below. After doing this I was able to complete the data collection configuration via the GUI successfully. Afterwards I ...
Use the most_recent_sql_handle in sys.dm_exec_connections to see the last statement that was executed. SELECT t.text, QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid)) + '.' + QUOTENAME(OBJECT_NAME(t.objectid, t.dbid)) proc_name, c.connect_time, s.last_request_start_time, s.last_request_end_time, s.status ...
Have you tried using Adam Machanic's sp_whoisactive? There's an option to get the outer command to see if it really is within a proc. It could be the application is holding open a transaction instead of committing it. Try looking at DBCC OPENTRAN as well.
From comments, I'm guessing you had a client side Command timeout that has aborted the SQL query. This does not rollback the transaction because the connection stays open on SQL Server due to connection pooling. So, you need to use SET XACT_ABORT ON or add some client rollback code See SQL Server Transaction Timeout for all the gory details
Only top voted, non community-wiki answers of a minimum length are eligible