Presents your 
SQL SERVER E-NEWSLETTER for August 22, 2002

<------------------------------------------->

Reading Job History Values

You can read the registry values to report settings for SQL Server
regarding the job history capacity by using the extended stored 
procedure,
xp_regread. Reporting these values informs DBAs of the maximum rows 
each job
is allowed to have to record history and the maximum rows of job 
history
SQL Agent will contain.

If these values are too small, they may catch you off guard when trying
to research a problem on SQL Server, especially when there's not a
history for the job to help identify the problem. It's wise to increase 
the
values considerably when there are a number of jobs or the jobs are 
being
executed in frequent intervals.

Here's a sample script to read values from the registry for the job
history settings with SQL Server 7.0.

PRINT REPLICATE('*',80)
PRINT ' JOB HISTORY RETENTION CAPACITY'
PRINT REPLICATE('*',80)
DECLARE @JobHistoryMaxRows int, @JobHistoryMaxRowsPerJob int
 EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
          N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
          N'JobHistoryMaxRows',
          @JobHistoryMaxRows OUTPUT,
          N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
          N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
          N'JobHistoryMaxRowsPerJob',
          @JobHistoryMaxRowsPerJob OUTPUT,
          N'no_output'
select @JobHistoryMaxRows JobHistoryMaxRows, @JobHistoryMaxRowsPerJob
JobHistoryMaxRowsPerJob

This code is for SQL Server 2000 with an instance named 
MYNAMEDINSTANCE.
Notice that the registry path is different than SQL Server 7.0.

PRINT REPLICATE('*',80)
PRINT ' JOB HISTORY RETENTION CAPACITY'
PRINT REPLICATE('*',80)
DECLARE @JobHistoryMaxRows int, @JobHistoryMaxRowsPerJob int
 EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
         N'SOFTWARE\Microsoft\Microsoft SQL
Server\MYNAMEDINSTANCE\SQLServerAgent',
         N'JobHistoryMaxRows',
         @JobHistoryMaxRows OUTPUT,
         N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
         N'SOFTWARE\Microsoft\Microsoft SQL
Server\MYNAMEDINSTANCE\SQLServerAgent',
         N'JobHistoryMaxRowsPerJob',
         @JobHistoryMaxRowsPerJob OUTPUT,
         N'no_output'
select @JobHistoryMaxRows JobHistoryMaxRows, @JobHistoryMaxRowsPerJob
JobHistoryMaxRowsPerJob

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