Steps to perform after changing the compatibility mode from 80 to 90?

Question

Steps to perform after changing the compatibility mode from 80 to 90?

rambilla4

SSCrazy Eights

Points: 8715
More actions
October 8, 2009 at 5:00 pm

#136271

Hi,
We have upgraded SQL Server 2000 to SQL Server 2005 last month and everything is working fine. Now we would like to change the compatibility mode to 90.
Could you advice the steps to perform after changing the compatibility mode to 90?
Thank you

Viewing 12 posts - 1 through 11 (of 11 total)

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

George M Parker SSCertifiable Points: 5412 More actions · Answer 1

You should definitely run DBCC UPDATEUSAGE after upgrading from SQL 2000 to SQL 2005 and I'd also run sp_updatestats in each database, then include the databases in whatever your standard database maintenance process includes.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 2

dbcc updateusage with count_rows

sp_updatestats

Rebuild all indexes (individually)

DBCC for DATA_PURITY

Change page verification to CheckSum (from Torn page)

Create a new backup

Perform test by the application team(s)

MJ

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 3

Change page verification to CheckSum (from Torn page)

why this step required?

Rebuild all indexes (individually)

can I use Maintenance plan Rebuild task to rebuild the indexes?

Perform test by the application team(s)

If the application did not work as expected, can we go backup to compatibility mode 80? Is this allowed?

please advice..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

rambilla4 (10/8/2009)
Change page verification to CheckSum (from Torn page)
why this step required?

A database upgraded from SQL 2000 will have page-verification set to Torn Page. SQL 2005 introduced the Checksum page verification, which picks up far more problems than torn page could. An upgraded database won't switch automatically, someone needs to do it.

Perform test by the application team(s)
If the application did not work as expected, can we go backup to compatibility mode 80? Is this allowed?

Yes. Honestly though, the testing should have been done before the production system was upgraded. There are some behaviour changes that are not controlled by the compat level.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 5

Can we rebuild indexes, and perform dbcc updateusage and sp_updatestats for MASTER database after changing the compatibility mode to 90?

I went through this link http://msdn.microsoft.com/en-us/library/ms175527.aspx which says we need to change the Page verify option to CHECKSUM for tempdb for SQL Server 2008.

But, it does not say anything about tempdb in SQL Server 2005. Can we change the page verify to CHECKSUM for tempdb for SQL Server 2005?? I went through the link http://msdn.microsoft.com/en-us/library/ms175527%28SQL.90%29.aspx but it did not say anything about it.

thanks

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 6

rambilla4

SSCrazy Eights

Points: 8715

October 14, 2009 at 9:44 am

#1065789

plz advice

thanks

Silverfox SSC-Insane Points: 23150 More actions · Answer 7

system databases shouldnt need changing

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 8

system databases shouldnt need changing

Is that means, we should NOT change the compatibility mode to 90 for MASTER database after doing in-place upgrade from sql server 2000 to 2005??

thanks

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 9

Do we need to change the compatibility mode to 90 for MASTER database after doing in-place upgrade from sql server 2000 to 2005 or NOT?

thanks

Silverfox SSC-Insane Points: 23150 More actions · Answer 10

rambilla4 (10/14/2009)
Do we need to change the compatibility mode to 90 for MASTER database after doing in-place upgrade from sql server 2000 to 2005 or NOT?
thanks

Dont double post, and the re-wording of your duplicate post, made it sound like a demand. We are here to help using our own free time, not to be at your beck and call.

Your system databases should all be at 90, unless you have specific reasons why they should be at 80, such as application dependencies. with it set at 80, you will not be able to use certain functionality such as the performance dashboard reports.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 11

rambilla4

SSCrazy Eights

Points: 8715

October 15, 2009 at 10:17 am

#1066380

thanks for your help..