Sql-server – Releasing space

Try sudo lsof| grep deleted and check if any PostgreSQL process appears. This command looks for files which have been deleted but its file descriptors are still open by any process. Another side effect is that df -h and du -sh / differs. This is because du looks at the file system and sum up the size of all files, and df looks at the physical device.

I have just had an issue with a database which didn't free any space after a DROP table and that was the cause.

The only solution I know is to restart the database. Maybe you can try to send a reload (SIGHUP).

You're going to have to physically move the data - there is no getting around that. But I don't know who came up with the idea that doing so requires creating a clustered index - this just exchanges certain steps for others, and isn't going to require any different amounts of temporary space to accomplish than the method I outline below.

Why not create a second, empty heap on the new filegroup:

CREATE TABLE dbo.whatever_copy
(
  ... cols ...
)
ON [second filegroup];

Then script the indexes, constraints etc. from the original table, copy the data over, remove any constraints that need to be removed (e.g. foreign keys pointing to the original table), then drop the original table, and rename the new one. Finally, add the indexes and constraints to the new table.

 -- script out indexes, constraints etc.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

INSERT dbo.whatever_copy(cols) WITH (TABLOCK)
  SELECT cols FROM dbo.whatever;

 -- remove constraints etc.

DROP TABLE dbo.whatever;

EXEC sp_rename N'dbo.whatever_copy', N'whatever', N'OBJECT';

 -- add constraints etc.

COMMIT TRANSACTION;