June 11, 2008 at 9:08 am
I know this is a documented feature of 2005. However, we have a 2000 server that won't be migrated for a few more months. We have a process that needs to be run when our accounting department adds new accounts. The problem is that this process is locking up the server to the point that enterprise mangler can't get connected to see what is going on.
So, my question is, is there an equivalent for this 2005 feature for 2000? Maybe something we can install?
June 11, 2008 at 9:17 am
Nope. Nothing like this in SQL Server.
There is a power button, however. 😉
June 11, 2008 at 10:03 am
Can you stop the MSSQLSERVER from Service?
June 11, 2008 at 10:17 am
I was able to stop/restart the service without having to power cycle the server. The server, from an OS standpoint, looked like it was idle. Of course, I don't know what I can see in DAC so the conversation may be moot. What I wanted to see was what caused SQL Server to choke.
June 11, 2008 at 7:58 pm
I just always kept a session open to each of my SQL Servers.
The good news is: This feature is supported on all versions! 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 12, 2008 at 5:58 am
jim.powers (6/11/2008)
...The problem is that this process is locking up the server to the point that enterprise mangler can't get connected to see what is going on.
I would like to know what the folks at Microsoft would say when they hear their beloved EM spelt out as Enterprise Mangler. LOL!
June 12, 2008 at 7:00 am
rbarryyoung (6/11/2008)
I just always kept a session open to each of my SQL Servers.
I had an EM session open before we started the batch. Once the batch was started, everything was shut out, including my EM session.
June 12, 2008 at 7:03 am
goodguy (6/12/2008)
I would like to know what the folks at Microsoft would say when they hear their beloved EM spelt out as Enterprise Mangler. LOL!
Must be my Microsoft keyboard that translated it out like that. 😀
Gotta have a little fun some time!
June 12, 2008 at 7:30 am
jim.powers (6/12/2008)
rbarryyoung (6/11/2008)
I just always kept a session open to each of my SQL Servers.I had an EM session open before we started the batch. Once the batch was started, everything was shut out, including my EM session.
Hmmm, OK, 3 things:
1) There is no reason to presume that an EM "DAC" session wouldn't have the same problems, and many reasons to believe that it would hav the same problems (see below).
2) I had QA sessions, NOT EM, and QA is what you want, because EM has an annoying habit of locking up anytime the SQL server has problems.
3) Shut Out" is a tad inspecific. Do you mean that the sessions was disconnected? Or do you mean that the session appeared to be hung? If it was hung up, were you trying to look at the active sessions/locks display? If so, be advised that that display will virtually always hang EM when there are any problems on your server, you should never use it when there are problems.
Instead, always use QA sessions, and always use "safe" commands that use WITH (NOLOCK), especially with sessions, processes and locks (sp_who and sp_lock are safe). if you execute a command and it seems to take too long to respond, abort it. Always use text mode output, NOT the grid as the grid has to complete all of its output before it will display.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 12, 2008 at 7:55 am
rbarryyoung (6/12/2008)
1) There is no reason to presume that an EM "DAC" session wouldn't have the same problems, and many reasons to believe that it would hav the same problems (see below).2) I had QA sessions, NOT EM, and QA is what you want, because EM has an annoying habit of locking up anytime the SQL server has problems.
3) Shut Out" is a tad inspecific. Do you mean that the sessions was disconnected? Or do you mean that the session appeared to be hung? If it was hung up, were you trying to look at the active sessions/locks display? If so, be advised that that display will virtually always hang EM when there are any problems on your server, you should never use it when there are problems.
Instead, always use QA sessions, and always use "safe" commands that use WITH (NOLOCK), especially with sessions, processes and locks (sp_who and sp_lock are safe). if you execute a command and it seems to take too long to respond, abort it. Always use text mode output, NOT the grid as the grid has to complete all of its output before it will display.
When I say "shut out" I meant that all of our applications started giving errors because they could not connect to the database. EM ultimately ended up timing out but showed the hourglass for several minutes first giving me the impression it had stopped responding.
As far as the batch is concerned, it is a vendor script (probably a stored procedure they are calling) so I don't know for sure what it was trying to do. Next time I'll start up profiler to see if I can capture it. At any point, I was trying to get to Management -> Current Activity to see what was going on. I just started with this employer and haven't seen 2000 since I moved my previous employer to 2005 three years ago (we were early adopters, pre SP1). I have been spoiled by SSMS and Idera's Diagnostic Manager for finding problems in a GUI so I don't remember what to query in QA to find what I'm looking for.
We'll be moving to 2005 in the next month or two so I guess I won't concern myself too much with this. We have another process to follow that, while it takes three hours longer, it does accomplish what we were trying to do.
June 12, 2008 at 9:06 am
In QA, you could query sp_who2, and then use dbcc inputbuffer() to see what they're running.
If it's an issue, when the server starts, open an isql or isqlw connection (no object browser) to the server and leave it there.
June 12, 2008 at 4:15 pm
jim.powers (6/12/2008)At any point, I was trying to get to Management -> Current Activity to see what was going on.
This is exactly what you must not do with EM when you are having problems. It will virtually always hang and then your whole EM is hosed. If you want to investigate this live, it has to be from QA.
I have been spoiled by SSMS and Idera's Diagnostic Manager for finding problems in a GUI so I don't remember what to query in QA to find what I'm looking for.
In QA use: sp_who, sp_lock, and "select * from master..sysprocesses with (nolock)". Look for blocked and blocking processes. The use sp_lock to find the objID they are blocking on. Then use "Select * from {dbname}..sysobjects with (nolock)" to find the object that they are blocking on.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 13, 2008 at 6:50 am
Right now, I'm having the same type of problem. I issued a simple select statement that should have completed sub-second but did not. So I clicked on stop (I'm using SSMS against our 2000 server). It has been trying to cancel the query for 12 minutes now. When I RDP into the server, I'm trying to start QA but it won't connect. I guess I just need to start opening QA when I get here every morning until we upgrade? I know there is a job that is trying to finish at this point so I'm hesitant about re-starting the process but I'm not sure what else I can do since I can't get QA to connect.
June 13, 2008 at 9:05 am
jim.powers (6/13/2008)
I guess I just need to start opening QA when I get here every morning until we upgrade?
That would be my advice, yes.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply