I have a problem with one of my SQL Server jobs. I've found that while the job is running, SQL Server Management Studio locks up. It always locks up while I'm looking at tables and sprocs within the "Object Explorer Details" screen.
I am using version 10.0.1600.22 of the SSMS. Incidentally, version 9.00.4035.00, which I also have installed, has no problem with this–I open this one to find the suspended mangagement studio process and kill it.
The job runs a stored procedure which does the following things:
-truncates a table
-pulls a ton of data from various select statements into the truncated table
-inserts & updates data in second table with records from the first table
All of this occurs within a Transaction, and i have set the DEADLOCK_PRIORITY to HIGH. I tried removing this setting but it didn't solve the problem.
The procedure is much to involved to post here, but what should I look for to resolve this problem?
Best Answer
In my case, I was able to solve the problem by reducing the size of the transaction in the stored procedure, ala the comment provided by @Aaron Bertrand. The entire stored procedure was wrapped in one begin/end transaction. As the stored procedure progressed, my management studio instance would lock up. Reducing the transaction to just the critical inserts/updates, and adding some error handling, eliminated the problems I was having with SSMS.