Post reply

Why page verify option is set to NONE for Tempdb by default?

  • October 28, 2009 at 4:44 pm

    #206083

    Hi.

    We have SQL Server 2005 Developer edition x64 with SP3. I have noticed that except for Tempdb all the other databases, the Page verify option is set to CHECKSUM. Only tempdb has Page verify option set to NONE.

    Can we change this page verify option to CHECKSUM for tempdb? will it improve performance\ IO error Detection? Or we should NOT change this page verify option at all and keep it as default valuse "NONE"?

    please advice

    thanks

  • October 28, 2009 at 5:33 pm

    #1072167

    Here is an msdn blog that may be of use.

    Page Verify Option in Tempdb

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • October 29, 2009 at 12:34 am

    #1072251

    thanks,

    I went through your blog and it says we can only set page verify to CHECKSUM for a tempdb starting from SQL Server 2008. But I can see the CHECKSUM option for tempdb in SQL Server 2005 too.

    With CHECKSUM available on tempdb starting with SQL2008, you can finally close this window. You can use the following command

    ALTER DATASE tempdb set PAGE_VERIFY CHECKSUM

    I just want to know that can we use CHECKSUM option for tempdb in SQL Server 2005 or NOT? If not, it should be set to NONE right?

    thanks

  • October 29, 2009 at 7:26 am

    #1072420

    The following restrictions also apply to tempdb:

    The database CHECKSUM option cannot be enabled.

    http://technet.microsoft.com/en-us/library/cc966545.aspx

    MJ

  • March 15, 2015 at 10:55 pm

    #1784142

    Thanks for posting the link.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply