SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 SQL 2000 DB to SQL 2012?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrpush
Starting Member

14 Posts

Posted - 06/11/2013 :  16:51:53  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 06/11/2013 :  17:01:57  Show Profile  Reply with Quote
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.
Go to Top of Page

mrpush
Starting Member

14 Posts

Posted - 06/13/2013 :  11:18:05  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 06/13/2013 :  13:54:49  Show Profile  Reply with Quote
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)
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 06/13/2013 :  13:57:24  Show Profile  Reply with Quote
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 ''?'''
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000