Timeline for Sql Server Express edition - Could not allocate space for object [Table] in database [DB] because the 'PRIMARY' filegroup is full
Current License: CC BY-SA 4.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 13, 2020 at 6:10 | comment | added | Tibor Karaszi | I don't understand what you mean by "it fixed by insert issues". How did you fix it? Is the table a heap or a clustered table? Why did it hold on to the storage if the table is clustered (it shouldn't)? How will you make sure it doesn't happen again? Etc. | |
| Aug 11, 2020 at 11:41 | comment | added | NikitaSerbskiy | check this answer too: dba.stackexchange.com/questions/273179/… | |
| Aug 11, 2020 at 11:38 | comment | added | tomahim | Thanks @TiborKaraszi it fixed by insert issues! not sure how many time I have until it fil again but at least it situation is unblocked | |
| Aug 11, 2020 at 10:03 | comment | added | Tibor Karaszi | Because heaps sucks. 😁 Or, rather, SQL Server sucks at managing heaps. But since your table isn't a heap, then just to reorganize instead of rebuild! And no shrink. | |
| Aug 11, 2020 at 9:52 | comment | added | tomahim | Yeah indeed ALTER TABLE ... REBUILD is trying to allocate a new page and the size limit is already reached so it fails : "Could not allocate a new page for database". I just checked my table is not a heap (edited my question accordingly). @TiborKaraszi after shrinking the database, why do you think I need to create a clustered index ? | |
| Aug 11, 2020 at 9:51 | history | edited | tomahim | CC BY-SA 4.0 |
added 125 characters in body
|
| Aug 11, 2020 at 9:03 | comment | added | Tibor Karaszi | Isn't the REBUILD likely to fail because it copies the old data to a new set of pages and those won't be available because we already reach the size limit? It is of course worth a try. If it is a B-tree that uses lots of space, then REORGANIZE would be sort of in-place. Otherwise, perhaps a shrink is needed after all, to do a type of compaction "in place". And then sort out the situation (probably create a clustered index on the table(s)). | |
| Aug 11, 2020 at 9:00 | comment | added | Tibor Karaszi | Your query only show us the file size, not how full the file are. There's plenty of stuff out there for this, I use my own sp_dbinfo, sp_tableinfo and sp_indexinfo. Might be a starting point to dig into this database: karaszi.com/articlesandutilities | |
| Aug 11, 2020 at 8:59 | comment | added | Denis Rubashkin |
Seems the table is a heap. To deallocate empty data pages from the heap run ALTER TABLE [MY_LARGE_TABLE] REBUILD;. More info on the issue here: DELETE Operation in SQL Server HEAPs
|
|
| Aug 11, 2020 at 8:23 | comment | added | tomahim |
@DenisRubashkin thanks for the insight, though after running DBCC UPDATEUSAGE ([DB_NAME],[MY_LARGE_TABLE]); I still have the error on the insert and row_size_mb stay the same, did I miss something ?
|
|
| Aug 11, 2020 at 8:03 | comment | added | Denis Rubashkin | Run DBCC UPDATEUSAGE as a first step | |
| Aug 11, 2020 at 8:01 | review | First posts | |||
| Aug 11, 2020 at 9:13 | |||||
| Aug 11, 2020 at 7:55 | history | asked | tomahim | CC BY-SA 4.0 |