After installing SQL, some considerations – part 4

SQL Mail / SQL Agent

A well configured SQL Agent can save you a lot of time, and allow you to keep ahead of the trend when it comes to failing jobs and alert conditions on your server, and let’s face it, staying on top of what’s happening in our domain is what we are all about.

Configure your Database mail / Operator and Link to your Agent

To take full advantage of your SQL Server Agent, the 1st thing you need to do is configure your database mail. As before, this is a general blog, so I am not going to go into full details on how to do this, as it has been amply covered elsewhere (this is Microsoft’s official how to), and a quick Google search will provide you with everything you need.

Operators

The SQL Operators you can configure simply act as a holder for an email address. They allow you to set up a group of people or individuals that can be alerted when certain configurable conditions arise. As an example, you could set up an SSIS operator that notifies your BI team when scheduled SSIS packages fail. Or more importantly for the DBAs, you can configure notifications for backups and have them notify a DBA operator when they fail / complete, whatever your preference be. There are several options for notifications (email, pager, net send) but to be honest, I have only ever used email, and Microsoft has been promising to depreciate the other options at some point, so it is just safer to stick with email.

Once your mail is enabled and configured and you have successfully sent yourself a test mail (Always test your mail configuration after initial set up and any configuration changes), and you have a valid operator, then you need to link it to your Agent. I have seen this step missed a number of times and it is really so easy that there is no excuse.

  1. Go to SQL Agent Properties (right click your agent in SSMS) and go to the Alert System tab:
  2. Under the Mail session make sure Enable mail profile is selected and set the Mail Profile to be the one you configured when you set up your Database Mail
  3. Under fail-safe operator make sure Enable fail-safe operator is selected and the operator you configure earlier is selected.
  4. Click OK.
  5. RESTART SQL AGENT – this is a step I have seen missed a number of times, if you do not restart your agent after making these configurations, SQL Agent will not recognize them.

Maintaining Agent History

By default SQL server keeps history of all agent activity and job activity. The default retention for this is:

This is quite a small log if you have jobs that run every minute over a 24 hour period, so it is advisable to increase this, otherwise you may lose valuable information in a very short space of time. This can be accomplished by either going into the agent properties / history tab, or running the SQL below:

USE [msdb]
GO
EXEC [dbo].[sp_set_sqlagent_properties]
@jobhistory_max_rows=20000,
@jobhistory_max_rows_per_job=1000
GO

The figures you choose for these 2 values are totally dependent on the activity you are going to see on the server, so if you are unsure you can revisit this after a week or 2 and tweak.

Cycling the error log

Following on from the history is making sure you manage the growth of the log files, both the SQL agent and the Server engine. If you have a high usage server that requires a fairly continuous up time, you may have long periods between server restarts. As logs are initialized at server start by default, this can mean you may build up very large log files. Once the logs start to get too large, browsing through them can become a struggle as the interface tries to read all of the entries in the log. Because of the settings in the previous section, you will also lose any entries that exceed the value you put in the Agent history.

Fortunately there is a very easy fix for this; SQL provides 2 very easy system procedures that allow you to re-initialize both logs, creating an archive of the old log:

sp_cycle_agent_errorlog
sp_cycle_errorlog

One of the first things I do on any new server is to configure a job that recycles the log on a weekly basis. Below is the script I use:
USE [msdb]
GO

/****** Object:  Job [DBA - cycle SQL Error Log]    Script Date: 06/22/2012 10:28:10 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

/****** Object:  JobCategory [Database Maintenance]    Script Date: 06/22/2012 10:28:11 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA - cycle SQL Error Log',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Re-cycle the SQL error logs on a weekly basis to stop them growing too large.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [sp_cycle_errorlog]    Script Date: 06/22/2012 10:28:11 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'sp_cycle_errorlog',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC sp_cycle_errorlog',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [sp_cycle_agent_errorlog]    Script Date: 06/22/2012 10:28:11 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'sp_cycle_agent_errorlog',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'sp_cycle_agent_errorlog',
@database_name=N'msdb',
@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly cycle',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20110912,
@active_end_date=99991231,
@active_start_time=60000,
@active_end_time=235959,
@schedule_uid=N'0c6a2d81-259a-4141-a329-e8ef389bbd83'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave

QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

This simple job allows you to manage your log files very easily and runs in milliseconds once a week. It depends on an operator called ‘DBA’ so if you named your operator something else, make sure to update the script before running.

Alerts

Alerts for SQL server is a massive topic and one for outside the scope of this discussion, but I just want to stress that these Alerts are life savers! There are some awesome products on the market that allow you to monitor your servers and configure alerts in an easy to manage interface (RedGate, Idera and Quest have probably the best ones I have worked with) but if you find yourself limited by budget I can’t stress how important these Alerts can be to you. Below are some links that should see you on your way:

http://www.sqlservercentral.com/articles/alerts/89885/ (This is one of mine, so it had to be first J)

http://msdn.microsoft.com/en-us/library/ms187827.aspx (basic steps for configuring alerts)

http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=264 (default alerts and configuration)

These should get you started, but there are hundreds of articles out there on specific alerts and why they are required. Like everything SQL related it all comes down to what you need to accomplish on the server you are working on at the time.

Hopefully these small steps will help you and your environment. There are many more options that can be covered when setting up your SQL server, but these few are the ones that have helped me most when building out a new server.

Thanks for reading.

Advertisements
This entry was posted in SQL Server and tagged , , , , , . Bookmark the permalink.

One Response to After installing SQL, some considerations – part 4

  1. Pingback: What is a Fail Safe Operator? « Understanding SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s