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.