Wednesday, June 15, 2011

Upgraded to SQL 2008 tempdb from earlier version like SQL2005/2000

When you upgraded to SQL 2008 from SQL 2005/2000 make sure to change tempdb database Page_verify_option set to CHECKSUM.


By default in SQL server 2008 Page_verify_option is set CHECKSUM but in SQL 2005 the default is NONE.

You can  run the below query to check:

select name,Page_verify_option from sys.databases where name='tempdb'

0 - NONE
1 - TORN_PAGE_DETECTION
2 - CHECKSUM
 
source: http://msdn.microsoft.com/en-us/library/ms175527.aspx