script to find specific information from Error log

SET NOCOUNT ON


-- 1 - Declare variables

DECLARE @numerrorlogfile int

-- 2 - Create temporary table

CREATE TABLE #errorLog

([LogDate] datetime,

[ProcessInfo] nvarchar(20),

[Text] nvarchar(max)

)

-- 3 - Initialize parameters

SET @numerrorlogfile = 0

-- 4 - WHILE loop to process error logs

WHILE @numerrorlogfile < 5

BEGIN TRY

INSERT #errorLog ([LogDate], [ProcessInfo], [Text])

EXEC master.dbo.xp_readerrorlog @numerrorlogfile, 1, N'DBCC', NULL, NULL, NULL, N'desc'



SET @numerrorlogfile = @numerrorlogfile + 1;

END TRY

BEGIN CATCH

SET @numerrorlogfile = @numerrorlogfile + 1;

END CATCH

-- 5 - Final result set

SELECT LogDate,[Text] FROM #errorLog

-- 6 - Clean-up temp table

DROP TABLE #errorlog

GO

Database Page Verify options

Use of Page verify option: 

1.     Page verify option identifies the suspected or damaged page from corrupted disk.

2.     It logs the entry of error 824 in SQL error log as well in application event log.

3.     On the basis of this error SQL server writes a record in msdb.suspect_pages table.

There are there options of Page verify

1.     Checksum

2.     Torn_page_detection

3.     None

Microsoft recommended option for page verify is Checksum, So why it is?

Because the CHECKSUM,

Option advises SQL Server to calculate a checksum over the contents of each data / index page when it is written to disk.

This calculated value is stored in the page header.

When the page is read from the disk, checksum is computed again and compared to the value in page header.

Torn page detection:

Before we go to this option we take one look of what is torn page?

            1. Torn page is the page which is damaged because of not properly got written on disk.

            2. Because of sudden shutdown or power off the data did not get saved on disk and while       recovery of database the page get suspect.

            3. This is nothing but the incomplete I/O operation.

So you can add this page verify option to detect torn pages , it configures the 512 byte sector on each data/index pages and if there is any damage to page it identifies as a suspect page when it get read.

None:  so if you don’t want the above helpful advises to find the damaged pages and like to loss then u can choose this option.

Use Alter database command to set this page verify option.

e.g     Alter database xyz set page_verity Checksum / Torn_page_detection