Is there a task manager of sorts for SQL Server 2008 and on?

Question

Is there a task manager of sorts for SQL Server 2008 and on? A way to see what SQL server is doing, kill runaway queries, etc...

Answer 1

select * 
from sys.sysprocesses

Should show you the processes running. Kill tasks is the

KILL <task> 

command

Note, this view (master.dbo.sysprocesses) is as a backward compatible view, so it might not be here in future SQL releases

Answer 2

You can use the Management Studio and the activity monitor.

Answer 3

Activity Monitor: How to: Open Activity Monitor (SQL Server Management Studio):

Use Activity Monitor to obtain information about SQL Server processes and how these processes affect the current instance of SQL Server.

Activity Monitor is a tabbed document window that has the following expandable and collapsible panes: Overview, Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries. When any pane is expanded, Activity Monitor is querying the instance for information. When a pane is collapsed, all querying activity stops for that pane. You can also expand one or more panes at the same time to view different kinds of activity on the instance.

For the columns that are included in the Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries panes, you can customize the display in the following ways:

• To rearrange the order of the columns, click the column heading and drag it to another location in the heading ribbon.
• To sort a column, click the column name.
• To filter on one or more columns, click the drop-down arrow in the column heading, and then select a value.

To view the Activity Monitor in SQL Server 2005 and SQL Server 2008, a user must have VIEW SERVER STATE permission.

To view the Activity Monitor on a SQL Server 2000 server, a user must have SELECT permission to the sysprocesses and syslocks tables in the master database. Permission to view these tables is granted by default to the public database role.

To KILL a process, a user must be a member of the sysadmin or processadmin fixed server roles.