October 30, 2007 at 1:46 pm
We've experienced two applications timing out after running an "in-place" upgrade from 2000 to 2005.
Are there some post-upgrade steps I should have done ??
October 30, 2007 at 1:58 pm
Wow, I need to read the question and answer the question. Have you re-indexed? What do the SQL Server logs have to say?
October 30, 2007 at 2:02 pm
I have not re-indexed anything. I encountered some problems over the weekend during the upgrade & ran out of time. Is it recommended to rebuild all indexes ?
October 30, 2007 at 2:04 pm
Reindexing, Update statistics (WITH FULLSCAN) and DBCC UPDATEUSAGE come to mind.
At some later point you may (or may not ) want to perform a DATA_PURITY check too 😉
* Noel
October 30, 2007 at 2:04 pm
post upgrade steps:
-rebuild all indexes !
-sp_updatestats;
-dbcc updateusage (0) with count_rows;
-dbcc freeproccache
And offcourse then make full backups !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 30, 2007 at 2:12 pm
- also keep in mind with an in-place upgrade, all your databases (including the system databases) are still at db_level 80 (sql2000) !
- If you want to use the new features, you'll have to alter them to level 90 !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 30, 2007 at 2:25 pm
ALZDBA (10/30/2007)
- also keep in mind with an in-place upgrade, all your databases (including the system databases) are still at db_level 80 (sql2000) !- If you want to use the new features, you'll have to alter them to level 90 !
Good point .... So I can't reindex with users on the system because it will be the "2000 reindex" that locks tables ??
October 30, 2007 at 2:31 pm
If you can get 15 minutes you could: (Probably more like 10)
Get everyone off of the SQL Server.
Set the SQL Server into single user mode
sp_dbcmptlevel , 90
Set the SQL Server into multi-user mode
Let everyone in
Re-index (Online)
If the apps aren't working, another 15 minutes won't hurt.
Good Luck!
jim
October 30, 2007 at 3:12 pm
Thanks for all the replies !
BOL has this about ALTER INDEX (replaces dbcc dbreindex), so maybe I can reindex while users are accessing the tables, even though I'm in 80 compatibility mode ?? I'll try it in test env first.
[font="Courier New"]ALTER INDEX
(snip)
ONLINE = { ON | OFF }
Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.
Note:
Online index operations are available only in SQL Server 2005 Enterprise Edition.
ON
Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This allows queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is very briefly held on the source object. At the end of the operation, an S lock is very briefly held on the source if a nonclustered index is being created, or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online, or when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.[/font]
October 31, 2007 at 5:11 am
I don't know if it is the case, but some things to keep in mind with an in-place upgrade :
http://sqlblog.com/blogs/allen_white/archive/2007/10/08/pay-attention-to-cluster-setup.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply