February 3, 2010 at 12:51 pm
We are using a dedicated 2005 server. When I ran a query that was taking awhile to complete (over an hour), no other users could get into the system through management studio.
This seems like strange, bizarre behavior for the server.
Any suggestions as to why this may be occurring?
Only 1 user running 1 query (all other users locked out).
February 3, 2010 at 2:09 pm
Poorly written query performing multiple cross-joins?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 3, 2010 at 3:15 pm
There is not a whole lot to go on here. What edition of SQL 2005 is installed on the server? What kind of locks are there when other users cannot access SSMS? Does the log show any information?
Joie Andrew
"Since 1982"
February 3, 2010 at 3:31 pm
When you say no other users could get in do you mean they couldn't connect, or they were locked out of a table or DB and couldn't function?
It might help if you posted the query.
The server's memory and SQL Server's max memory configuration might be useful to see too.
~Craig
Craig Outcalt
February 3, 2010 at 3:32 pm
Even if it was poorly written, that would lock others out? One query can shut down the entire server?
I've had better performance results in Microsoft Access (with more data records).
February 3, 2010 at 3:34 pm
They couldn't connect.
February 3, 2010 at 3:36 pm
post your max server memory settings from SQL and also post your available memory at the OS level.
Is SQL the only thing running on the machine?
Oh, and your allocated disk space and available disk space
Craig Outcalt
February 3, 2010 at 3:36 pm
lallen-661487 (2/3/2010)
Even if it was poorly written, that would lock others out? One query can shut down the entire server?I've had better performance results in Microsoft Access (with more data records).
Yes it could. It really depends on what the query was. It also depends on the transaction isolation level. It could also depend on if an explicit transaction was started and the commit was never reached. You are looking at lock escalation and this can sometimes cause a timeout to occur when trying to expand objects from SSMS or even login to a server through SSMS. Access doesn't have the same locking mechanisms in place and thus the data is not as well protected there.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 3, 2010 at 3:38 pm
SQLBOT (2/3/2010)
post your max server memory settings from SQL and also post your available memory at the OS level.Is SQL the only thing running on the machine?
Oh, and your allocated disk space and available disk space
Also, post your query and execution plan. Those will also be very helpful in troubleshooting this issue.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply