SQL Server sp_who2 – What Are These Generic Looking Activities?

sql serversql-server-2008

I am trying to determine whether a sql server instance is actually in use, or just sitting there. It has no user databases, just the normal system databases and distribution. I'm fairly sure there is nothing happening, but it is in our production environment, so I can't just switch it off without being sure.

I have set up an agent job to run sp_who2 and log to a table every minute to see if I can catch any activity. I have the following rows, but nothing else (except stuff with my name on it!). They all look fairly generic – like it's the agent doing it's thing – but I'd just like to check first.

Are these jobs likely to be anything more than generic jobs doing nothing except checking to see if there's something to do?

DBName  Command ProgramName
msdb    AWAITING COMMAND    SQLAgent - Email Logger                                                  
master  SELECT INTO         SQLAgent - TSQL JobStep (Job 0x988FC626DDAAEE4D8F49FC83540F48A5 : Step 1)
msdb    AWAITING COMMAND    SQLAgent - Alert Engine                                                  
msdb    AWAITING COMMAND    DatabaseMail - SQLAGENT - Id<19792>                                      
msdb    AWAITING COMMAND    SQLAgent - Generic Refresher                                             
msdb    AWAITING COMMAND    SQLAgent - Job invocation engine                                         
msdb    SELECT              SQLAgent - Alert Engine                                                  
msdb    AWAITING COMMAND    SQL Management                                                           
msdb    AWAITING COMMAND    SQLAgent - Job Manager                                                   
msdb    UPDATE              SQLAgent - Schedule Saver

Best Answer

All of that appears to be the result of normal Sql Agent activity.

Assuming you don't have any 'real (non-system)' Sql Agent job running (meaning you don't really need the Sql Agent running), my suggestion would be to create a Windows Task scheduler process using SQLCMD to run your sp_who2 process on your defined interval and then STOP the Sql Agent. What gets logged into your table then?

Related Question