Detecting Corruption

Corrupt data is often unrecoverable after a disaster, so you must ensure that your data remains uncorrupted and recoverable. (Paul S. Randal, “SQL Server: Top Tips for Effective Database Maintenance,” Microsoft TechNet, https://technet.microsoft.com/en-us/library/2008.08.database.aspx [accessed August 28, 2018].)

Unfortunately, when rarely used data becomes corrupt, the problem often goes unnoticed. Therefore, you should check the integrity of your database frequently. (Brien Posey, “How to detect data corruption in files and folders,” TechTarget, https://searchwindowsserver.techtarget.com/tip/How-to-detect-data-corruption-in-files-and-folders [accessed August 28, 2018].)

To create a job that checks the integrity of your database daily and runs automatically outside business hours and within your maintenance window, run the following script.

By default, this script configures the job to run daily at 2:08 p.m. server time. To change the start time, change the number of seconds specified in the script’s @active_start_time=50000 entry. (The default 50000 seconds is equivalent to 2:08 p.m.)

If the job finds corrupted tables, it fails, and you must resolve the errors.

USE [msdb]

GO

/****** Object:  Job [IntegrityCheck]    Script Date: 2/8/2017 10:15:32 AM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 2/8/2017 10:15:32 AM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

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

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'IntegrityCheck',

		@enabled=1,

		@notify_level_eventlog=0,

		@notify_level_email=0,

		@notify_level_netsend=0,

		@notify_level_page=0,

		@delete_level=0,

		@description=N'No description available.',

		@category_name=N'[Uncategorized (Local)]',

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

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

/****** Object:  Step [Integrity Check]    Script Date: 2/8/2017 10:15:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Integrity Check',

		@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_MSforeachDB ''DBCC CHECKDB (?) WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY''',

		@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'Daily Check',

		@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=20170208,

		@active_end_date=99991231,

		@active_start_time=50000,

		@active_end_time=235959,

		@schedule_uid=N'fb18d81d-e80a-4347-a83f-c8eccb42f7b5'

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