2

I am maxed out on a DB in SQL Express 10GB.

I deleted rows of approx 2.5G in size. In report I can see number of rows is much lower. But "Data" is still the same. I am happy about Reserved remaining 10G, but I am unable to insert new rows.

I read that shrinking is not necessary if table will grow again, BUT how do I tell SQL Server that it now has space to use again?

enter image description here

3

Your problem is you were deleting from a heap, i.e. your table has no clustered index, so the space was not released, you could solve this in follow ways as described here: DELETE (Transact-SQL)

When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database. To delete rows in a heap and deallocate pages, use one of the following methods.

  • Specify the TABLOCK hint in

    the DELETE statement. Using the TABLOCK hint causes the delete operation to take an exclusive lock on the table instead of a row or page lock. This allows the pages to be deallocated. For more information about the TABLOCK hint, see Table Hints (Transact-SQL).

  • Use TRUNCATE TABLE if all rows are to be deleted from the table.

  • Create a clustered index on the heap before deleting the rows. You can

    drop the clustered index after the rows are deleted. This method is more time consuming than the previous methods and uses more temporary resources.

  • Note

Empty pages can be removed from a heap at any time by using the ALTER TABLE REBUILD statement.

So what you can do now is rebuild the table into another database as you have no space in current database. And then create a clustered index on it to avoid the same situation in future

0

You could follow below steps:

  1. move the required data(assuming it is very less) to backup table using select into statement.

  2. truncate the main table(disable foreign keys referring to this table,if required).

  3. insert data from backup table to main table.

  4. drop the backup table.

  5. enable foreign keys in main referring tables(if it was disabled).

This way you can delete records fast and space will be released too(because of truncate)

I hope this will work for you.

  • I will try that - just seems odd that space freed-up by deleting rows was NOT available to be used. – ManInMoon Jun 2 '17 at 12:36

Your Answer

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

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