We help IT Professionals succeed at work.

SQL 2005 Backwards Compatibility - Performance Issues

We had a hardware failure on one DB Server, and had to restore SQL 2000 DBs to a DB Server with SQL 2005.  We changed Compatibility Mode to 80.  Users are functioning, but are reporting slowness and time-outs.  The machine we restored to has a faster processor and double the RAM.  Neither are experiencing average utilization above 50%.  In fact, we see no hardware issues in any of the machines involved in the path from Web Server to App Server to DB Server to SAN.  Users on other DB Servers are not experiencing problems, but none of those have utilized backwards compatibility.

Are there any known significant performance issues relating to utilizing backwards compatibility????  And/Or, any suggestions on where to look further to isolate the true problem, and to fix it?
Watch Question

Database Consultant
Top Expert 2009
did you execute these after the restration

EXEC sp_UpdateStats


We had not run these.  We just ran them in test, and since we clearly see no harm in these, we are running them in production.  While we see no harm in them, what is it about these that would "fix" the problem?  We are not doubting you, but rather, we are just trying to clarify our understanding.  Thanks.
Most Valuable Expert 2014
After a restore of the database, especially a different/new server, until these maintenance type tasks are run, statistics and detailed database consistency checks aren't really performed.

The DBCC CHECKDB command needs to have the database in single-user mode, so that will probably have to be run out of hours.

Note that even with the databases in Compatibility mode for 80, you can't just detach and re-attach the databases to a SQL2K box.  The only way to take them back is by using DTS to import them back into an empty database.


Additional information:  SQL Server is not logging messages to its log file, as in messages like those referring to starting or stopping of the service.  Again, this is isolated only to the SQL 2005 DB Server into which SQL 2000 DBs were restored.  Our other DB Servers (both 2000 and 2005) are functioning just fine.

To the specific comments by "jimpen", we did create empty databases, but we did not import using DTS.  Instead, we restored from SQL 2000 BAK files.  Would there be any issues with the approach we took?
Most Valuable Expert 2014
I know it is a click or checkbox in SQL somewhere to get those events logged. I just can't remember where. I hit it when I'm configuring SQL right after an install. I'll look for it Monday.

The other possibility is that your errorlog directory isn't there. Go to the SQL Server Configuration Manager. Right click MSSQLSERVER --> Properties --> Advanced tab and in the startup parameters it should have something like:
as one long line. Fine the -e and make sure that path down to the word "ERRORLOG" exists.

>> Would there be any issues with the approach we took?

No issues with how you did it. Just there is no easy way to go back to SQL2K if you were wanting to do that. I ran into the issue shortly after upgrading our first SQL server to 2K5. I couldn't take a database back to SQL2k after 2K5 touched it. Not a big problem if you aren't reverting.

Top Expert 2011

Hello jimmckenzie,

Migrating you database shouldn't be a spur of the moment decision , even in compatability mode you aren't guaranteed that the system will continue to function as previously...

you do need consider re-organising / re-indexing  a database whenever it is moved.
the restoration process of restoring the 2000 backups onto a 2005 system HAS upgraded you DATABASE to 2005 structures even though you're running in 2000 mode.

there can be problems "slow response etc"  for legacy applications attaching to sql 2005 systems perhapos your base paroblems lie there...

you should run the upgrade advisor on a copy of the 2000 database ASAP to see if there are any obvious issues with your system which should have been addressed prior to migration.

a better choice would probably have been to install a sql 2000 instance on one of your servers and restored to that.

as previously stated there is no immedaite way back for your database now that it a 2005 one apart from unloading all the data and reloading to a fresh / empty sql 2000 version.

good luck




We still have performance issues, but those appear to be inherent in that which me upgraded using the Compatibility Mode.  We will not see those issues go away until we modify our physical code.  However, the maintenance steps provided did help.
Most Valuable Expert 2014

Glad to be of assistance. May all your days get brighter and brighter.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.