Indexes messed after 2000 -> 2005 db move

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 2005 Forums

SQL Server Administration (2005)

Indexes messed after 2000 -> 2005 db move

Author

Topic

jbabco
Starting Member

8 Posts

Posted - 2008-06-13 : 11:08:32

Recently moved a couple of dbs from SQL 2000 -> SQL 2005 (different machine). Just restored the dbs on the new server from a full backup of the old one. Everything is fine but...

Some queries are returning out-of-order results (for records added since the migration). I tracked this down to the indexes. I completely rebuilt and reorganized the indexes (online) through the manager interface. This helped provide correct ordering for existing records, but new records added since the re index are still out-of-order.

Looking for a way to permanently fix the indexes so I don't have to constantly rebuild them.

Thanks.

sqldba20
Posting Yak Master

183 Posts

Posted - 2008-06-13 : 11:58:26

Did you change the compatibility after you moved the databases? Maybe you can follow these steps

1) Change the compatibility of the database from SQL Server 2000 (80) to SQL Server 2005 (90). ---
EXEC sp_dbcmptlevel @dbname=N'databasename', @new_cmptlevel=90

2) Run DBCC UPDATEUSAGE on each database ---
use databasename
go
DBCC UPDATEUSAGE(databasename)
go

3) Run DBCC DBREINDEX on each table. ---
use databasename
go
DBCC DBREINDEX(table name)
go

Thanks !

jbabco
Starting Member

8 Posts

Posted - 2008-06-13 : 13:00:52

Thanks for the tip. No, I had not set the compat level.

I'll know soon if this did the trick.

Thanks again!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-13 : 14:06:18

If you want query results to be in a certain order, you have to use an ORDER BY clause in your SELECT.

There is never any guarantee of the order of a result set if you do not use an ORDER BY, no matter what version of SQL Server you are using.

CODO ERGO SUM

jbabco
Starting Member

8 Posts

Posted - 2008-06-16 : 14:47:35

Yes. I guess what's messing me up is that on the old server they always were in the correct order by default (the order they were entered in the db). So without any T-SQL changes, the order returned now is different. At first I thought it might have something to do with collation, but rebuilding the indexes (temporarily) fixes things.

I'm again seeing incorrect ordering. sqldba20's advice helped somewhat but now it's back to "messed up".

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-16 : 14:48:51

You got lucky in SQL Server 2000 as you were never guaranteed order without an explicit ORDER BY. Fix your code so that you include your ordering. Do not rely on how SQL Server retrieves the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jbabco
Starting Member

8 Posts

Posted - 2008-06-16 : 14:57:49

Fair enough. The ordering thing is annoying, but what's worrying me is if the returned data is *wrong*. I haven't seen that yet (fingers crossed).

Would completely dropping and recreating the indexes be a recommended step here?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-16 : 14:59:48

The returned data will not be wrong.

I don't know what you mean by recommended. If you are trying to solve the ordering, then recreating the indexes is not recommended.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources