SQL Server Agent Articles

Monitor SQL Server Replication Jobs

The Replication infrastructure in SQL Server is implemented using SQL Server Agent to execute the various components involved in the form of a job (e.g. LogReader agent job, Distribution agent job, Merge agent job) SQL Server jobs execute a binary executable file which is basically C++ code. You can download all the scripts for this […]

Monitoring Table Size Growth in SQL Server

General In this article I introduce a simple process that saves tables size information at points in time which latter allows for tracking down table growth over time. The process is useful in monitoring data growth over time and lets you see what tables are growing rapidly in what databases and can also help in […]

Verify Whether a SQL Server Agent Job is Running

When I posted an faq about Finding Running SQL Server Agent Jobs I had several few emails and queries on how verify whether a job is running by using only a TSQL script. The below script will verify if the job exists and whether or not it is running. DECLARE @jobname sysname =’Running Job’ — […]

Notifications for SQL Server Agent Restarts

Introduction In this article we will set up an email notification at the SQL Server level whenever SQL Server and the SQL Server Agent Restarts. The article will   useful for DBA’s who support a large number of database servers which they cannot manually connect to   daily to check the SQL Server status, SQL job status […]

Controlling Growth of a Distribution Database

I was recently asked to set up Transactional Replication comprising of 29 publishers as well as same number of subscribers. One interesting challenge was that the size of the distribution database suddenly increased from few MB’s to around 62 GB and as a result the disk on which the production database was hosted quickly filled […]

SQL Server Agent Alter Column Error

Replication Agent will fail when attempting to alter a column in the publisher which is part of an index. For example, let’s say you have table like following: CREATE TABLE [dbo].[Employees]( [ID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [varchar](50) NULL, CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ( [ID] ASC ) ) ON [PRIMARY] And […]

SQL Server Agent job syspolicy_purge_history is failing in the cluster environment.

When you install or upgrade SQL Server 2008 or R2 you will  see a new SQL Server Agent job named syspolicy_purge_history. In fact this is the only job you will see after installing a brand new SQL Server 2008 instance. This job is relevant to Policy Based Management and by default it has a daily […]