shrinkdb issue

  • I have a local sql 2019 instance on my laptop that I am using to test a server consolidation project.

    annoyingly all of the databases won't fit on any dev environment that I have - I tried to script out the databases (I just need the schema really - I can use Redgate to pump fake data in) - but there are some obstacles that are preventing me from scripting the databases (I have to create part of a database, then create another database, then back to the first one because of the linked server interdependencies that i'm trying to fix) - plus we have stretch tables, scripting a stretch enabled database is a nightmare.

    so  - my plan - restore one database at a time, truncate data where possible, delete where I can and have a tiny database - then move to the next database.

    if I look at the database properties and a specific filegroup, it tells me it is 99% empty(in this case about 170GB free) - but dbcc shrinkfile is doing nothing... then I noticed on the "disk usage by table" report that there are still records in the tables on that filegroup.... im working on this, but why is there a disparity of 170GB between the database properties screen and the table report??

    is this a known issue - ???? - by the way I've tried not using the "truncateonly" option - it's an .ndf file (so not the t-log) and I've even gone as far as creating a new file in the filegroup and migrating from one file to the other.

    still no ability to shrinkfile or see the free space in the file

    MVDBA

  • Have you got the recovery model set to simple?

  • first thing I did - then a checkpoint and dbcc shrinkfile(2,1)  - it's not the log - the log is tiny. it's a .ndf file that is misreporting it's free space and not allowing me to shrink it

    MVDBA

  • Maybe try sp_clean_db_file_free_space on the file and see if that helps.

    Sue

  • On the disk usage report - where is it showing the space being used?  If the tables have LOB data - the space could be allocated for the LOB's but not actually being used.  If the tables are HEAPS - the space would still be allocated to the table even if all rows have been deleted.

    The procedure sp_clean_db_file_free_space will only affect pages where a variable-length column on a table was dropped.  If that is your situation it may help.

    ALTER TABLE ... WITH REBUILD may recover the unused space if that is your issue.

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • surely if I moved all of the data to a NEW file in the filegroup then none of those conditions can occur

    besides, I can see the data in the tables but still the shrinkfile screen shows that it is empty

    MVDBA

  • Have you tried rebuilding the table?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 7 posts - 1 through 7 (of 7 total)

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