Post reply

Data not being released after deletion

  • September 22, 2014 at 9:57 pm

    #299612

    Hello I've recently kicked off an archiving/Migration project which inserts rows from a source table on database A to an identical destination table on database B. I take some table size statistics using sp_spaceused before and after the each migration and have found that the amount of space that is being released on the source table is significantly lower then the amount of space that is being claimed on the destination.

    For example, the pre-post migration difference between the 'reserved' sp_spaceused value on the source table is 0.64 gb's, on the destination table it's 3.17 gb's

    I should mention that the table contains blob images that are stored in LOB pages.

    I've attached a table schema script and an excel sheet with the outputs from sp_spaceused

  • September 23, 2014 at 12:26 am

    #1747293

    Maybe the source table is compressed?

    (ps: you didn't add anything in attachment)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • September 23, 2014 at 12:31 am

    #1747294

    No table is not compressed I've just added the attachments 🙂

  • September 24, 2014 at 5:58 pm

    #1748016

    Can anyone help out here?

  • September 24, 2014 at 6:15 pm

    #1748020

    do both databases have same auto-growth settings ?

    is the fill factor of indexes on both tables same ?

  • September 24, 2014 at 6:25 pm

    #1748021

    I've compared the create table scripts and they are exactly the same as well the indexes. My thoughts were that something may be happening around the LOB pages, maybe the image records were not getting deleted or database was not being released

  • September 24, 2014 at 9:55 pm

    #1748028

    Quick thought to rule out some problems, have you checked the logs for error messages or the output of DBCC CHECKDB?

    😎

  • September 24, 2014 at 11:28 pm

    #1748039

    -- Update usage

    EXEC sp_spaceused 'dbo.pr_history', @updateusage = N'TRUE'

    -- Check for ghost records

    select * from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('pr_history'),NULL, NULL, 'DETAILED')

    -- Rebuild the index

    ALTER INDEX [pr_history_PK] ON [dbo].[pr_history] REBUILD

  • September 25, 2014 at 3:58 am

    #1748081

    Do you delete all data on the destination table before you begin the transfer from the source?

    If you always delete all data I have had a similar problem a few weeks ago as you can see here:

    My problem were pages which weren't deallocated after my delete off all data in the table (heap)...

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply