October 9, 2009 at 12:55 am
Hi,
We did an in-place upgrade of sql server 2000 to 2005. Now want change the compatibility mode to 90.
1. I have verified the System databases compatibility mode and it is 90 EXCEPT master database.
2. Before changing compatibility of master database, do we need to perform any tasks? and what are the steps to perform after changing the compatibility of master database??
For user databases:
1. change the compatibility mode to 90.
2.Index rebulid
3.DBCC UPDATEUSAGE
4.SP_UPDATESTATS
Are there any additional steps to perform apart from the above for User databases?
thanks
October 9, 2009 at 1:09 am
just as a side note, have you checked that changing the compatibility mode will not break any of the application code that is running against the database. I take it that the sql jobs work as expected, you have removed any deprecated commands like old style ansi joins for example
I am surprised that the master database is still 80 though
--------------------------------------------------------------------------------------
[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]
October 9, 2009 at 7:44 am
I've never run an in-place upgrade, but I would be very concerned if the master database was still in 8.0 compatibility mode. To me it sounds like the upgrade did not complete successfully. I'd check the SQL Server 2005 install logs. Check this article for where to look for the log files.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 9, 2009 at 11:29 am
I am surprised that the master database is still 80 though
http://blogs.msdn.com/psssql/archive/2007/10/16/database-compatibility-and-new-features.aspx --see this link.
Its by design. For an in-place upgrade of sql server 2000 to 2005, EXCEPT the compatibility of MASTER database, the other system databases compatibility changes to 90.
thanks
October 9, 2009 at 12:23 pm
Jack Corbett (10/9/2009)
I've never run an in-place upgrade, but I would be very concerned if the master database was still in 8.0 compatibility mode. To me it sounds like the upgrade did not complete successfully. I'd check the SQL Server 2005 install logs. Check this article for where to look for the log files.
If you have any custom objects in master - the upgrade will keep the compatibility level at 80 and you will have to manually change it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 9, 2009 at 10:26 pm
For user database read -->http://www.sqlservercentral.com/Forums/Topic800427-146-1.aspx
MJ
October 9, 2009 at 11:51 pm
If you have any custom objects in master - the upgrade will keep the compatibility level at 80 and you will have to manually change it.
But we do not have any custom objects in master...
October 10, 2009 at 12:26 am
Mani-584606 (10/9/2009)
If you have any custom objects in master - the upgrade will keep the compatibility level at 80 and you will have to manually change it.
But we do not have any custom objects in master...
But, the upgrade process doesn't know that - so, it keeps it in 80 compatibility and lets you decide whether or not you can change it.
If you have no custom objects, procedures, functions, etc... in the master database then there should be no problem changing the compatibility level.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply