As a recap from my last blog post, this is part 2 of the the topic, What cases my tempdb to grow. At this point, you've already created the log table and you have the query to populate said log table, but wait, there's more (Sunday, Sunday Sunday)! We want to put this query into a SQL Agent job and assign a schedule to it. So how do we do that? Follow along below to find out. 

Creating The SQL Agent Job

To create a SQL Agent Job perform the following:

1. Right click on the SQL Server Agent | Jobs node in your SSMS Object Explorer and choose “New Job…”
   
   
2. Give your job a name on the general page
   
   
   
3. On the steps page click the “New…” button and fill out the page as follows. Be sure to insert the query mentioned above inside the “Command” text box:
   
   
   
   1. Press the OK button to complete this action
      
4. Provide a schedule for this SQL Agent Job to run by clicking on the Schedules button and clicking “New…”
   
   Note: This schedule will run every day. It will automatically expire on a certain date so that we don't always fill a diagnostic table with information no longer needed after the initial problem is solved
   
   

Test your set up of the job and schedule

The very first thing to do is to test that the job is functional. We will run the job manually once and then read the table with a select query to ensure that we don’t have some type of technical problem:

To test that the job runs without error, right click on the new job name and choose to “Start Job at Step…”

If you receive an error you can click in the “Message” field for specifics. For example, one reason you might receive an error is if the SQL Agent service is turned off:

To fix this, simply click on the “Start” button off the SQL Server Agent object in the object explorer as shown below:

Once you start the SQL Server Agent re-test that your job can run. You should now receive the following success message:

Let's Test This Out

Finally, you would test that the table actually has data with the following query:

SELECT *

FROM dbo.LogOfTempDBActivity

When run, you should see something similar to the following:

Quick Summary So Far...

Ok, with part 2 you have now have the approach to build your SQL Agent job and a schedule for it. Coming up in part three, we'll put it all together and briefly show how to analyze the data. Stay tuned!

See Part 3!