0

I have SQL Server Express 2019 on my local system on a large SSD with >50Gb free disk space.

I had a 14Gb database on production, which I copied, deleted a bunch of old data, and reduced the size below 10G, backed up and restored it on my local system. With some use, the local database then auto grew to 10G but still has a bunch of free space.

Now it has started giving me the insufficient disk space error when inserting a record in for some tables (not all which is so weird):

Could not allocate a new page for database 'MyDB' 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.

When I run the shrink database command, it just clears the log and does not release the free space in the data file. I have tried a variety of ways. It seems like the shrink database command itself needs some space and not getting it even thought the data file has a lot of space. Any ideas?

enter image description here

enter image description here

Improve this question
7
  • 2
    Considering that your local system is not a production environment, why are you not using sql server developer edition which does not have these limitations?
    – MBuschi
    Oct 14, 2022 at 6:39
  • I did not know about it, thanks for letting me know :)
    – Vaibhav Garg
    Oct 14, 2022 at 9:26
  • 1
    @VaibhavGarg What MBuschi said is only true if you're not doing any production work on the copy of the database on your local system. Assuming you're not (as that would be unusual), but we're not you so we don't know for sure. Always check with Microsoft / your licensing vendor for help with licensing questions.
    – J.D.
    Oct 14, 2022 at 12:28
  • What is your fill factor set to ?
    – Dominique Boucher
    Oct 14, 2022 at 14:54
  • How many filegroups do you have on that database?
    – Ronaldo
    Oct 14, 2022 at 20:41

2 Answers 2

Reset to default
1

If you have tables without a clustered index, rebuild the table. For the rest, just rebuild all indexes and then try to shrink again.

Improve this answer
1
  • This has helped but it might be due to me switching to the Developer edition allowing for the DB to glow first before shrinking.
    – Vaibhav Garg
    Oct 17, 2022 at 6:22
2

One or both of the shrink dialogs are showing incorrect information regarding free space. I recall seeing that before, and the numbers we see in the images you posted supports that.

Anyhow, use shrink file, not the whole database. And do not select "Release unused space" (which doesn't shuffle data, the last-most used page limits the size it can shrink to). Use "Reorganize pages ...".

I also support MBuschi's comment to consider using Evaluation or Developer Edition, assuming this isn't any type of production environment.

Improve this answer
5
  • I have tried shrink file as well and it doesn't do anything
    – Vaibhav Garg
    Oct 14, 2022 at 9:26
  • You also need to select the "Reorganize Pages ..." option. Please read my answer closely.
    – Tibor Karaszi
    Oct 14, 2022 at 12:29
  • Yes I did, I have use this function before but this time no matter the combinations or settings I try, it doesnt work.
    – Vaibhav Garg
    Oct 17, 2022 at 4:29
  • Then perhaps the database really is full, and the SSMS GUI is showing you the incorrect information? Try Right-click the database, Reports, and the Space Usage Report.
    – Tibor Karaszi
    Oct 17, 2022 at 15:33
  • It was fixed once I rebuilt the table.. which doesnt make sense to me but I am not an expert in databases.
    – Vaibhav Garg
    Oct 20, 2022 at 10:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.