jump to navigation

Run a SQL Server job in intervals of seconds September 12, 2008

Posted by msrviking in Scripts.
trackback

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!

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: