The Enterprise Cloud
Monitor database file sizes with SQL Server Jobs
By Tim Chapman | February 25, 2008, 1:58 PM PST
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.
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.
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.
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.
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=Nemail@example.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.
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.
It really is this easy
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% of the Jobs you’ll ever define will be simple ones that involve only one Job step and one schedule.
Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at firstname.lastname@example.org.
Get database tips in your inbox
TechRepublic’s free Database Management newsletter, delivered each Tuesday, contains hands-on SQL Server and Oracle tips and resources. Automatically subscribe today!