July 7, 2012 at 7:41 pm
We backed up a sql 2005 user database and restored it to a sql 2008 server (after running Upgrade Advisor) The restore went fine but what "post" tweaks do we need to do to the restored database? For example are any of transactions in the upgraded translog log relevant to the upgraded sql 2008 database? Should we truncate the trans log? Do we need to update statistics or rebuild indexes on the restored database?
TIA,
Barkingdog
July 8, 2012 at 3:42 am
Update all statistics
Run a checkDB
Update compat mode to 100
Take a backup
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
July 8, 2012 at 5:07 am
And don't sweat the transaction log. Any uncompleted transactions have already been dealt with by the restore process. Just make sure you set up the next set of log backups if your database is in FULL or BULK LOGGED recovery models.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 8, 2012 at 7:03 am
Is there any value to running "DBCC updateusage" in addition?
Barkingdog
July 8, 2012 at 8:56 am
Coming from 2005, probably not. If you'd been coming from SQL 2000 I would have said absolutely yes.
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
July 8, 2012 at 9:31 am
GilaMonster (7/8/2012)
Coming from 2005, probably not. If you'd been coming from SQL 2000 I would have said absolutely yes.
I keep hearing that they have fixed this issue - and still find that it has problems in 2005. I haven't checked on 2008 or above though.
I don't think it will hurt to run it - but it isn't something that you really need to do.
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
July 8, 2012 at 9:38 am
Jeffrey Williams 3188 (7/8/2012)
GilaMonster (7/8/2012)
Coming from 2005, probably not. If you'd been coming from SQL 2000 I would have said absolutely yes.I keep hearing that they have fixed this issue - and still find that it has problems in 2005. I haven't checked on 2008 or above though.
It got fixed in SQL 2005, in 2005 SP1, in 2005 SP2, in 2005 SP3 and in 2005 SP4. Basically most of the issues were fixed for SQL2005 RTM, some slipped through and were fixed later. It's possible there's still outstanding problems in 2008.
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
July 8, 2012 at 9:43 am
GilaMonster (7/8/2012)
It got fixed in SQL 2005, in 2005 SP1, in 2005 SP2, in 2005 SP3 and in 2005 SP4. Basically most of the issues were fixed for SQL2005 RTM, some slipped through and were fixed later. It's possible there's still outstanding problems in 2008.
That is what I have seen also. I don't think it is as big an issue now though...like I said, won't hurt anything to run it at least once but isn't something I would say you have to do for the upgrade.
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
July 8, 2012 at 8:23 pm
Thanks to everyone for their help!
BD
July 10, 2012 at 1:01 am
Jeffrey Williams 3188 (7/8/2012)
GilaMonster (7/8/2012)
It got fixed in SQL 2005, in 2005 SP1, in 2005 SP2, in 2005 SP3 and in 2005 SP4. Basically most of the issues were fixed for SQL2005 RTM, some slipped through and were fixed later. It's possible there's still outstanding problems in 2008.That is what I have seen also. I don't think it is as big an issue now though...like I said, won't hurt anything to run it at least once but isn't something I would say you have to do for the upgrade.
I am in the midst of a 2005 to 2008 R2 upgrade project and I still get a few hits when running it after upgrading 2005 SP4 DBs to 2008 R2. I always recommend it on the forums because it does not take terribly long even for larger DBs, and does not hurt anything to run it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 10, 2012 at 7:44 am
Is the order (DBCC spaceusage, UPDATE STATISTICS) in which they are run of importance?
BD
July 10, 2012 at 7:47 am
No.
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
July 10, 2012 at 9:04 am
Barkingdog (7/10/2012)
Is the order (DBCC spaceusage, UPDATE STATISTICS) in which they are run of importance?BD
Nothing for spaceusage...
Run DBCC UPDATEUSAGE then everything Gail said, in that order:
GilaMonster (7/8/2012)
Update all statisticsRun a checkDB
Update compat mode to 100
Take a backup
I'll also add to schedule your regular index maintenance job to run as soon as possible after the upgrade. If you have time during the upgrade window do that before the "take a backup" step above.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 11, 2012 at 3:40 am
Check for orphaned users.
Check for Cross-database ownership issue.
July 11, 2012 at 7:39 am
I also run a DBCC CHECKDB WITH DATA_PURITY. Well, more accuratly, I check to see if it needs to be run. Check out Paul Randal's post on how to determine this at http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-How-to-tell-if-data-purity-checks-will-be-run.aspx. Coming from 2005, you should be ok, but I've found databases that were migrated from 2000 to 2005 and then to 2008 and never had data_purity run, so the db flag Paul talks about was still set to zero.
Note: if needed, this can take a long time to run on large dbs.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply