Develop an SQL Server Job from scratch
Thursday, March 27, 2008 12:01 PM
The ability to automate administrative tasks is vital in almost any SQL Server database environment.
In the SQL Server world, this scheduling of work is known as a "Job".
It allows you to perform such tasks as database maintenance, invoke executables, ActiveX script, Integration Services packages, and business transactions.
I'll show you how to develop a SQL Server Job from scratch to monitor the size of the database files on your server.
SQL Agent
The SQL Agent is a Windows service that is installed with SQL Server; it is the application responsible for the scheduling and executing of SQL Server Jobs.
These Jobs include: one or more steps in which operations are performed; different schedules to determine when the Jobs are executed; and alerts set up so that business owners are notified when Jobs are successful, when they fail, or when they complete.
Example
In a previous article, I describe how to use built-in system views in SQL Server 2005 to capture a snapshot of the size of the database files on your system. While it's a great method for illustrating how you can capture this information, it would be better to automatically capture this information at regular intervals and alert the appropriate people if any of the databases grow outside of what is expected.
In this example, I will detail how you can develop a SQL Server Job to capture this data at regular intervals and send a Database Mail e-mail to alert you when one or more databases get too big.
To create a Job, I open SQL Server Management Studio and right-click the Jobs folder under the SQL Server Agent heading and select New Job. This opens the window in Figure A.
Figure A
This screen presents me with all of the available options for creating the Job. I need to give the Job a name and an owner. I also have the option of assigning a category, as well as a description, to the Job. By using the options on the left-hand side of the screen, I can assign steps to the Job, one or more Job schedules, and alerts.
Most of the work of defining a Job occurs in the Steps section. To create a new Job step, navigate to the Steps option on the left side of the New Job menu. Select the New Job button at the bottom of the new window.
When creating a new step, you give the step a name, indicate what type of action will occur (such as a TSQL script, command prompt, or an Integration Services package), and the command involved. The only step in my Job will execute a TSQL script that will load data into the DatabaseFiles table and send an e-mail if any of my databases are over 5 gigabytes. See Figure B.
Figure B
Here is the script used in the Job step:
IF OBJECT_ID('DatabaseFiles') IS NULL
BEGIN
SELECT TOP 0 * INTO DatabaseFiles
FROM sys.database_files
ALTER TABLE DatabaseFiles
ADD CreationDate DATETIME DEFAULT(GETDATE())
END
TRUNCATE TABLE DatabaseFiles
EXECUTE sp_msforeachdb 'INSERT INTO DatabaseFiles SELECT *,
GETDATE() FROM [?].sys.database_files'
IF EXISTS
(
SELECT SizeInMB = CAST(((SIZE * 8.00)/1024.00) AS DECIMAL(18,2))
from DatabaseFiles
WHERE CAST(((SIZE * 8.00)/1024.00) AS DECIMAL(18,2)) > 5000
)
BEGIN
EXECUTE msdb.dbo.sp_send_dbmail
@recipients=N'chapman.tim@gmail.com',
@body='The size of one or more databases have
grown outside of the expected bounds.',
@subject ='Database(s) possibly need attention.',
@profile_name ='Database-mailProfile';
END
In the Advanced section of Job Steps, I can define how to handle when the step of the Job succeeds or fails. I can add logic to skip to a separate step or skip to the next defined step, or quit the Job reporting success or failure. In addition, I can output the results of the Job step to an output file or database table.
I find it really helpful to keep a long running history of Job step successes and failures; this assists me in finding problems that may have occurred in the distant past. See Figure C.
Figure C
I want this to run at regular intervals, so I need to define a schedule for the Job. I want this Job to run at midnight every night, so I really just need to define one schedule. However, you can define as many schedules for a Job as you'd like. See Figure D.
Figure D
This simple example shows you how easy it is to define a SQL Agent Job for SQL Server; it also outlines the basics involved in defining the Jobs. I bet that about 90 percent of the Jobs you'll ever define will be simple ones that involve only one Job step and one schedule.
Secure the "Next-Gen SOA Infrastructure" & "Bringing SOA Value Patterns to Life" whitepapers here
Learn how to achieve better performance and improve your service levels, while driving down IT costs.
There are currently no comments for this post.