December 15, 2016 at 1:37 pm
We recently completed a project to move data to a new system. The old system's production database is still in use, but only to allow our users to enter time clock data.
All old production data was removed from the system over the course of 4 months. We moved all data relevant to a specific location out of the production system into a historical database (with the same DB structure).
So the production DB dropped in used space form over 400GB to 11 GB. Throughout the process (to make room for the growing historical database), I periodically shrank the production DB data file.
However, towards the end of the project, I encountered a problem. I would run the DBCC SHRINKFILE command; it would complete successfully (no errors reported) - but the data file would be the same size.
The data file is currently stuck at 128GB (11GB of which are in use, as I noted earlier).
I have tried:
- running DBCC SHRINKFILE with different target sizes, to remove as little as 1GB, and as much as 50GB. No change.
- Changing the initial file size to something smaller (the changes won't take).
- Changing the initial file size to something larger (forcing the DB file to grow slightly, which has helped in the past when I was getting errors when trying to perform a shrink); the file still won't shrink.
- Tried again after a reboot; no change.
- Tried restoring the latest backup of the database to another server, and shrinking there; no change.
DBCC CHECKDB is regularly run on the database, and no problems have been reported.
The server where this database resides will be decommed shortly. I'll need to move this to another server (at which point, it will also be just historical data). I'll like not to have to waste 100GB on the new server.
REMINDER: I'm trying to shrink the data file, no a transaction log file. And, in the past, I've been able to perform shrinks on this particular data file, with no problems. And, this is a situation where the DB will never grow again, so shrinking it isn't a bad idea from that perspective.
Any suggestions?
R David Francis
December 15, 2016 at 1:56 pm
Hi David
What is the DB recovery setting? Simple or Full? Although production databases are NOT meant to have this setting to simple, since you mention historical databases, this setting may be set as Simple.
The example B in https://msdn.microsoft.com/en-us/library/ms189493.aspx may be the solution.
Thanks
December 16, 2016 at 1:55 am
Weird. I'd think that you should be able. No matter what the spacing of the objects in the file.
Maybe try a reindex that might move things around and then a shrink? Are you using dbcc shrinkfile?
December 16, 2016 at 7:51 am
Recovery setting is FULL (it's still production data for a couple of weeks).
R David Francis
December 16, 2016 at 7:58 am
Unfortunately, example B on the DBCC SHRINKFILE man page won't help much, since that shrinks the transaction log, and my problem is shrinking the data file.
R David Francis
December 16, 2016 at 8:00 am
Steve Jones - SSC Editor (12/16/2016)
Weird. I'd think that you should be able. No matter what the spacing of the objects in the file.Maybe try a reindex that might move things around and then a shrink? Are you using dbcc shrinkfile?
We have a weekly job that reorgs/rebuilds indexes, no change after that's been run.
R David Francis
December 16, 2016 at 8:05 am
ADDITIONIAL INFORMATION:
Thought I'd put this in the initial post, but it's not there.
I watched the DBCC SHRINKFILE process running (via sp_who), and noted this:
The first time I run it (after changes, or after our rebuild/reorg index job), it takes a while to run, and you can see it go through the three steps to free up space: DbccSpaceReclaim, DbccFileCompact, and DbccLobCompact.
Subsequent runs presumably work through those steps, but it's hard to catch without running Profiler, because the command completes in just a few seconds. Form this, I infer that the space reorganization is completing successfully. The file simply isn't shrinking.
R David Francis
December 16, 2016 at 12:44 pm
How did you determine that only 11GB of space is in use? I have seen this kind of issue with one or more tables where LOB data use to exist but was set to NULL. Since the column still exists and the LOB allocation still exists for that table - it then shows up as unusable space and part of the reserved space for that table (but not used space).
You may also have a lot of HEAPs that have unusable space - I would check those tables to see how much space is reserved as opposed to used.
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
December 16, 2016 at 1:19 pm
Jeffrey Williams 3188 (12/16/2016)
How did you determine that only 11GB of space is in use? I have seen this kind of issue with one or more tables where LOB data use to exist but was set to NULL. Since the column still exists and the LOB allocation still exists for that table - it then shows up as unusable space and part of the reserved space for that table (but not used space).You may also have a lot of HEAPs that have unusable space - I would check those tables to see how much space is reserved as opposed to used.
Good thought; no joy, though.
I was basing the space used off the amount reported in the Shrink File window for the database in question.
I double-checked, running a script that sums up reserved, data, index and unused space from sp_spaceused. That agreed, showing:
- 10762864 KB reserved
- 5440040 KB data
- 5126080 KB index
- 196744 KB unused.
Note that this was a final tally. I used the same script to identify two tables that were consuming the most unused space, confirmed that they were heaps, and converted the primary key from non-clustered to clustered (BTW, it's a vendor-supplied DB, and they are several heaps; I've converted the worst offenders to clustered indexes, where I could safely do so). The reserved was originally over 11,000,000 KB, and the original unused was over 400,000 KB.
I tried to shrink the file down 2 MB (from 128,180 to 126,180). As usual, the DBCC SHRINKFILE command completed without error, but the file size was still 128,120.
I suppose it could still be LOB-related, but (assuming I'm correctly following what you're saying, and that what you're saying is correct 🙂 ) leftover reserved LOB data would still be showing up in the total reserved space, and that doesn't show an extra 100+ GB sitting around.
Thanks, though - it was worth checking out!
R David Francis
December 16, 2016 at 1:48 pm
RD Francis (12/16/2016)
Jeffrey Williams 3188 (12/16/2016)
How did you determine that only 11GB of space is in use? I have seen this kind of issue with one or more tables where LOB data use to exist but was set to NULL. Since the column still exists and the LOB allocation still exists for that table - it then shows up as unusable space and part of the reserved space for that table (but not used space).You may also have a lot of HEAPs that have unusable space - I would check those tables to see how much space is reserved as opposed to used.
Good thought; no joy, though.
I was basing the space used off the amount reported in the Shrink File window for the database in question.
I double-checked, running a script that sums up reserved, data, index and unused space from sp_spaceused. That agreed, showing:
- 10762864 KB reserved
- 5440040 KB data
- 5126080 KB index
- 196744 KB unused.
Note that this was a final tally. I used the same script to identify two tables that were consuming the most unused space, confirmed that they were heaps, and converted the primary key from non-clustered to clustered (BTW, it's a vendor-supplied DB, and they are several heaps; I've converted the worst offenders to clustered indexes, where I could safely do so). The reserved was originally over 11,000,000 KB, and the original unused was over 400,000 KB.
I tried to shrink the file down 2 MB (from 128,180 to 126,180). As usual, the DBCC SHRINKFILE command completed without error, but the file size was still 128,120.
I suppose it could still be LOB-related, but (assuming I'm correctly following what you're saying, and that what you're saying is correct 🙂 ) leftover reserved LOB data would still be showing up in the total reserved space, and that doesn't show an extra 100+ GB sitting around.
Thanks, though - it was worth checking out!
It's a given that the following might be a serious PITA, but it might be worth it if the space recovery is critical.
1.) Script the database and ALL of its objects.
2.) Create a database whose intitial size is 11 GB on another server using those scripts.
3.) Build a query or SSIS package that will do nothing but copy data from table to table.
4.) Check space usage on the new database.
Come to think of it, it might be easier to restore the database on the other server, and also script all the tables and indexes and keys and constraints, and then having restored the database, drop all the tables and views on that restored database. Then shrink that restored database, then use the scripts to recreate the tables and views and indexes and keys and constraints, and then copy the data across to it from the original. Either way is probably a PITA, but it might just do the trick...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 16, 2016 at 2:05 pm
RD Francis (12/16/2016)
I double-checked, running a script that sums up reserved, data, index and unused space from sp_spaceused. That agreed, showing:- 10762864 KB reserved
- 5440040 KB data
- 5126080 KB index
- 196744 KB unused.
So those results show just under 11 GB reserved, 5ish GB data, 5ish GB index with only 192 MB unused.
Could you post the results of this query for the database in question, just for another data point?
SELECT file_id,
size_MB=size/128,
free_MB=(size-FILEPROPERTY(name,'SpaceUsed'))/128
FROM sys.database_files
Also, what does sp_spaceused show for unallocated space?
Cheers!
EDIT: Accidentally submitted before finishing the post.
December 16, 2016 at 2:23 pm
sgmunson (12/16/2016)
It's a given that the following might be a serious PITA, but it might be worth it if the space recovery is critical.
1.) Script the database and ALL of its objects.
2.) Create a database whose intitial size is 11 GB on another server using those scripts.
3.) Build a query or SSIS package that will do nothing but copy data from table to table.
4.) Check space usage on the new database.
Come to think of it, it might be easier to restore the database on the other server, and also script all the tables and indexes and keys and constraints, and then having restored the database, drop all the tables and views on that restored database. Then shrink that restored database, then use the scripts to recreate the tables and views and indexes and keys and constraints, and then copy the data across to it from the original. Either way is probably a PITA, but it might just do the trick...
Yeah, that's my emergency fallback plan. We have a script that will set up an empty DB with the correct structure, so that's not a major issue. And I've had to "downgrade" other copies of the DB from newer SQL versions to 2008, which entailed that process.
Still, it bothers me that the file refuses to shrink, and that the problem doesn't crop up in searches (or, is so buried under more common issues that I can't find it). I'd love to know why, y'know?
R David Francis
December 16, 2016 at 2:34 pm
Jacob Wilkins (12/16/2016)
So those results show just under 11 GB reserved, 5ish GB data, 5ish GB index with only 192 MB unused.
Could you post the results of this query for the database in question, just for another data point?
SELECT file_id,
size_MB=size/128,
free_MB=(size-FILEPROPERTY(name,'SpaceUsed'))/128
FROM sys.database_files
Data file shows size_MB = 128,180, free_MB = 117,628.
Also, what does sp_spaceused show for unallocated space?
database_size = 133,219.88 MB, unallocated space = 117,635.59 MB.
R David Francis
December 16, 2016 at 2:50 pm
RD Francis (12/16/2016)
Jacob Wilkins (12/16/2016)
So those results show just under 11 GB reserved, 5ish GB data, 5ish GB index with only 192 MB unused.
Could you post the results of this query for the database in question, just for another data point?
SELECT file_id,
size_MB=size/128,
free_MB=(size-FILEPROPERTY(name,'SpaceUsed'))/128
FROM sys.database_files
Data file shows size_MB = 128,180, free_MB = 117,628.
Also, what does sp_spaceused show for unallocated space?
database_size = 133,219.88 MB, unallocated space = 117,635.59 MB.
Thanks!
I'm sure this will just fall in line with all the other numbers, but since I don't see it posted in the thread yet, when you run DBCC SHRINKFILE what is the output?
Cheers!
December 16, 2016 at 3:07 pm
Jacob Wilkins (12/16/2016)I'm sure this will just fall in line with all the other numbers, but since I don't see it posted in the thread yet, when you run DBCC SHRINKFILE what is the output?
DbId: 5
FileId: 1
CurrentSize: 16407088
MinimumSize: 256000
UsedPages: 1350320
EstimatedPages: 1350320
R David Francis
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply