CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
egl74
egl74

Reputation: 35

Measure database free space after a DELETE of a considerable amount of data

We serialize data into an xml, put it into a file and then remove that data from database. We call this process 'Archiving'. The issue is we have to log database free space size before archiving and after archiving. We determine free space with a stored procedure 

ALTER PROCEDURE [dbo].[SP_USED_SPACE]
AS
BEGIN
  declare @reservedpages bigint, @dbsize bigint

  select @reservedpages = sum(a.total_pages)
    from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
        left join sys.internal_tables it on p.object_id = it.object_id

  select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))  from dbo.sysfiles

  SELECT (convert (dec (15,2), @dbsize)) * 8192 / 1048576 as database_size,
     (case when @dbsize >= @reservedpages then
            (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 
            * 8192 / 1048576 else 0 end) as free_space

END

But after archiving that procedure returns nearly or exactly the same value as it did before archiving. Well, I can assure that the deleted data was considerable, say several hundreds of rows in several tables.

Screenshot of what is returned before and after archiving

Screenshot of what is returned by the same procedure just a bit after the first screenshot was made (that is what I expect)

I have already tried:

  1. DBCC UPDATEUSAGE
  2. Send a plain query, without using stored procedure that could be cached
  3. Add Thread.Sleep(5000) in C# before invoking a procedure after archiving. (This works on my local machine but not on the server. Increasing the value between the parentheses doesn't guarantee it would work everywhere in any circumstances).
  4. Perform database shrink after data deletion
  5. Using different ways to determine free space size, i.e. using FILEPROPERTY or sp_spaceused

What could be the reason of such behavior and how do I fix it?

Upvotes: 3

Views: 1368

Answers (1)

CPMunich
CPMunich

Reputation: 725

When a table has no clustered index, a DELETE-Statement does not deallocate the pages. So the space cannot be reused by other objects.

There are options you can use to deallocate pages:

  1. use the TABLOCK-hint in the DELETE Statement
  2. create a clustered index on the table if possible here you can DELETE without the TABLOCK-hint

Look at MSDN online, Topic Locking Behavior https://msdn.microsoft.com/en-US/library/ms189835(v=sql.120).aspx

Upvotes: 2

Related Questions