Post upgrade steps for system databases

Question

Post reply

Post upgrade steps for system databases

klnsuddu

SSCertifiable

Points: 5836
More actions
March 20, 2009 at 5:15 pm

#219281

Hi,
After upgrading sql server 2000 to 2005, we should do the following for user databases. Does this applies system databases too OR not? please clarify me
DBCC UPDATEUSAGE
DBCC CHECKDB(db_name) WITH DATA_PURITY
DBCC CHECKDB(db_name) WITH NO_INFOMSGS
SP_UPDATESTATS

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

I don't see any harm in running on system databases.

MJ

rajganesh.dba SSCrazy Points: 2538 More actions · Answer 2

rajganesh.dba

SSCrazy

Points: 2538

March 21, 2009 at 1:47 am

#963926

Not needed

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

keep in min to change the dblevel to 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

maechismo_8514 SSCoach Points: 17510 More actions · Answer 4

klnsuddu (3/20/2009)
Hi,
After upgrading sql server 2000 to 2005, we should do the following for user databases. Does this applies system databases too OR not? please clarify me
DBCC UPDATEUSAGE
DBCC CHECKDB(db_name) WITH DATA_PURITY
DBCC CHECKDB(db_name) WITH NO_INFOMSGS
SP_UPDATESTATS

It is needed. It is always a best practice, to run the integrity checks once upgraded from sql 2005 as sql 2000 dint include DBCC CHECKALLOC for errors and sql 2005 includes.

I do it whenever i upgrade a database to 2005.

Lowell SSC Guru Points: 323518 More actions · Answer 5

I've personally seen issues where SP_UPDATESTATS or UPDATE STATISTICS [specific table name] didn't fix performance issues for stored procs after an upgrade, and I had to actually call DROP STATISTICS and then CREATE STATISTICS instead.

There's a few threads on here about people suffering the same issue...I don't know what the real issue is, just how to resolve it (and avoid it by dropping and creating stats proactively)

that's a suggestion from me

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

EdVassie SSC Guru Points: 60472 More actions · Answer 6

If you have done an in-place upgrade it would be wise to do all of the OP statements. If you have done a new install then the DBCC CHECKDB(db_name) WITH DATA_PURITY is not necessary but also not harmful.

Also, Lowell makes a good point that you should be aware of if you have performance issues with specific SPs after the upgrade.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara