Author |
Topic |
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2010-01-12 : 09:31:33
|
Hi,I have restored the databases to sql 2008 , Executed the updateusage and then check the status of the database. Ran the below sql and output is select name,status from master..sysdatabases name statusmaster 65544tempdb 65544model 65536msdb 65544ReportServer 65536ReportServerTempDB 65544mosis 65544DB01 20DB01 16DB01 16DB01 20DB01 16DB01 16DB01 16DB01 8DB01 16DB01 16DB01 16DB01 16Next, i went to msdn, to check the status's and here is the list http://msdn.microsoft.com/en-us/library/aa260406(SQL.80).aspxStatus bits, some of which can be set by the user with ALTER DATABASE (read only, offline, single user, and so on): 1 = autoclose; set with ALTER DATABASE. 4 = select into/bulkcopy; set with ALTER DATABASE RECOVERY.8 = trunc. log on chkpt; set with ALTER DATABASE RECOVERY.16 = torn page detection, set with ALTER DATABASE.32 = loading.64 = pre recovery.128 = recovering.256 = not recovered.512 = offline; set with ALTER DATABASE.1024 = read only; set with ALTER DATABASE.2048 = dbo use only; set with ALTER DATABASE RESTRICTED_USER.4096 = single user; set with ALTER DATABASE.32768 = emergency mode.4194304 = autoshrink , set with ALTER DATABASE.1073741824 = cleanly shutdown.Multiple bits can be on at the same time.Here are my questions, 1. In my query output , i can see some databases with "status" 16 i.e torn page. Is there any harm? if so what we can do to avoid such errors. 2. in my output if you can see status = 65544. What does it mean? i cannot find desc for this value. What does it signify?3. i can also see status = 8 , what does it indicate. is it ok or else do we need to do something?4. what does 65536 signify?5. what does 65544 signify?But good thing is that, all are online select name,state,state_desc from sys.databases name state state_desc master 0 ONLINEtempdb 0 ONLINEmodel 0 ONLINEmsdb 0 ONLINEReportServer 0 ONLINEReportServerTempDB 0 ONLINEmosis 0 ONLINEDB01 0 ONLINEDB01 0 ONLINEDB01 0 ONLINEDB01 0 ONLINEDB01 0 ONLINEDB01 0 ONLINEDB01 0 ONLINEDB01 0 ONLINEDB01 0 ONLINEDB01 0 ONLINEDB01 0 ONLINEDB01 0 ONLINEThanks in Advance |
|
Kristen
Test
22859 Posts |
Posted - 2010-01-12 : 11:18:42
|
I don't think this is telling you anything useful - well, I don't think its telling you what you probably want to know."1. In my query output , i can see some databases with "status" 16 i.e torn page.Is there any harm? if so what we can do to avoid such errors."That's just telling you that the database is configured to DETECT torn pages. I reckon that's a good thing - if something bad happens SQL will detect it What you may have meant to do isDBCC CHECKDBon the database you restored to check that there are no errors.However, I would suggest that before you do that you change the database compatibility to SQL 2008 (Right click the database, choose OPTIONS and its on that page); a restored database will have the compatibility mode of the server it came from, and running at less than SQL 2008 (on a SQL 2008 server) is probably not what you want to do.Then Reindex all tables / indexesThe Update UsageThen DBCC CHECKDBThen take a Full Backup, and add it to your Backup Maintenance Plan.I've been meaning to write a SQL2008 migration post, but I did write one for SQL 2005, and much of that is relevant to SQL 2008 IMHO:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80138 |
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2010-01-13 : 02:36:55
|
I have done the following1. restored 2000 bkp2. observed that compatibilty level is still 80 i.e 20003. ran update usage 4. dbcc checkdb --no errors-- 5. done the index rebuilds 6. finally changed the compatibilty mode to 2008Is that what you want to say? pl correct if am wrong.Thank You. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-13 : 02:58:36
|
I would have done in this order:126534I've started a post on SQL 2008 migration. In case helpful to you:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230 |
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2010-01-13 : 04:48:30
|
Thank You So Much! |
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2010-01-13 : 05:03:10
|
Kristen,One question.You said,1. restore 2. observed the compatibilty mode 803. immediately change to 1004. done the index rebuilds 5. Ran update usage 6. Dbcc checkDbIs there any reason why running the index rebuilds first followed by UPDATE USAGE and then DBCC CHECKDB ? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-13 : 08:22:08
|
I'd like the DBCC CHECKDB to be last to prove that nothing got broken during migration (maybe SQL 2008 Reindex runs fine on SQL 2008 database, but just maybe there is slightly damaged data in SQL 2000 that might break during the first SQL 2008 reindex - I'm a cautious person ). So probably makes sense to do it first too (no sense trying to migrate a broken database !!)Supposedly UPDATE USAGE is not needed from SQL 2005 onwards (or maybe they fixed it properly only in SQL 2008, I forget exactly).My SQL 2000 databases do get "fixed" when I run UPDATE USAGE though, so that's a needed step after SQL 2000 database restore to SQL 2008.Again, I don't trust anything!, so I want to run it last too.However, you raise a good point. What if I restore a database where Usage is incorrect, will any of the Reindex etc. tasks do anything wrong (or "less well") because of it? Maybe it would be best to do that first to ensure Usage Stats are fixed before doing any SQL 2008 housekeeping tasks.I'm still not trusting that it is really REALLY fixed until I see it with my own eyes, so I'll still do an UPDATE USAGE at the end of the migration process Belt & Braces, but folk will probably tell me its absolutely no longer required . Definitely required for a database migrated from SQL 2000 though.Note that in the post I linked to I'm doing Data Purity checks as well, so even more Belt & Braces. |
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2010-01-14 : 00:58:12
|
Thanks. That's really knowlegable.One more, After changing the compatibilty mode, we still can work with all the 2000 features. Right?I feel changing the compatibility mode is taking advantage of 2008 features( provided with backward compatibilty).After changing the compatibility to 90/100 and after running the dbcc chckdb on each database, would fix all the problems?I mean all deprecated features everything? or else do we need to check each and every stored procedures, views ,functions and all? Am asking this because before giving for testing team, how a DBA can make sure to some extent things are fine( excluding DTS packages, jobs , maintenance plans which am taking care separately).I have also run the upgrade advisor against databases and sql server but except DTS error but i didnt receive any errors related to database.Also, you are talking about data purity checks. Am assuming the DBCC CHECDB command would fix all those issues and there is any other(s), it will report to us.Correct me if am wrong.Thanks. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-14 : 03:43:48
|
"After changing the compatibilty mode, we still can work with all the 2000 features. Right?"Hmmm ... that's probably a "Yes and No" answer There may be some features that were deprecated and have now gone in SQL 2008 (you probably aren't using them) or columns that have changed size (there are some system table columns that have gone from INT to BIGINT). Upgrade Advisor should tell you about those.There is potentially some "different behaviour" that may break your application. This for example:SELECT ColumnIWantToSeeEasily, *FROM MyTableORDER BY ColumnIWantToSeeEasilyI use this a lot in DEV just to check data, and put a few columns at the front of the list to make it easy to see that data - then scroll Right if I need to see other columns.The [ColumnIWantToSeeEasily] is ambiguous because it is also in the SELECT * part. However, when I try to ORDER BY on it both SQL 2005 & SQL 2008 raise an error; SQL 2000 never cared.I have a few of those in my actual application too (but don't tell anyone, OK? )There is also the possibility that the way that queries are executed has changed and may break, or slow down, your application. One thing that came to light with SQL 2000 SP4 (and I think with SQL 2005 too) was when a WHERE clause compared columns / expressions of different types, and made an implicit datatype conversion. The performance of those fell like a stone when SP4 came out - the fix was to use an explicit CAST.So there may be gotchas like that when migrating to SQL 2008 - maybe Upgrade Advisor warns about them, maybe not. Because of the risk I will do a full regression test on my application."I feel changing the compatibility mode is taking advantage of 2008 features"You definitely won't be able to use features new to SQL 2008 if you leave compatibility mode at an earlier version"After changing the compatibility to 90/100 and after running the dbcc chckdb on each database, would fix all the problems?"I'm running DBCC CHECKDB just to double check that there is no damage to the database - before I start using it "for real". That's all. I'm not planning to let it fix anything, I just want reassurance that everything validates OK."I mean all deprecated features everything? or else do we need to check each and every stored procedures, views ,functions and all? Am asking this because before giving for testing team, how a DBA can make sure to some extent things are fine( excluding DTS packages, jobs , maintenance plans which am taking care separately)."My view? You have to test everything. Even if you kept compatibility mode at the old version how could you be sure that there was no code in your application that will be handled differently in SQL 2008? SQL 2008 is a big piece of software! On that basis you might as well change to SQL 2008 compatibility mode - you've got to run the test anyway - fix anything that breaks, and then you have all the benefits of being native in SQL 2008 - improving your code by adopting new SQL 2008 features, and so on.I have a vague memory that when SQL 2005 came out running it in SQL 2000 compatibility mode was a lot slower, so it was kind-of a band-aid only."I have also run the upgrade advisor against databases and sql server but except DTS error but i didnt receive any errors related to database."Excellent! I wasn't so lucky (but I only had a handful of changes to make)"Also, you are talking about data purity checks. Am assuming the DBCC CHECDB command would fix all those issues and there is any other(s), it will report to us."I don't think it fixes anything, although there is a "correct errors" mode, but I'd prefer to take care of any errors myself.Hmmm ... I wonder if there are any other features in SQL 2008 that may be disabled by default on a database restored from an earlier version - "Torn page Detection" for example, I would definitely want that turned on. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-14 : 03:57:50
|
Here's another (from within the body of an Sproc):IF @intDebug = 1 SELECT * FROM SomeTable WHERE ColumnDoesNotExist = 123 in SQL 2000 the syntax error (column name does not exist any more) is only reported at runtime when executed - i.e. when @intDebug = 1in SQL 2008 its checked at runtime even when @intDebug = 0 (perhaps to make the query plan)That's broken several of my SProcs where I didn't even know that I had incorrect column names in debug statements 'coz those SProcs haven't been run in debug mode for years! |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-14 : 04:25:08
|
I checked a database restores from SQL 2000 to SQL 2008:Recovery : Verify : is set to "Torn Page Detection"so that is good |
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2010-01-16 : 00:57:20
|
Hi Kristen,Thanks so much.Why am asking all this is because when i run the Upgrade Advisor 2008 against 2000 instances, am getting an error which is poping me an windows dialog saying Send Error message and terminating.Am able to analyze only the DTS packages!I dono why the error is coming.i have tried to run the upgrade adviosor multiple times but the same error is poping up. Any idea's? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-16 : 04:23:07
|
Worth running Upgrade Advisor 2005 against your SQL 2000 database?Can you restore to SQL 2005 somewhere, and run Upgrade Advisor 2008 against that, just as a one-off? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-16 : 05:19:31
|
quote: Originally posted by Kristen I checked a database restores from SQL 2000 to SQL 2008:Recovery : Verify : is set to "Torn Page Detection"so that is good
Once the DB's upgraded to 2005/2008, that should be changed to CheckSum. Torn page was the only option on SQL 2000 (other than none), it's not the best option on 2005/2008. CheckSum will allow detection of far more problems than torn page could.--Gail ShawSQL Server MVP |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-16 : 05:29:57
|
quote: Originally posted by Kristen "After changing the compatibility to 90/100 and after running the dbcc chckdb on each database, would fix all the problems?"I'm running DBCC CHECKDB just to double check that there is no damage to the database - before I start using it "for real". That's all. I'm not planning to let it fix anything, I just want reassurance that everything validates OK.
Other important reason to run CheckDb after an upgrade is because, on SQL 2000, CheckDB did not pick up all issues. Main thing here is that on SQL 2000 CheckDB did not run checkCatalog. Hence there could be schema corruption (often cause by direct modifications to the system catalogs) and you'd never know. On SQL 2005, CheckDB does run checkcatalog, hence those problems will be immediately picked up. Orphaned records cause by direct modifications to the system catalog are easy (relatively) to fix on SQL 2000. They're near-impossible to fix on SQL 2005. Hence you want to find those as early as possible so that you can restore the pre-upgrade backup to SQL 2000, fix the errors there, then upgrade again.quote: "Also, you are talking about data purity checks. Am assuming the DBCC CHECDB command would fix all those issues and there is any other(s), it will report to us."I don't think it fixes anything, although there is a "correct errors" mode, but I'd prefer to take care of any errors myself.
CheckDB won't fix data purity errors. It doesn't know what to do about them. Say there's an invalid value in a non-nullable datetime column. What should it update the value to? ChekDB will tell you what the data purity errors are. You have to fix them manually.--Gail ShawSQL Server MVP |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-16 : 05:54:21
|
"Once the DB's upgraded to 2005/2008, that should be changed to CheckSum.Torn page was the only option on SQL 2000 (other than none), it's not the best option on 2005/2008. CheckSum will allow detection of far more problems than torn page could."Cool, many thanks for that. I had (wrongly) assumed that Torn Page was new in SQL 2005, and given to me by default on restore to SQL 2008. I've changed my Migration Policy DOC to up-the-ante on that one and set to CHECKSUM.I've added your very useful explanation of CHECKDB benefits to my post [url=http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230"]Migrating to SQL 2008 Hints and Tips[/url]. Thanks. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-16 : 07:47:50
|
I've added a couple comments to that thread as well.--Gail ShawSQL Server MVP |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-17 : 10:39:04
|
There's a couple of things to know if you switch from torn page detection to checksum -- most importantly that it doesn't do anything on existing data pages until they are written to. Also it adds a little overhead.Be sure to read this before you change from torn page detection to checksum: http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/29/Enabling-CHECKSUM-in-SQL2005.aspx |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-17 : 16:24:43
|
Which (refering to the first of your points) is why I usually recommend switch to checksum and then rebuild all indexes. Won't help if there are heaps in the DB, but if all tables have a cluster that should touch all data and index leaf pages and most of the allocation pages. Not sure if it'll touch the LOB pages, I don't think a rebuild does.--Gail ShawSQL Server MVP |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-17 : 20:56:45
|
i think u are right |
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2010-01-20 : 09:15:31
|
Thanks All... |
|
|
Next Page
|