Mac users' indifference toward security 'worrying' http://t.co/MBTwqilk
6 minutes ago by OnlineWebTips on twitter
Breaking News: Yahoo announces Alibaba stake sale
ZDNet / Techguide / Database
Develop an SQL Server Job from scratch
Summary
The ability to automate administrative tasks is vital in almost any SQL Server database environment.
Events
IBM Technology Conference & Expo 2012
May 22, 2012
One World Hotel, First Avenue, Bandar Utama City Centre, 47800 Petaling Jaya, Selangor
Echelon 2012
June 11 and 12, 2012
University Cultural Centre, National University of Singapore
Startup Asia Jakarta 2012
June 7 and 8, 2012
12th Floor, Annex Building, Wisma Nusantara Complex, Jl. M.H. Thamrin No. 59 Jakarta 10350, Indonesia
MMA Forum Singapore
April 23-25, 2012
Grand Hyatt Singapore
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.
Resource Centre Useful content from our premier sponsors
Database TechGuides
Audit data using SQL Server 2005's COLUMNS_UPDATED function
Physically destroy your hard disk for security
Simplify queries with a Dates table
Document SOX compliance with SQL Server 2005
Diagnose performance issues via sp_lock
Build a file location on the fly
Generate dynamic SQL statements in SQL Server
Write a common sorting algorithm
Bulk importing data into SQL Server
Correct data casing in SQL Server
ZDNet Asia Live
RT @Bilafer: 25% of top VC firms investing in China, India or both. Good news for innovation, #SAPAPJ http://t.co/wkCXKkxU
6 minutes ago by businessobjects on twitter
Mac users' indifference toward security 'worrying' http://t.co/AkSlHrCH #cyber #infosec
6 minutes ago by Sec_Cyber on twitter
RT @zdnetasia: Gartner: Mobile CRM gives better ROI than social. http://t.co/nTgj44H8
36 minutes ago by Oystor_Tweets on twitter
China hits back at Pentagon report on spy claims. http://t.co/CccR4SBM
36 minutes ago by zdnetasia on twitter
China hits back at Pentagon report on spy claims http://t.co/YP380BYQ http://t.co/erFX4aVv #arcavir
36 minutes ago by V_RaV on twitter
http://t.co/VNaZtseV China hits back at Pentagon report on spy claims: Annual report by Pent... http://t.co/TvgCi5RE http://t.co/wiqY9ktt
36 minutes ago by RavtachSolution on twitter
#AntiVirus News: Mac users' indifference toward security 'worrying' http://t.co/spWS0CpU #AdAware
36 minutes ago by AdAwareFree on twitter
Mac users' indifference toward security 'worrying' http://t.co/BtVn1BAk
> expected! They still remember Mac vs PC ads
#infosec #news #apple
Pentagon report says China exploit US tech, conduct cyberespionage, China says it has been "unjustly criticized" http://t.co/P5wgqy6I #in
51 minutes ago by EllyZDNetAsia on twitter
Mac users' indifference toward security 'worrying': 59 Jakarta 10350, Indonesia In light of the recent spate of ... http://t.co/Lxgnc1wM
1 hour ago by GoodCodeBadCode on twitter
Pakistan lifts block on Twitter - ZDNet Asia: Pakistan lifts block on TwitterZDNet Asia59 Jakarta 10350, Indones... http://t.co/61n85ajh
1 hour ago by semarang_photo on twitter
Pakistan lifts block on Twitter http://t.co/WHqoJOqm http://t.co/erFX4aVv #arcavir
1 hour ago by V_RaV on twitter
http://t.co/VNaZtseV Pakistan lifts block on Twitter: Country restores access after briefly ... http://t.co/5gqegFWK http://t.co/wiqY9ktt
1 hour ago by RavtachSolution on twitterI reckon your view: "CRM is strategy, not software", if a company replicating the approach uses in ERP implementation into CRM, what they...
3 hours ago by wykoong on Gartner: Mobile CRM gives better ROI than socialThis video will teach you about the Excel fill handle but also provide you with a workook to download... http://www.youtube.com/watch?v=...
20 hours ago by TradeBrother on A quick fill handle trick for Microsoft Excelwaiting...
2 days ago by eapete on What should count in a company's market value?Boy, you've opened a can of worms now.
Wait for the rants & raves.
I was puzzling before this whether to replicate the success formula we executed for a financial institute, and come out with a standard s...
3 days ago by wykoong on Drop the egos, copy ideas, then innovateKeep up with ZDNet Asia
Facebook 
RSS Feeds 
Newsletters 
Twitter Whitepapers
Downloads
Database News
Inside ZDNet Asia
Sponsored
Echelon 2012 - The Awesomer Tech Event in Asia
Echelon 2012 – SEA’s longest running tech startup event goes Awesomer. Catch 50 of Asia’s most promising startups & over 40 international speakers on June 11-12.
Startup Asia Jakarta showcases new product-ready tech startups. Plus: hackathon, exhibition, and speakers. Use promo code CBSi50 for 50% discount.
ZDNet Asia Intelligent Singapore video series
Featuring inteviews with CXOs who define "intelligence" in their markets and reveal how their companies drive business efficiencies through ICT.
Develop an SQL Server Job from scratch
I initially thought that this is creating jobs from scratch without the use of the SSA.
Do you know if it's possible to create and schedule jobs if SSA is not enabled/intalled?