6

A SQL Server Express database hit the 10GB limit. I cleared a lot of orders over a certain age. Is this cleared space available for other tables in the same database? I don't need to reduce the database file itself, I don't mind having allocated the 10GB, as I'd rather be out of space for other files than out of space for the database.

Do I need to release empty space in tables to be used for other tables?

0

1 Answer 1

8

SQL Server stores data on 8kb pages, which then get arranged into data structures that represent your table. In cases like this, I find it helpful to think of them as literal pages in a notebook. The notebook is your database data file(s), and it has a bunch of pages. Inside the notebook, it will allocate certain pages to tables and indexes, but also have a bunch of free space, too.

How much space is free inside your database?

You can run a query to see how much free space there is inside your database. I have an open-source DBA Database with a stored procedure that you can use to Check_FileSize which will show used & free space.

You can also see total allocated vs used space at the table level by using a built-in stored procedure EXEC sp_spaceused 'dbo.MyTableName'.

First let's talk indexes and pages

The row data itself is stored either as a heap or the clustered index order. If it's a heap, it's scribbled into the notebook in any random order, wherever there's free space. If the table has a clustered index, then the row data is logically maintained in the order of the clustering key. Since you mention you deleted orders, I'd guess you probably have a clustered index on something like OrderID or OrderDate, either of which puts data roughly in chronological order.

You probably also have non-clustered indexes for important data like AccountID and OrderStatus. These are also stored in index key order, with each index on it's own set of pages in the same notebook.

Back to deleting data

Now, you mentioned you deleted data... You didn't mention how much, but for the sake of my example, let's say you deleted 1/3 of the orders.

If you deleted a bunch of the oldest data, that is like erasing a bunch of contiguous pages at the start of the index, and erasing every row from those pages. This seems good. In the notebook analogy, there are a bunch of contiguous pages that are empty and you have 1/3 of the pages ready for reuse. Right?

On the index by AccountID, there are a bunch of index pointers to deleted orders that you've erased from the middle of pages. Those pages now are 1/3 empty, on average.

On the index by OrderStatus, you've also deleted a bunch of references to deleted rows. But because they were all old, completed orders, they aren't necessarily tidy contiguous cleanup, it's going to be random across the orders with status "compete". This index might have a combination of pages completely empty, and partially empty. The empty pages might be in the "middle" of the index though. The empty pages in the middle aren't going to be able to be immediately reused because they are stuck in the middle of used pages.

Do you need to do anything?

It depends. If you've got free space on pages allocated to the table/indexes and the free space is in the right spot, SQL Server can add data back to that page as necessary.

However, if you really want to reclaim as much space as possible, you'll need to rebuild the indexes on the table. These days, you don't need to do index rebuilds frequently (or even regularly). But in your case, you want to reclaim space from a significant delete operation, so it makes sense to do it as a one-time operation.

ALTER INDEX... REBUILD is an offline operation in Express Edition, so just keep in mind that it's going to lock your table for the duration of the rebuild.

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.