-1
I ran the following  command in Management Studio for SQL Server Express 2008R2

DBCC DBREINDEX('PropertyPair', '',90)

I received the following error

Could not allocate space for object 'dbo.SORT temporary run storage:  430271000936448' in database 'mydata' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

There is plenty of disk space but the data file is approaching the 10 Gig limit.

After studying This question I tried stopping SQL Server and running defrag. I also rebooted the server, but I still get the error.

If I look at the database properties in Management Studio I see that the size is 9661.19Mb and the space available is 2904.34Mb

If I drop the offending table then I can run DBCC Reindex on the other tables without error.

Kirsten
  • 15,730
  • 41
  • 179
  • 318
  • >>>>> "because the 'PRIMARY' filegroup is full" that seems self explanatory. Check your transaction log drive. hopefully not the same as data drive..... – Mitch Wheat Oct 20 '17 at 08:20
  • @MitchWheat aren't using a transaction log. Recovery model is Simple – Kirsten Oct 20 '17 at 08:53
  • you are using a transaction log, even in simple mode! – Mitch Wheat Oct 20 '17 at 08:59
  • @MichWheat it would be the same drive then. How do I check it? – Kirsten Oct 20 '17 at 09:14
  • Right-click the database in SSMS and get properties, then click Files in the list on the left. Allocate additional space to the file(s) and / or enable autogrowth. Be careful what you set autogrowth to. Frequent autogrowths can have a negative effect on performance. – squillman Oct 20 '17 at 12:03
  • @squillman I am already close to 10Gig which is the limit for 2008R2 – Kirsten Oct 20 '17 at 12:33
  • 2
    Ah, sorry. Glazed over the part that this is Express. About your only options are to shrink the file, delete data, drop indexes, or add another database and marshal data back and forth. Other than that you're looking at an edition upgrade. – squillman Oct 20 '17 at 12:41
  • @squillman The tip about dropping the indexes really helped. I dropped all but the primary index, then ran DBCC SHRINKDATABASE (mydatabase, TRUNCATEONLY) and was then able to run DBCC DBREINDEX('MyTable', '',90) without error. Do you care to write up your answer? – Kirsten Oct 20 '17 at 20:52
  • @kirsteng Sure thing, glad it helped! – squillman Oct 21 '17 at 17:35

1 Answers1

1

For SQL Server Express, about your only options if you are running up against the maximum data limit are to shrink the file, delete data, drop indexes, or add another database and marshal data back and forth. Other than that you're looking at an edition upgrade.

squillman
  • 13,363
  • 3
  • 41
  • 60