Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to check the status of SQL Server. When I say status its not the status for each database, but rather for the whole SQL Server.

I need to know what are the queries running in the server and no. of connections, which query is taking too long.

Can anyone please help me with this..

share|improve this question

migrated from stackoverflow.com Feb 27 '12 at 13:20

This question came from our site for professional and enthusiast programmers.

    
you might want to say which exact version of mssql server you've got installed –  Gabriel Feb 27 '12 at 7:37
    
You can check from there. I am using that one. You can get information on SQL Server in detail. Who is Active –  Thit Lwin Oo Feb 27 '12 at 7:38
    
Flagged for dba.se, but perhaps need more info... –  gbn Feb 27 '12 at 7:39
    
if you are using PHP myAdmin there is a tab called processes to check these details –  rajesh.adhi Feb 27 '12 at 7:39
    
I have only experience with 2008 R2 version, and I use the shipped profiling tools that are described here. There's a plenty good chance someone who knows more than me will answer, but I wanted to help out if I could. –  Gabriel Feb 27 '12 at 7:42

5 Answers 5

You'd have more options here:

There are also many other external monitoring tools (some free, some paid) that you can use, but these ones are free and worth mentioning as a first step in your work.

share|improve this answer

I would recommend you using sp_Whoisactive. That store procedure can give variuos ínformation about what mssql.

To say I like SP_WhoIsActive is an understatement. This is probably the most useful and effective stored procedure I’ve ever encountered for activity monitoring. The purpose of the SP_WhoIsActive stored procedure is to give DBAs and developers as much performance and workload data about SQL Server’s internal workings as possible, while retaining both flexibility and security. It was written by Boston-area consultant and writer Adam Machanic, who is also a long-time SQL Server MVP, a founder of SQLBlog.com, and one of the most elite individuals who are qualified to teach the Microsoft Certified Master classes.

See more information here

or you can also see more information like said in the comments here

share|improve this answer
    
To add to this suggestion, Kendra has a useful post of collecting data using sp_WhoIsActive. Take a look! –  yogirk Feb 27 '12 at 13:57

When you say "I need to know what are the queries running in the server and no. of connections, which query is taking too long. "

There is a DMV for this: sys.dm_exec_requests.

Note: You must have SQL 2005 or greater.

share|improve this answer

To get information about the queries, you can use SQL Server Profiler (included in the paid SQL Server editions out of the box).

It can show you (for example):

  • the exact SQL that was used
  • how long did each query take
    (so you can find the long-running ones - for example, you can tell it to log only those queries that took longer than X)

Here are some links to get started:

share|improve this answer
SELECT DB_NAME() AS DatabaseName, DATABASEPROPERTYEX('master', 'Status') AS DBStatus

The DATABASEPROPERTYX function only allows you to see one element at a time, but this maybe helpful if that is all you need.

Here we can see the Status for the master database by issuing the above query.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.