Run a SQL Server job in intervals of seconds

One the projects I work required a script to monitor sys.sysprocesses table to capture activity in the database.

At this point my thoughts were running around and I was looking for some scripts on the web (to avoid reinvention of the wheel). At last, I found a script written by Chad Boyd –  check the article here sp_who for Sql 2005 (sp_who2k5).
 
Now what? I had to tweak the above script so that it could be joined up with sys.sysprocesses table instead of sys.dm_exec_sessions. The reason why I did this was to ensure I get information for all the spid’s listed in the sys.sysprocesses table, unlike the sys.dm_exec_sessions which gives only information of running spid. Now I was little perplexed on how to poll the using the above tweak script in seconds.

Well, I was telling myself "Don’t worry, dude!, you will have the breakthrough". And yes, I did have the break when I learnt that we could schedule a SQL Agent Job to run in seconds by setting the value of the parameter @freq_subday_type to 2. If you set this value the Agent job runs every few seconds and by setting the  @freq_subday_interval.

I have the job running for past few weeks and gathering all the information I need from the system table, DMV and DMF’s. I hope this article helps you all!

Happy reading!