2

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?

Improve this question
4
  • 2
    You can start by ensuring that your transaction is held open for the absolute minimum amount of time. It can be a common mistake to start a huge process with BEGIN TRANSACTION and not commit until the end, even if the process includes a lot of things that are time-consuming but not necessary to be part of one massive transaction.
    – Aaron Bertrand
    Commented Aug 24, 2012 at 18:17
  • I would also try to see if there are less locks taken by more modern versions of Management Studio (2008 R2, 2012).
    – Aaron Bertrand
    Commented Aug 24, 2012 at 18:18
  • And patch your client; that is the RTM release of 2008 and there are 3 service packs available.
    – Jason Cumberland
    Commented Aug 25, 2012 at 15:29
  • You might also want to try adding 'with nolock' phrase to all FROM s in the queries running in the job as part of the stored proc.
    – Sky
    Commented Aug 27, 2012 at 10:12

1 Answer 1

Reset to default
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.