May 31, 2012 at 7:48 am
Ive just run a very large delete statement in a stored procedure that deletes data from around 15 tables. Everything was wrapped in a transaction and it executed without any errors being generated. The problem now is that generating a select statement with the parameters i used to delete the data (on the same tables) is causing enterprise manager to sit executing the query without end ! I would expect it to return with no results. Instead it just carries on executing the query, but doesn't return. Any ideas what could have caused this ?
May 31, 2012 at 7:53 am
Did you commit or rollback the transaction? Sounds like blocking to me.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2012 at 7:59 am
yeah I included a commit statement and a rollback in a try catch construct if there was an error
May 31, 2012 at 8:00 am
how would i find out if its blocked ?
May 31, 2012 at 8:09 am
This will show you the current open transactions.
select * from sys.dm_tran_active_transactions
If you still have the same SSMS window open you can see if your transaction did not complete.
select @@TRANCOUNT
I suspect you did not correctly handle the commit/rollback in your try/catch. Can you post the code you ran?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2012 at 8:14 am
I think the problem is that I initially commented out the commit statement, that seems to have caused the problem. How can i kill the locks ? can I kill the process ? how do i find out which process is causing the lock ?
May 31, 2012 at 8:17 am
sp_who2 or sys.dm_exec_requests/sys.dm_exec_sessions will get you the information you need.
if the query window was closed and didnt commit it will be rolling back the transaction. if the query window hasnt been closed just go back to that window and issue a commit transaction or rollback transaction depending what you want.
May 31, 2012 at 8:24 am
it was in a stored procedure
May 31, 2012 at 8:27 am
Then you need to close the connection that called it. That will cause an implicit rollback.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2012 at 8:57 am
finally sorted it, closing down management studio released the transaction, its back to normal now, thanks for your help though.
May 31, 2012 at 9:00 am
You're welcome. Closing SSMS closed that connection and therefore implicitly rolled back your transaction. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply