database status after migration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

database status after migration

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 status
master 65544
tempdb 65544
model 65536
msdb 65544
ReportServer 65536
ReportServerTempDB 65544
mosis 65544
DB01 20
DB01 16
DB01 16
DB01 20
DB01 16
DB01 16
DB01 16
DB01 8
DB01 16
DB01 16
DB01 16
DB01 16

Next, i went to msdn, to check the status's and here is the list

http://msdn.microsoft.com/en-us/library/aa260406(SQL.80).aspx

Status 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 ONLINE
tempdb 0 ONLINE
model 0 ONLINE
msdb 0 ONLINE
ReportServer 0 ONLINE
ReportServerTempDB 0 ONLINE
mosis 0 ONLINE
DB01 0 ONLINE
DB01 0 ONLINE
DB01 0 ONLINE
DB01 0 ONLINE
DB01 0 ONLINE
DB01 0 ONLINE
DB01 0 ONLINE
DB01 0 ONLINE
DB01 0 ONLINE
DB01 0 ONLINE
DB01 0 ONLINE
DB01 0 ONLINE

Thanks 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 is

DBCC CHECKDB

on 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 / indexes

The Update Usage

Then DBCC CHECKDB

Then 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 following

1. restored 2000 bkp
2. observed that compatibilty level is still 80 i.e 2000
3. ran update usage
4. dbcc checkdb --no errors--
5. done the index rebuilds
6. finally changed the compatibilty mode to 2008

Is 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:
1
2
6
5
3
4

I'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 80
3. immediately change to 100
4. done the index rebuilds
5. Ran update usage
6. Dbcc checkDb

Is 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 MyTable
ORDER BY ColumnIWantToSeeEasily

I 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 = 1

in 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 Shaw
SQL 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 Shaw
SQL 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 Shaw
SQL 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 Shaw
SQL 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources