2

This seems a bit strange. I migrated a database in a test environment from one server (SqlExpress) to another (full SqlServer). I did that by detaching the database from the first server, copying over the mdf- and ldf-file and then attaching those to the new server. That worked well, except I see some performance losses, hence I want to do some comparisons in terms of speed and maybe exection plans and here starts my problem:

I cannot reattach the original database I left on the old server (SQLExpress), I did not do anything to it since it was detached and it worked like a charm until I did.

But now, when I want to attach it to the original database server (Sql Express) I get this message:

CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database. (.Net SqlClient Data Provider)

Sad thing is, this is party true, the database is 10,911,616 KB in size (1,024 KB for logs), which, honestly, stumps me, since I didn't do anything with it after detaching and it worked up until then.

However I am very certain, there is lots of free space inside that database (something around 9GB)

So this is a three-part-question:

  1. Can someone point me to the circumstances that could lead to this problem (database working on the very same server, then detached, not changed (at least by me), then not being able to re-attach?
  2. Is there a way to free up space inside that mdf-file without actually attaching it?
  3. Or could it be feasible to take the database I already attached to the new (full) SqlServer, shrink that in size and then attach it to SQLExpress? (or would attaching to full SqlServer change the format of said mdf-file to be unusable by SqlExpress?)

additional Server information:

  • New Database Server has version Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
  • Old Database Server has version Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
  • Database is running in compability level SQL Server 2008 (100)
  • You cannot reattach database that was upgraded when attached to SQL Server 2012 back to 2008 R2 even after shrink. Downgrade of database is not supported – sepupic Sep 29 '17 at 14:25
  • now if you need to run on SQL Server 2008 R2 you are going to take data out of the new database using export-import or a similar technology – George K Sep 29 '17 at 14:31
  • @GeorgeK any hints on how to do that without attaching the database? – DrCopyPaste Sep 29 '17 at 14:35
  • Presumably you cannot restore a backup of the original database since you don't have one? That's a great lesson to learn - always have a backup plan. – Max Vernon Sep 29 '17 at 16:57
  • Are you sure your are looking at the right file? 2008R2 database size limit is 10GB which DOES NOT include log file. – SqlWorldWide Oct 2 '17 at 1:28
2
  1. Can someone point me to the circumstances that could lead to this problem (database working on the very same server, then detached, not changed (at least by me), then not being able to re-attach?

You have 2 problems:

  1. Your 'old' server is 2008R2 and the new one is 2012. So as @sepupic stated you will not be able to attach database from 2012 to 2008R2.
  2. The limit of 10Gbs is exceeded

What to do:

  • attach your DB to SQL Server 2012 (or higher) of edition higher than SQL Express
  • Export data from there to SQL Server 2008R2 (if you really need to run 2008R2 version)
  1. Is there a way to free up space inside that mdf-file without actually attaching it?

you do not actually need it. I do not know such a way.

  1. Or could it be feasible to take the database I already attached to the new (full) SqlServer, shrink that in size and then attach it to SQLExpress? (or would attaching to full SqlServer change the format of said mdf-file to be unusable by SqlExpress?)

you can do it if you will work on SQL Server 2012 Express. No - the format will not be changed.

  • Thank you for the answer, but I am not sure it helps me. Attaching to SQL Server 2012 or higher is not an option atm, because I want to be able to test behavior with to old server and that is not 2012 ;( – DrCopyPaste Sep 29 '17 at 15:10
  • As to your answer to my first question, I know I exceeded the limit, but my question is not what is the reason I cannot add it now, but how could it get into a state where it cannot be re atached when before it did (how could SQLExpress DB get beyond that limit in the first place) – DrCopyPaste Sep 29 '17 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.