Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 Aug 24 '12 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 Aug 24 '12 at 18:18
    
And patch your client; that is the RTM release of 2008 and there are 3 service packs available. –  Jason Cumberland Aug 25 '12 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 Aug 27 '12 at 10:12

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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