October 9, 2007 at 3:54 pm
Hi All,
SQL2K, sp2.
I've noticed in the last week several problems with our prod server:
- attempt to expand Current Activity freezes SQL EM
- attempt to open SQL error logs freezes SQL EM
- attempt to open tempdb properties freezes SQL EM
These happen every attempt, morning, noon, night, immediately after re-boot.
Then, there are the really, really slow queries that didn't use to take so long.
We are running a lot of users (400) with 3 connections each, but, there are only 20 or so active (i.e., doing something) at any given time.
I'm not aware of anything done to the system that might be causing this.
I've also just noticed that the query time-out settings on 3 of 4 servers now reads 600 seconds, where it has always been 0 in the past. This points to someone having changed these, and potentially other, settings.
Right now, I need the performance back, then witchhunt.
Any ideas?
Thanks.
October 9, 2007 at 4:03 pm
EM is a client side tool, so it EM freezing on your workstation or on any/all workstations?
Are you rebooting your machine or the server? I can see the logs taking a long time. If you don't reboot SQL very often, then they grow large. Activity can be strange, but you might try a server side trace of your ID, when you open the tool and see if the queries are getting through or if it's your machine.
October 10, 2007 at 3:15 am
At busy-server times I use Query Analyzer instead of EM which tends to lock up at such moments.
sp_who2: show current connections
DBCC INPUTBUFFER (spid): show sql-query
It has also a schema-browser (tools->Object browser)
October 11, 2007 at 9:41 am
Well, it looks like bad app code: there were several hundred open transactions, and the server was simply chocking on the load.
Adding commits after read operations solved the problem.
October 12, 2007 at 3:59 am
I often find EM Current Activity hangs when the server is busy, especially if there is a lot of blocking happening.
You can use QA and sp_who2 to get the same information, or even query master..sysprocesses.
I tend to use Windows Exploer to look at Errorlogs, as EM is very slow or hangs when looking at a large log.
SSMS is far more reliable for these tasks, and can be used to manage SQL 2000 as well as SQL 2005.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply