Update SQL Server Agent Job Owner

Hi there,

I have been working on a project to move/migrate lots of jobs to a new server in another domain and we had to update quite a bit of jobs to set the owner to 'sa' prior to migration.  Out of close to 600 jobs, there were about 200 jobs owned by not 'sa' but an account in Active Directory.  So instead of touch all of those jobs manually and change the owner I come up with the following script and all done in less than 10 minutes including time took come up with the script.  I decided to share it here and also use my blog to document it if I ever need it.

HTH,
Bulent

USE MSDB;
GO
SET NOCOUNT ON;
/********************************

This script is used to update the job owner to sa for
the jobs that is not already owned by sa.

********************************/
DECLARE
      @MinRowId INT = 1
    , @MaxRowId INT
    , @jobId UNIQUEIDENTIFIER
    , @owner_login_name NVARCHAR(128) = N'sa';

-- Store the job steps in a temp table to update the in the loop later
IF OBJECT_ID('tempdb..#Jobs') IS NOT NULL
    DROP TABLE #Jobs;
CREATE TABLE #Jobs (
      RowId INT IDENTITY (1,1) NOT NULL
    , job_id UNIQUEIDENTIFIER NOT NULL
    , jobname SYSNAME NOT NULL
    , owner_sid VARBINARY(85)
    );
-- Insert list of jobs not owned by 'sa'
-- Can change the select statement and
-- where clause to your need
INSERT INTO #Jobs (job_id, jobname, owner_sid )
SELECT    j.job_id, j.name, j.owner_sid
FROM    dbo.sysjobs AS j
WHERE    J.owner_sid <> 0x01 -- sid for SA
ORDER BY j.name

SET    @MaxRowId = @@ROWCOUNT;

WHILE @MinRowId <= @MaxRowId
    BEGIN
        SELECT    @jobId = job_id
        FROM    #Jobs
        WHERE    RowId = @MinRowId;
       
        EXEC sp_update_job @job_id = @jobId, @owner_login_name = @owner_login_name;

        SET @MinRowId = @MinRowId + 1;
    END
--Return list of jobs and original owner sid
SELECT * FROM #Jobs;
DROP TABLE #Jobs;

Cycling SQL Server Error Log

I have a daily check list that I go through everyday. And checking the SQL Server error log is one item on the list. Depends on how your server is configured SQL Server logs informational and error messages to the log file. By default your server is configured to have 6 log files that it keeps and when the SQL Server Service starts it removes the oldest log file. In the meantime if your server is up for a long time the information in that log file just keeps growing and makes the log file hard to open from remote workstation.

In the environments that I support I change the configuration so that I keep more than 6 files. I set up to store 99 files (which is the max allowed files for the SQL Server error log). Then I create a job that runs every night right after midnight. This way when I come in every morning there is a brand new log file and since the daily log files are much smaller it makes the file easy to open and check. I use the script below to change the number of log files (note you can do this using SSMS GUI and just make a right click on SQL Server Logs under Management folder than click on Configure and change the value to what you need). After the change using second script I create SQL Server Agent Job that is scheduled to run right after midnight to cycle the error log. This make the daily task of checking the error logs easier (at least for me). Use the scripts below in a test environment and once you find out it could add some value to your daily tasks you could use it in your production environment.

HTH,
Bulent

Script 1:
This changes the number of log files stored to a value you set at the end and uses extended stored procedure to update the registry. Remember this can be done through GUI but it does executed the same in the background.

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 60

GO



Script 2:
This script creates a job (make sure you sql server agent is running) and schedules it to run at 12:00:01 am everyday.

USE [msdb]

GO

/****** Object: Job [A DBA SQL Server Error Log Cycle] Script Date: 01/27/2012 14:01:38 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [Database Maintenance] Script Date: 01/27/2012 14:01:38 ******/

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'A DBA SQL Server Error Log Cycle',

@enabled=1,

@notify_level_eventlog=2,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'Cycle sql serve error log and create a new error log file',

@category_name=N'Database Maintenance',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Cycle Error Log] Script Date: 01/27/2012 14:01:38 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cycle Error Log',

@step_id=1,

@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'EXEC sp_cycle_errorlog ;

',

@database_name=N'master',

@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'Cycle Error Log',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20111013,

@active_end_date=99991231,

@active_start_time=1,

@active_end_time=235959

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

Suspect_Pages

I think not many database administrators are aware of the msdb.dbo.suspect_pages table in SQL Server 2005/2008. The database engine uses this table to record possible suspect page data. It records the pageid and event_type_value which indicates what type of error or how the page repaired along with some other data. For detecting suspect page in your server I recommend setting up a job which looks at that table with certain event_type_value. (the values can be 1,2,3,4,5,7) If database engine records a row with value less than equal to 3 then it means you're facing possible CRC failure, checksum error, or torn page. Then you can send an email alert to your self to start looking for solution. You can use DBCC commands to repair the page or if it does nt work you may have to restore the page from a backup. Suspect_pages table is like having some early warning system so I think dba's should use this table to alert themselves. You can read more about it here on MSDN Site.

HTH