I already provided some reasons why you see this error in the Control Panel in a previous blog post:

so I won’t bore you with the details again.  Instead, I will be covering another error that is thrown and trapped on the back end:

“MODIFY FILE failed. Size is greater than MAXSIZE.”

Your database add-on space is capped depending on the type of database you ordered and how much extra disk space you purchased.  You can view the maximum size of your database using the tools in the DiscountASP.NET Control Panel by clicking on the MS SQL Manager link under the Database Management section in the menu to your left:

Your transaction log disk space is also capped at 1000 MB (1 GB).  Below are instructions on how to create and fix the problem.

How You Can Create the Problem

As in my last tutorial, I will be using the AdventureWorks database in this example.  First, I’ll attach both the database and log file to my local SQL Server instance.

Next, right click on the database and select Properties.  Go to the Files section and notice the initial size.

Go ahead and change these values to 500 and 1500, respectively, and click on OK.

Backup the database, upload it and try to restore it using the tools in the DiscountASP.NET Control Panel.  A base SQL Server 2005 database is 300 MB and the transaction log size is 1000 MB, so it will fail when you try to perform the restore and throw the error mentioned earlier.

Now, if you had only set the primary data file (.mdf) to 500 and left the transaction log (.ldf) with a value under 1000 MB and tried restoring to a higher version such as SQL Server 2012, it will work because the newer versions have a higher base value.  This problem will also create itself naturally as your database grows and fills up with data, or if you issue too many SQL commands in an instance, causing the transaction log to fill up too quickly.

The Solution

The solution is fairly simple.  You can try recovering unused database space by shrinking it.  Right click on your local database instance and select Tasks -> Shrink -> Database.  You can also shrink your database by opening a New Query window in SQL Server Management Studio and executing this SQL Statement:

DBCC SHRINKDATABASE (N’DatabaseName’, TRUNCATEONLY)

For example:

DBCC SHRINKDATABASE (N’AdventureWorks’, TRUNCATEONLY)

You can check the new values in the Initial Size (MB) column by right clicking on the database, selecting Properties and then Files.  Make sure the Initial Size (MB) of the primary data file is less than the value found in the DiscountASP.NET Control Panel and that the Initial Size (MB) of the transaction log file is less than 1000 MB (1 GB).  If the Initial Size (MB) of the primary data file is greater after you shrink it, then that means you just need to order more SQL Server disk space.

In this series of articles I hope to cover some of the common problems our customers face when backing up and restoring a database, centralize the information scattered throughout our site and forums over the years, show you how the problem may have been created in the first place and provide you with a step-by-step guide on how you can identify the problem and resolve it.

That’s a tall order, but if you’re ready, here we go.

You might see this message once in a while when restoring a database:

There are a few reasons why we do this:

1)    We trap the SQL Server Error thrown on the back end so that one of our support staff can research and review it, interpret it for you and guide you to a solution.  Because we have a diversity of customers who contact us with varying technical skills and knowledge, displaying an error might be meaningful to an experienced developer or tech geek but not so much to the average user or a beginning developer who has his application working locally but is finding difficulty getting it to work in our hosting environment.

2)    We have International customers and bridging the language barrier can be already difficult.  If we displayed the error message, it might lead to even more confusion trying to explain it.

3)    It wouldn’t look pretty. 

In this walk-through, I will cover some of the more common errors that our customers run into, show you how it may have been created and provide you with a solution.  The errors are:

“More than 2 file list in mdf file” or “More than 1 data or log file in bak file”

To simplify things, our hosting platform only supports restore operations for databases containing only one data file and one log file.  And here are a couple of ways on how to create the problem.

Method 1: Adding a Full Text Catalog

To create this problem, I’m going to download the sample AdventureWorks database for SQL Server 2005 here.

(Note: You will need to double click AdventureWorksDB.msi to extract the files.)

Then I am going to upload the AdventureWorks_Data.mdf file only to my hosting account and use the Attach tool to restore it to my DiscountASP.NET database.  Now, I am going to log into my database here at DiscountASP.NET using SQL Server Management Studio and execute this T-SQL statement to create a Full Text Index/Catalog:

USE [DiscountASP.NET database name];
 GO

CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID);
 CREATE FULLTEXT CATALOG ft AS DEFAULT;
 CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX ui_ukJobCand;
 GO

After that, I am going to use the DiscountASP.NET Control Panel to create a backup of the database.  You shouldn’t run into a problem if you perform a restore to the same version of SQL Server 2005.  The problem occurs when you try to restore to a higher version of SQL Server such as 2008, 2008R2, or 2012.  This is the error that will occur:

“More than 2 file list in mdf file”

Now if I download that .bak file and try to restore it on my local machine where I have full rights to my SQL Server instance then I can see the problem.  The Full Text Catalog is part of the file list.

You can also view this information by executing this T-SQL statement:

RESTORE FILELISTONLY
 FROM DISK = N'[Path to Backup File]'

To fix the problem, I will go ahead and restore the database on my local machine.  Once restored, open a new query window and execute this T-SQL statement to drop the index:

DROP FULLTEXT INDEX ON HumanResources.JobCandidate

Then right click on the database and select Properties.  In the Database Properties window, select Files and then the Full Text Catalog.  Use the Remove button to remove it.  Click on the OK button.

Prior to removing the catalog, you should scroll all the way to the right.  You’ll notice that the Full Text Catalog is stored as a secondary database file or .ndf file.  Secondary database files are used for a lot of reasons including organizing data and spreading it across different disk partitions to improve performance.  It’s more useful in a dedicated server environment and very large databases where you may need the performance increase.

If we backup the current database (without the Full Text catalog) locally and upload it, then we will be able to perform the restore using the tools in the DiscountASP.NET Control Panel.  Make sure the version of SQL Server running on your local machine matches the version on the DiscountASP.NET server, or you will run into a different error.

This is something you may want to keep in mind when upgrading your database, and it fails.  You can re-create the Full Text Catalog after the database has been restored.

Method 2: Adding an .ndf file

The second way to create this problem is to create a secondary database (.ndf) file.  To do this, I will go ahead right click on my local database and select Properties.  Then I will click on Files and the Add button.  Notice a new row has been appended under the Database files section.  I’ll give the new row a logical name of SecondaryFile.

Now, scroll to the right and under Filename, type Secondary.ndf and click on the OK button.

If you were to backup this database, upload it, and try the DiscountASP.NET Restore tool, you would get this error message on the back end:

“More than 1 data or log file in bak file”

And the solution is similar to the one above.  If you have data stored on the secondary file, you will need to move it by executing this command:

DBCC SHRINKFILE (Logical Name, EMPTYFILE)

So for example:

DBCC SHRINKFILE (SecondaryFile, EMPTYFILE);

Highlight your database, right click on it and select Properties.  Go back to the Files section and use the Remove button to remove the secondary file.  You will now be able to restore the backup using the DiscountASP.NET tools.