SQL 2000 DB to SQL 2012? - SQL Server Forums

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

SQL Server Administration (2012)

SQL 2000 DB to SQL 2012?

Author

Topic

mrpush
Starting Member

14 Posts

Posted - 2013-06-11 : 16:51:53

Hi,

I have a DB originally created and run in SQL 2000 that I sent to a cloud provider to use there. They stated they had to do "something" to the database to get it in SQL 2012. The MDF file I sent them was 20GB non compressed. Once they attached it, now it 40GB.

Questions:

Can a SQL2000 DB be attached(upgraded) directly to SQL2012 instance or is there some intermediate step to get it there?

Can / could this process somehow DOUBLE the size of the DB in any case?

Thanks,

M

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-11 : 17:01:57

They would have had to change the compatibility level of your SQL 2000 database from 80 to at least 90, because that is what the minimum compatibility level that SQL 2012 requires.

There is some info here on compatibility levels: http://msdn.microsoft.com/en-us/library/bb510680.aspx

As to why the size of the database doubled - I don't know, and I don't know any reason why it should. If you have the ability to do so, look at the file sizes and how much free space is available. It just may be that the files are large, but a lot of it is unused.

mrpush
Starting Member

14 Posts

Posted - 2013-06-13 : 11:18:05

James,

Thanks for the reply. So changing the compatibility level is a simple T-SQL command run.

I did some googling and did not come up with anything related to compatibility and database size increases. After some looking about, I did see that there is now 9GB of the 40Gb that is free space in the DB. However that still does not explain the 11GB increase in size. I wonder if the compatibility change has done this just not sure.

Thanks,

MP

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-13 : 13:54:49

Run this command on both the old and the new servers to see what is taking up the extra space

SELECT  OBJECT_NAME(object_id) objectName ,
        SUM(used_page_count) used_pages ,
        SUM(reserved_page_count) reserved_pages
FROM    sys.dm_db_partition_stats
GROUP BY OBJECT_NAME(object_id)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-13 : 13:57:24

Strike what I said above. SQL 2000 does not have DMV's, and I don't know the commands to find the usages in SQL 2000. You can find space used by tables using this, you will need something similar for indexes.

sp_Msforeachtable 'exec sp_spaceused ''?'''

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources