IT Employment optimize

Monitor database file sizes with SQL Server Jobs

Tim Chapman shows you how to develop a SQL Server Job from scratch to monitor the size of the database files on your server.

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

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

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

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

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 chapman.tim@gmail.com.

-----------------------------------------------------------------------------------------

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!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

7 comments
jdbrown239
jdbrown239

Tim This is a good article and I found it helpful. Do you have any reports that you use with the collected data?

TheRealPauper
TheRealPauper

When did they stop teaching the proper use of the English language ? The phrase "I?ll show you how to develop a SQL Server Job ..." Should be: I?ll show you how to develop an SQL Server Job ..." Shouldn't it ?? At least, back when I was in school, there were proper usages for "a", "an", "the" (pronounced thee, as in 'The excellent...') and "the" (pronounced thuh, as in 'The daily press...') When did the school system stop teaching the usages, or have people just stopped using them ??

Kent.Drysch
Kent.Drysch

If you pronounce it Sequel Server then 'a' would be correct. If you prefer S-Q-L Server, then 'an' would be the way to go.

TheRealPauper
TheRealPauper

"Sequel Server" never occurred to me, guess I'm too literal. You're right of course. :-)

mybsacct
mybsacct

Well, we pronounce it "Squeal" so I believe "a" is the proper usage...

Mafig
Mafig

In the Microsoft Press book T-SQL QUERYING by ITZIK BEN-GAN there's a paragraph about the 'Origin of SQL Pronunciation' and it refers to both ways of saying SQL and the use of 'a' or 'an'. Before reading it I didn't know there were two ways of saying it... (I'm not English anyway). Curious. MF