In all the blogs and forums that I have visited, I have always seen SQL Server experts and gurus asking novice developers and administrators to avoid updating the SQL Server system databases. In fact, the official documentation from Microsoft in the MSDN/Books On Line (BOL) documentation (http://msdn.microsoft.com/en-us/library/ms179932) opens with the following lines:

"The system tables should not be changed directly by any user."

However, their advice often falls to deaf ears, mostly because they have not really experienced and understood the side-effects of implementing such "short-cuts". An example always helps to go a long way in understanding a concept, and that's what I intend to present today.

Some time ago, I was pulled out of my current assignment to help one of the teams overcome multiple deployment issues with their database. Today, using this real-life scenario, I would attempt to demonstrate why updating system tables directly is not recommended and does not work.

The Symptom

The installer of this particular product used to deploy a couple of nightly jobs onto the destination SQL Server instance. These jobs were deployed as disabled by default. After all default data population and other configurations are complete, the installer should to enable the nightly jobs. However, the nightly jobs did not work immediately after deployment. The SQL Server Agent needed to be re-started for the agent to "recognize" the change in enabled status.

Demonstration of the problem

I will now present a set of scripts that would create a simple nightly job. I will be creating the job through T-SQL code, which would involve the following steps:

1. Create a (disabled) SQL Server Agent job and an associated job step
2. Create an enabled schedule
3. Associate the job with the schedule
4. Enable the job by directly updating the system tables

Allow me to walk-through the code snippets step-by-step.

USE tempdb
GO
--Step 00. Create a test table
BEGIN
   --Safety Check
   IF OBJECT_ID('NAVJobTracker') IS NOT NULL
   BEGIN
      DROP TABLE NAVJobTracker
   END
   
   --Create the test table
   CREATE TABLE NAVJobTracker (Name VARCHAR(50),
                               DateExecuted DATETIME
                              )
END
GO

The code above creates a simple table that my nightly job would be populating when executed. That would help me to check when the nightly job ran and whether it ran as per it's defined schedule or not.

Next step now is to create a simple, disabled nightly job, with one job step. Note that I am using system stored procedures to create the nightly job.

--Step 01. Create a disabled job, with one job step
USE msdb
GO
BEGIN
   DECLARE @disabledJobId UNIQUEIDENTIFIER
   
   --Create a DISABLED SQL Server Agent Job
   EXEC dbo.sp_add_job @job_name = 'Disabled Job', 
                       @enabled = 0, 
                       @description = N'A job that is disabled first, and will be enabled later on.',
                       @job_id = @disabledJobId OUTPUT
   
   --Create a Job step within the DISABLED SQL Server Agent Job
   EXEC dbo.sp_add_jobstep @job_name = 'Disabled Job',
                           @step_name = 'Test T-SQL statement from disabled job',
                           @subsystem = N'TSQL',
                           @command = 'INSERT INTO tempdb.dbo.NAVJobTracker (Name, DateExecuted) VALUES (''Disabled Job'', GETDATE())'
END
GO

Now, I need to create a job schedule as shown by the code below:

--Step 02. Create an enabled schedule and attach it to the jobs
USE msdb
GO
BEGIN
   EXEC dbo.sp_add_schedule
            @schedule_name = N'Nakul-Disabled Test Schedule' ,
            @enabled = 1,
            @freq_type = 4,
            @freq_interval = 4,
            @freq_subday_type = 2,
            @freq_subday_interval = 10;
   EXEC dbo.sp_attach_schedule
            @job_name = N'Disabled Job',
            @schedule_name = N'Nakul-Disabled Test Schedule' ;
END
GO

The schedule created ("Nakul-Disabled Test Schedule") is enabled and would execute the nightly job at an interval of every 10 seconds daily. Please refer the documentation for sp_attach_schedule for more information.

Next, I will target the nightly job to a specific SQL Server instance. In this case, the instance name of my SQL Server instance is "W2K8\SQL2K8" (it's a named instance). Please replace it with the instance name of your SQL Server instance when executing on your development environment.

--Step 03. Target the test jobs to specified server
USE msdb
GO
BEGIN
   EXEC dbo.sp_add_jobserver @job_name = 'Disabled Job',
                             @server_name = 'W2K8\SQL2K8' -- UPDATE THIS!
END
GO

In order for a job to execute, both the job and the schedule should be enabled. Because our test job is disabled, it will not automatically start executing when associated to an enabled schedule.

Now comes the crucial moment - enabling the nightly job. I will intentionally update the system table - msdb.dbo.sysschedules directly, which is not a recommended practice.

-- Step 04. Enable the job - Bad Practice
USE msdb
GO
BEGIN
   UPDATE sj
   SET sj.enabled = 1
   FROM msdb.dbo.sysjobs AS sj
   WHERE sj.name = 'Disabled Job'
   
   ----Debug point!
   --SELECT ss.name AS ScheduleName, ss.enabled AS IsScheduledEnabled, sj.name AS JobName, sj.enabled AS JobEnabled
   --FROM msdb.dbo.sysjobs sj
   --INNER JOIN msdb.dbo.sysjobschedules sjs ON sj.job_id = sjs.job_id
   --INNER JOIN msdb.dbo.sysschedules ss ON sjs.schedule_id = ss.schedule_id
   --WHERE sj.name = 'Disabled Job'
END
GO

Now that the nightly job has been enabled, in theory, I should start seeing entries being written every 10 seconds into our test logging table (tempdb.dbo.NAVJobTracker). So, let me execute the statement shown below to take a look:

--Step 05. Check the Log table to see if the job is running
USE tempdb
GO
BEGIN
   WAITFOR DELAY '00:00:40' 
   SELECT '05' AS StepNumber,Name,DateExecuted FROM tempdb.dbo.NAVJobTracker ORDER BY DateExecuted DESC
END
GO

I see that no results were returned by the script, although we waited for a period as big as 40 seconds.

The query execution shows that the nightly job did not execute as expected.

Let me now restart the SQL Server Agent service to determine whether the status change is picked up at the service restart. I will confirm the restart by using the extended stored procedure - xp_readerrorlog:

--Step 06. Check when the SQL Server Agent was restarted
EXEC xp_readerrorlog 0, 2, 'start'

The output indicates that the restart was done on August 18, 2012; 16:03hrs.

Now, let me query the test table (tempdb.dbo.NAVJobTracker) again, using the query shown in Step 05 above. The output is shown below, which indicates that the job did start executing after the Agent Service was restarted.

As demonstrated above, the key symptom that the team encountered was that SQL Server agent had to be restarted every time the nightly jobs were deployed (or re-deployed) by the product's installer. Because the SQL Server instance had other databases configured for replication and other disaster recovery options, restarting the Agent service was not an acceptable option as a long-term recommendation.

(Scripts to cleanup the environment by destroying the jobs and schedules created in this exercise are available in the attached set of files).

The Solution

The root cause of the issue lies in the fact that the code used in the installer is updating the system tables directly. The information regarding jobs and their schedules is cached. Hence, when underlying system table data is updated, the cache should be updated and the agent service would start consuming the updated cache. The update of the cached/meta data is what goes missing when users directly update the system tables.

The solution is therefore to use system stored procedures to enable the nightly job. The system stored procedure - msdb.dbo.sp_update_job performs the following tasks:

• Updates the system tables
• Updates the meta-data/cache concerned around the nightly jobs and their schedules
• "Informs" SQL Server agent about the change to the underlying meta-data so that it can use the updated configuration

When updating system tables directly, a SQL Server Agent restart is required to update the underlying meta-data.

The demonstration

Because the steps to create the temporary logging table, the nightly job and the schedule are the same as before, I will not be repeating them for sake of brevity.

I have executed steps #00-03 shown above to create the logging table, nightly job, job steps and schedules. What changes is step #04, which is now updated to use the the system stored procedures.

-- Step 04. Enable the job using system stored procedure - Best Practice
USE msdb
GO
BEGIN
   EXEC dbo.sp_update_job @job_name = 'Disabled Job',
                          @enabled = 1
END
GO

Once the nightly job is enabled, let us query the job tracking table to see if the enabled status change took immediate effect or not.

As proof that the SQL Agent was not restarted, let us query the SQL Server Agent error log again:

Conclusion

I would say that this exercise reiterates the age-old recommendation:

Users should not update system tables directly.

It may be appealing to most developers to directly update the system tables because of the adrenalin rush associated with working directly at the root level, but it is not a recommended approach. Restarting the SQL Server Agent, or even the SQL Server service may not be a big deal in development environments, but if all recommendations are followed, there are very few situations where a restart is mandated.

References

The following would be helpful to you in working through implementation, configuration and management of nightly jobs through T-SQL:

• How to: Create a SQL Agent Job (T-SQL)?: http://msdn.microsoft.com/en-us/library/ms181153(v=sql.105).aspx
• System tables in SQL Server: http://msdn.microsoft.com/en-us/library/ms179932

Attachments

• SSC-Updating System Tables-Bad Practice.sql
• SSC-Using System SPs-Best Practice.sql

Disclaimer

• The scripts in this article are provided "as-is", i.e. without warranties of any kind and are intended for demonstration purposes only
• Request you to use these scripts for understanding and study purposes in your development environments only - they are not meant for use in production. The author is not responsible for any damage caused by misuse of these scripts
• Scripts are tested on SQL 2008 R2
• The opinions expressed herein are his own personal opinions and do not represent his employer’s view in anyway

About the author

Nakul Vachhrajani is a Technical Specialist and systems development professional with iGATE having a total IT experience of more than 7 years. Nakul is an active blogger with BeyondRelational.com (180+ blogs), and can also be found on forums at SQLServerCentral and BeyondRelational.com. Nakul has also been a guest columnist for SQLAuthority.com and SQLServerCentral.com. Nakul presented a webcast on the “Underappreciated Features of Microsoft SQL Server” at the Microsoft Virtual Tech Days Exclusive Webcast series (May 02-06, 2011) on May 06, 2011. He is also the author of a research paper on Database upgrade methodologies, which was published in a Computer Society of India (CSI) journal, published nationwide. In addition to his passion about SQL Server, Nakul also contributes to the academia out of personal interest. He visits various colleges and universities as an external faculty to judge project activities being carried out by the students.

Blog | LinkedIn | Twitter | Google+