2

I have a database in SQL Server 2012 Express. I inserted 14 million rows to test index performance and everything was working fine. The database's PRIMARY file group was at over 21 GB. I had applied this index manually in the Management Studio designer, and even dropped and recreated it a few different times, trying different clustered indexes on different keys to see which one performed best for the queries I was using. Once I settled on one, I had to drop the index and restore the original clustered index on the primary key, so I could create a migration with entity framework that would build the clustered key I wanted. I manually inserted lines into the migration to drop and recreate the primary key as non-clustered so that the new clustered index could be created.

Suddenly, when trying to create the clustered index, which should be less than 4 GB, I get the error:

Msg 1101, Level 17, State 12, Line 6
Could not allocate a new page for database 'MyDatabaseName' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

I'm not out of disk space with 702GB free on the drive, and the database file was already clearly over the 10 GB limit that I've been reading about. I tried shrinking the database. That got it down to about 7 GB, so I have no idea how or why it was up to 21 GB in the first place. I cannot set the size greater than 10 GB now, as I get a licensing error.

What I'm trying to understand is how I was able to add all that data to the database and drop and recreate this very index and ones just like it many times before without any issue and how the database clearly exceeded the size limit, yet now it's suddenly enforcing the limit.

  • SQL Express limitation is 10GB for every database. And this is only the case for datafiles. Are u sure it was not 21GB, f.e. 8GB datafile and 13GB logfile? – Stijn Wynants Jun 20 '16 at 11:07

Your Answer

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

Browse other questions tagged or ask your own question.