April 16, 2008 at 1:30 pm
Hi all,
I have restored my SQL 2000 database onto SQL 2005 with the compatibility level remaining at 80. Now, I would like to switch the compatibility level from 80 to 90 to utilize the advanced functionality of 2005,i found that the best practice to do that is
1. Set the database to single user access mode by using ALTER DATABASE SET SINGLE_USER.
2. Change the compatibility level of the database.
3. Put the database in multiuser access mode by using ALTER DATABASE SET MULTI_USER
But i donot want users to be disconnected
I understand from BOL that
"Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries. For example, if the compatibility level changes while a query plan is being compiled, the compiled plan might be based on both the old and new compatibility levels, resulting in an incorrect plan and potentially inaccurate results. Furthermore, the problem may be compounded if the plan is placed in the plan cache and reused for subsequent queries. To avoid inaccurate query results "
My question is can i free the procedure cache and then change the compatibility mode , without disconnecting the users? this way existing query plans will be dropped and fresh set of query plans will be created .Thus avoiding the above error.
Is this the only problem or are there any pitfalls associated with changing the compatibility mode without disconnecting the users.
April 16, 2008 at 1:50 pm
Freeing the procedure cache will not help the issue being noted.
The problem is if there is a plan being built while the setting is changed the plan could possibly be built partially with the 80 setting and partially with the 90 setting. Clearing the cache would probably make it more likely that a plan would be in the process of being built because there would be no plans for a query to use.
This is a bit of a sensitive configuration to be changing while users are in the database. It would be prudent to find a time when you can make sure everyone is out.
April 16, 2008 at 2:48 pm
I'd second Michael's notes. Wait for a quiet time to do this.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply