Last post Jan 02, 2019 11:40 PM by PatriceSc
Jan 02, 2019 04:03 PM|shahid.majeed|LINK
I am facing a problem on our production server, my primary database file size reached 10GB. we have enough free disk space on the server. that is not the problem.
But database file is not increasing and getting following error.
Could not allocate space for object 'dbo.RumBokBokningHistory' in database '7A' 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.
Jan 02, 2019 04:35 PM|PatriceSc|LINK
Could it be that you are using SQL Server Express ? It is limited to 10 Gb for the database size (it seems even to be the initial size for your db which might have been done on purpose ???)
Jan 02, 2019 04:38 PM|shahid.majeed|LINK
I am using sql server 2008 r2 express eddition now and i believe also it have file size limit 10GB becuase my file size is exactly 10GB now.
However i checked through a script which is the biggest table in the database. and i drop that data table. But file size still 10GB.
Jan 02, 2019 05:01 PM|PatriceSc|LINK
Yes, the db file doesn't shrink automatically when you make some space in it. You can use SQL Server Management Studio reports to see which space is now free inside your 10 Gb database file : see
Shrinking the file is not needed and in your case it seems the plan ways anyway to size the db file at the maximum size right away.
And yes this is limit in SQL Express. You may want to have something in place to monitor the available db space and do something when you are approaching the limit (maybe deleting some historical data that are not needed any more or at least moving them
to another db etc...)
Jan 02, 2019 05:16 PM|shahid.majeed|LINK
I tried to check which one is largest tablel in my database. I got following top table. so i sum all the table used space and it is only 1 GB.
Jan 02, 2019 08:08 PM|PatriceSc|LINK
My understanding is that you already dropped data so it could be expected . Once again it is expected that the file size is not reduced automatically. So it could still be at 10 Gb, having now 1 Gb used and 9 Gb of free space that could be now reused (and
all is fine now, there is nothing more to deal with urgently).
Stricyl speaking, reducing the file size is not required (and likely not possible in your case as the initial size is AFAIK also the minimal size of the file). Anyway it could be left for later.
To be on the safe side rather than using custom scripts I would use for now SQL Server Management Studio Reports and in particular the "disk usage" that just shows how much available space you have in your db file.
Then only I would have tried to backup my db and delete historical data that are not needed any more (which is likely the bigger table but hopefully you deleted this table because you know also you don't need it ?)
Then being past the immediate crisis I would look at what to do so that it doesn't happen again.
Jan 02, 2019 08:36 PM|jzero|LINK
By pictures and tables, seems you will reach 10Gb again sooner, and will have to send some data to trash again.
Better would be move to SQL Server Web or Standard, in the mean time I think you can implement Data Compression to get some more space and time, of course there is a cost.
Jan 02, 2019 08:47 PM|shahid.majeed|LINK
I generate first report i its show large green portion is that mean i have now alot of space for data? Please check the link
Jan 02, 2019 08:57 PM|shahid.majeed|LINK
So it could still be at 10 Gb, having now 1 Gb used and 9 Gb of free space that could be now reused (and all is fine now, there is nothing more to deal with urgently).
I reached to 10GB in 7 years, i dont have huge traffic toward my system.
Few hours ago i was thinking to go for Standard Edition. Now according to Report its shows i almost have 9 GB unallocated space again. So its fine for me for next couple of years.
I think file size is 10GB and I have 10GB or data in the file. But this is not the case basically it just 10GB reserved and actually data size 1GB. am i right ?
Jan 02, 2019 11:40 PM|PatriceSc|LINK
Yes, the file size doesn't really tell much. When you insert data the file can grow automatically. When you delete data, the size is never reduced automatically. You have to explicitly ask for this if you want. Also you can't reduce the file below its initial
size (and in your case it is also configured to 10 Gb).
Seems you should make sure you'll get an alert if a database is nearly full and you could also put something in place to automatically clean up data which are not needed any more. Not sure what you deleted but it seems you keep old and unused data in your