April 11, 2012 at 10:03 am
Hi Everyone,
Hope all is well.
I just noticed this morning that on our production server when I query select * from sys.databases the Page_Verify_Option_desc column shows TORN_PAGE_DETECTION for 3 databases. I checked the error logs and found nothing so I immediately ran DBCC CHECKDB WITH NO_INFOMSGS for those databases and got the messages below which made me feel better.
DBCC CHECKDB (<DBNAME1>) WITH no_infomsgs executed by domain\svc_sqlserver found 0 errors and repaired 0 errors. Elapsed time: 0 hours 16 minutes 3 seconds. Internal database snapshot has split point LSN = 0002a1ec:0000010e:0001 and first LSN = 0002a1ec:0000010d:0001. This is an informational message only. No user action is required.
DBCC CHECKDB (<DBName2>) WITH no_infomsgs executed by domain\svc_sqlserver found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 49 seconds. Internal database snapshot has split point LSN = 000027e2:000024d4:0001 and first LSN = 000027e2:000024d3:0001. This is an informational message only. No user action is required.
DBCC CHECKDB (<DBName3>) WITH no_infomsgs executed by domain\svc_sqlserver found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 40 seconds. Internal database snapshot has split point LSN = 000009e5:00001cad:0001 and first LSN = 000009e5:00001cac:0001. This is an informational message only. No user action is required.
I also backed up the databases and restored the databases and the databases were restored succesfully. But makes things look weird is even if i query now on sys.databases the column Page_Verify_Option_desc column still shows TORN_PAGE_DETECTION for the 3 databases. And the query select * from master..sysdatabases returns a status of 16 for the same.
Have anyone come across such a situation? I need your values inputs to get this fixed.
Thanks a bunch in advance.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
April 11, 2012 at 10:07 am
Sapen (4/11/2012)
I just noticed this morning that on our production server when I query select * from sys.databases the Page_Verify_Option_desc column shows TORN_PAGE_DETECTION for 3 databases.
From Books Online:
PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
Discovers damaged database pages caused by disk I/O path errors. Disk I/O path errors can be the cause of database corruption problems and are generally caused by power failures or disk hardware failures that occur at the time the page is being written to disk.
CHECKSUM
Calculates a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header. If the values do not match, error message 824 (indicating a checksum failure) is reported to both the SQL Server error log and the Windows event log. A checksum failure indicates an I/O path problem. To determine the root cause requires investigation of the hardware, firmware drivers, BIOS, filter drivers (such as virus software), and other I/O path components.
TORN_PAGE_DETECTION
Saves a specific 2-bit pattern for each 512-byte sector in the 8-kilobyte (KB) database page and stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information. Unmatched values indicate that only part of the page was written to disk. In this situation, error message 824 (indicating a torn page error) is reported to both the SQL Server error log and the Windows event log. Torn pages are typically detected by database recovery if it is truly an incomplete write of a page. However, other I/O path failures can cause a torn page at any time.
NONE
Database page writes will not generate a CHECKSUM or TORN_PAGE_DETECTION value. SQL Server will not verify a checksum or torn page during a read even if a CHECKSUM or TORN_PAGE_DETECTION value is present in the page header.
Consider the following important points when you use the PAGE_VERIFY option:
In SQL Server 2005 and SQL Server 2008, the default is CHECKSUM. In SQL Server 2000, TORN_PAGE_DETECTION is the default.
When a user or system database is upgraded to SQL Server 2005 or SQL Server 2008, the PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) is retained. We recommend that you use CHECKSUM.
Note:
In earlier versions of SQL Server, the PAGE_VERIFY database option is set to NONE for the tempdb database and cannot be modified. In SQL Server 2008, the default value for the tempdb database is CHECKSUM for new installations of SQL Server. When upgrading an installation SQL Server, the default value remains NONE. The option can be modified. We recommend that you use CHECKSUM for the tempdb database.
TORN_PAGE_DETECTION may use fewer resources but provides a minimal subset of the CHECKSUM protection.
PAGE_VERIFY can be set without taking the database offline, locking the database, or otherwise impeding concurrency on that database.
CHECKSUM is mutually exclusive to TORN_PAGE_DETECTION. Both options cannot be enabled at the same time.
Have anyone come across such a situation? I need your values inputs to get this fixed.
To get what fixed? Your checkDB is clean, there's no error.
That said, torn page is the old page protection. You should change that to checksum when you can for all databases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2012 at 10:11 am
No need to worry, everything is fine, just the page verify option setting.
If you right Click on your Database go to properties, go to Options, scroll down to Recovery (option page verify) you'll see that Torn_Page_Detection is the default choice.
This was more than likely a database that was created in SQL 2000 and upgraded to 2008?
In SQL 2000 the only options were Torn_Page_Detection and None. In 2005 Checksum was added to the list and is what you should normally go with.
you can select Checksum from the drop down window or simply execute:
ALTER DATABASE [test] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT
GO
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
April 11, 2012 at 10:13 am
Thanks Gail and SQLBalls for the info.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply