Command/utility to monitor currently running SQL? 2

[SantaMufasa]

We are encountering occasions when SQL seems to spin out of control, so much so that the SQL Server refuses new connections.

We would like to monitor the SQL that is executing at these unfortunate times.

Does anyone have suggestions of either code, utilities, or other methods for isolating "over-running" SQL?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[mrdenny]

Dave,
Is this for SQL 2000 or SQL 2005?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SantaMufasa]

Denny,

Sorry I didn't confirm that in my OP...it is 2005.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[mrdenny]

Log into the server with sqlcmd using the DAC (Dedicated Admin Connection) by using the -A switch. This "should" allow you to connect to a SQL Server which has spun out of control. Also you can usually connect from the servers console as SQL will usually respond to Named Pipes even if it's not responding to TCP/IP (local host connections use Named Pipes by default).

I like to use Quest Spotlight on SQL Server to monitor my SQL Servers. It maintains a connection to the SQL Server and you are able to view some information this way.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SantaMufasa]

Thanks, Denny, re: the connection advice (when server does not otherwise allow connections).

Do you have a suggestion for code, utilities, or other methods for viewing currently running SQL once we make the connection?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ptheriault]

Dave,
One of the best tools I use to monitor and capture database stats on performance is SQL Diagnostics Manager by Idera. It will collect every stat there, raise and send alerts when thresholds are met or exceeded. It stores history in a database that you can later go back and report on.

You can check it out here.

http://www.idera.com/Products/SQLdm/Default.aspx

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

The Dedicated Admin Connection is fairly limited. You can only connect with sqlcmd, so you have to manually run T/SQL commands to find the offending spids and kill them to try and get the SQL Server back.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SantaMufasa]

Thanks to both of you for your very valuable posts. Resultingly, you are both now

struck.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]