Which applications are using my MSSQL
As a database administrator [DBA] or a system administrator [SA], you should keep track of who is connecting to the SQL Server and which applications are connecting to your MS SQL Server.
You will be very surprised by what applications are using your SQL Server. Look for the number of connection through Microsoft Access or Microsoft Excel. These are the one that you will have to worry about. These MS applications can bring your SQL Server to a halt, through bad design or returning all the rows through a Cartesian product [linking a table to another table and displaying all the possible combinations].
Applied to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
Applications
MSSQL provides 2 built-in procedure that will give the information needed:
- sp_who
- sp_who2
- I have connected MS Access to the MS SQL Server, and opened a single table.
- Here are the results:
USE sql911;
go
EXEC SP_WHO;
go
go
EXEC SP_WHO;
go
spid ecid status loginame hostname blk dbname cmd request_id ------ ------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----- -------------------------------------------------------------------------------------------------------------------------------- ---------------- ----------- 1 0 background sa 0 NULL RESOURCE MONITOR 0 2 0 background sa 0 NULL LAZY WRITER 0 3 0 suspended sa 0 NULL LOG WRITER 0 4 0 background sa 0 NULL LOCK MONITOR 0 5 0 background sa 0 master SIGNAL HANDLER 0 6 0 sleeping sa 0 master TASK MANAGER 0 7 0 background sa 0 master TRACE QUEUE TASK 0 8 0 sleeping sa 0 NULL UNKNOWN TOKEN 0 9 0 background sa 0 master BRKR TASK 0 10 0 background sa 0 master TASK MANAGER 0 11 0 suspended sa 0 master CHECKPOINT 0 12 0 background sa 0 master BRKR EVENT HNDLR 0 13 0 background sa 0 master BRKR TASK 0 14 0 sleeping sa 0 master TASK MANAGER 0 15 0 sleeping sa 0 master TASK MANAGER 0 16 0 sleeping sa 0 master TASK MANAGER 0 17 0 sleeping sa 0 master TASK MANAGER 0 18 0 sleeping sa 0 master TASK MANAGER 0 19 0 sleeping sa 0 master TASK MANAGER 0 20 0 sleeping sa 0 master TASK MANAGER 0 21 0 sleeping sa 0 master TASK MANAGER 0 51 0 sleeping SQLSERVER\froggy SQLSERVER 0 master AWAITING COMMAND 0 52 0 runnable SQLSERVER\froggy SQLSERVER 0 sql911 SELECT 0 53 0 sleeping SQLSERVER\froggy XP-PRO 0 master AWAITING COMMAND 0 54 0 sleeping SQLSERVER\froggy XP-PRO 0 adp1SQL AWAITING COMMAND 0 55 0 sleeping SQLSERVER\froggy XP-PRO 0 adp1SQL AWAITING COMMAND 0 56 0 sleeping SQLSERVER\froggy XP-PRO 0 master AWAITING COMMAND 0 57 0 sleeping SQLSERVER\froggy XP-PRO 0 master AWAITING COMMAND 0 58 0 sleeping SQLSERVER\froggy XP-PRO 0 adp1SQL AWAITING COMMAND 0 59 0 dormant SQLSERVER\froggy SQLSERVER 0 master AWAITING COMMAND 0 (30 row(s) affected)
- Look at process 53 to 58. These are from MS Access on the XP-Professional computer.
- Opened only 1 table in MS Access and this resulted in 6 connections!
EXEC sp_who2;
go
go
SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID ----- ------------------------------ -------------------------------- --------- ----- ------- ---------------- ------- ------ -------------- ---------------------------------------------- ----- --------- 1 BACKGROUND sa . . NULL RESOURCE MONITOR 0 0 08/27 09:35:06 1 0 2 BACKGROUND sa . . NULL LAZY WRITER 0 0 08/27 09:35:06 2 0 3 SUSPENDED sa . . NULL LOG WRITER 15 0 08/27 09:35:06 3 0 4 BACKGROUND sa . . NULL LOCK MONITOR 0 0 08/27 09:35:06 4 0 5 BACKGROUND sa . . master SIGNAL HANDLER 0 0 08/27 09:35:06 5 0 6 sleeping sa . . master TASK MANAGER 0 0 08/27 09:35:06 6 0 7 BACKGROUND sa . . master TRACE QUEUE TASK 0 0 08/27 09:35:06 7 0 8 sleeping sa . . NULL UNKNOWN TOKEN 0 0 08/27 09:35:06 8 0 9 BACKGROUND sa . . master BRKR TASK 0 0 08/27 09:35:06 9 0 10 BACKGROUND sa . . master TASK MANAGER 0 0 08/27 09:35:06 10 0 11 SUSPENDED sa . . master CHECKPOINT 0 0 08/27 09:35:06 11 0 12 BACKGROUND sa . . master BRKR EVENT HNDLR 0 51 08/27 09:35:06 12 0 13 BACKGROUND sa . . master BRKR TASK 0 0 08/27 09:35:06 13 0 14 sleeping sa . . master TASK MANAGER 0 3 08/27 09:35:06 14 0 15 sleeping sa . . master TASK MANAGER 0 0 08/27 09:35:06 15 0 16 sleeping sa . . master TASK MANAGER 0 0 08/27 09:35:06 16 0 17 sleeping sa . . master TASK MANAGER 0 0 08/27 09:35:06 17 0 18 sleeping sa . . master TASK MANAGER 0 1 08/27 09:35:06 18 0 19 sleeping sa . . master TASK MANAGER 0 0 08/27 09:35:06 19 0 20 sleeping sa . . master TASK MANAGER 0 4 08/27 09:35:06 20 0 21 sleeping sa . . master TASK MANAGER 0 0 08/27 09:35:06 21 0 51 sleeping SQLSERVER\froggy SQLSERVER . master AWAITING COMMAND 0 128 08/27 09:35:35 Microsoft SQL Server Management Studio 51 0 52 RUNNABLE SQLSERVER\froggy SQLSERVER . sql911 SELECT INTO 77 97 08/29 18:39:42 Microsoft SQL Server Management Studio - Query 52 0 53 sleeping SQLSERVER\froggy XP-PRO . master AWAITING COMMAND 16 90 08/29 18:37:10 Microsoft Office 2003 53 0 54 sleeping SQLSERVER\froggy XP-PRO . adp1SQL AWAITING COMMAND 139 269 08/29 18:39:22 Microsoft Office 2003 54 0 55 sleeping SQLSERVER\froggy XP-PRO . adp1SQL AWAITING COMMAND 125 30 08/29 18:39:22 Microsoft Office 2003 55 0 56 sleeping SQLSERVER\froggy XP-PRO . master AWAITING COMMAND 16 19 08/29 18:39:14 Microsoft Office 2003 56 0 57 sleeping SQLSERVER\froggy XP-PRO . master AWAITING COMMAND 0 0 08/29 18:39:07 Microsoft Office 2003 57 0 58 sleeping SQLSERVER\froggy XP-PRO . adp1SQL AWAITING COMMAND 0 0 08/29 18:39:16 Microsoft Office 2003 58 0 59 DORMANT SQLSERVER\froggy SQLSERVER . master AWAITING COMMAND 0 0 08/29 18:39:22 Microsoft SQL Server 59 0 (30 row(s) affected)
- Look at process 53 to 58. These are from MS Access on the XP-Professional computer.
- You can easily see the application: Microsoft Office 2003.
- Opened only 1 table in MS Access and this resulted in 6 connections!
- These are the one you must worry about.
- Was this what you were expecting?