THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

SSMS tips: Okay, it’s an SSMS trap!

Boston is not nearby for me to attend Aaron’s presentation on Management Studio Tips & Tricks. But I did enjoy reading his presentation deck, and that reminded me of an SSMS trap I fell into not too long ago. Here I’d like to bring it to your attention so that you don’t have to be as frustrated as I was. And I was mighty frustrated!

 

So if you use SQL Agent jobs, you must have gone through the following motion numerous times in SSMS Object Explorer:

 

Click on the plus sign to expand “SQL Server Agent”

            Click on the plus sign to expand “Jobs”

                        Right click on a job to select “View History”

 

This works very well no matter how many jobs you have as long as you have a reasonable limit on the size of the job history log.

 

Now, one of these days when you are in a hurry, you may right click on “Jobs” under “SQL Server Agent” (instead of right clicking on a specific individual job), and select “View History” just as I did. What happens next depends on how large your job history log is.

 

In my case, the job history log was not excessively large on a per job basis. But we did have a lot of jobs. Unfortunately, the “View History” item on the “Job” rich click menu basically causes SSMS to load the entire job history log for all the jobs and sort them in chronological desc order. The end result in my case was that the workstation became extremely slow in responding to anything – mouse clicks, keyboard entries, etc. I even had trouble bringing up Task Manager. The workstation wasn’t dead, but nothing was responsive. I had to kill SSMS, an act that otherwise should be quick but required a great deal of patience on my part to accomplish.

 

As soon as SSMS was killed, the responsiveness of my workstation was restored and all was well.

 

Obviously, the feature was not designed or programmed properly. You just don’t want to populate user interface with a resultset all at once if that resultset can be very large.

Published Friday, February 12, 2010 9:52 PM by Linchi Shea
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Piotr Rodak said:

It wouldn't hurt if we had an option somewhere in settings of SSMS to specify default period of time for the 'History' windows. Loading all events into the error log window also is an overkill. Usually I would look for last 24 hours if not less.

February 15, 2010 4:46 PM
 

Lars-Erik Eriksson said:

I agree that the feature is not ideally designed, but I think you missed a setting.

On  the node SQL Server Agent>Properties>History

You will find "Limit size of history log"

This is by default set to 1000, so I assume you mistakenly unchecked this.

Another solution which works, but I would not recommended it, would be to add an index to the underlying historytable

 CREATE INDEX IX_Job_ID

                     ON msdb.dbo.sysjobhistory

                     (

                                job_id

                     )

April 28, 2010 7:31 AM
 

Mark Broadbent said:

From time to time do the same sort of thing but think that terminating app is a little OTT for me. I always usually have lot of other query tabs open and I never like forcefully terminating apps if there is another simple way to backout. I'd simply sqlcmd in, identify the ssms spid and kill it. You'll get an error message in your GUI and then you will be back in business (with all your other work intact).

Sometimes for me best practice is not always the quickest to action, but could end up saving you a lot of time.

June 2, 2010 8:16 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement