January 29, 2020 at 5:04 pm
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
January 29, 2020 at 5:21 pm
Have you got the recovery model set to simple?
January 29, 2020 at 5:29 pm
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
January 29, 2020 at 6:33 pm
Maybe try sp_clean_db_file_free_space on the file and see if that helps.
Sue
January 29, 2020 at 8:01 pm
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
January 30, 2020 at 8:37 am
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
January 30, 2020 at 6:24 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply