January 17, 2014 at 6:14 am
I have a SQL 2008 R2 database that I am trying to shrink. (Recovery model is Simple)
When I run the query below, it shows Used space of 120 GB and Free space of over 220 GB. The same number (220 GB) is under "Space Available" under DB properties.
select
name
, filename
, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
, convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a
I want to reclaim that space of 220 GB to help with my storage management.
I have tried
DBCC SHRINKFILE on both the data and log file
and
DBCC SHRINKDATABASE
but the space is not being release and the DB is still taking up over 300 GB of hard drive space.
What am I missing? Why won't the DB shrink? What else can I try?
January 17, 2014 at 9:15 am
please post the exact commands you used
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 17, 2014 at 9:21 am
For data file: try to shrink by small chunks. For example, it your total size is 340 GB, run dbcc shrinkfile('file_name',339900), and than check space again.
For log file: run dbcc loginfo. If there are some active VLF's at the end, run checkpoint command in simple recovery mode, check loginfo again, and than shrink.
January 17, 2014 at 9:40 am
It would be helpful if you posted the results of this query so we could see where the space is being used
-- Show Size, Space Used, Unused Space, and Name of all database files
select
[FileSizeMB]=
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB]=
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB]=
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName]= a.name,
[DBPhysicalName]= a.[filename]
from
sysfiles a
January 17, 2014 at 10:03 am
did you run updateusage before the spaceused.
How did this database come to have so much freespace? Were variable length columns dropped?
---------------------------------------------------------------------
January 17, 2014 at 10:10 am
Hi,
The shrink commands I used were:
DBCC Shrinkfile(dataFileNameHere,1)
DBCC Shrinkfile(logFileNameHere,1)
DBCC Shrinkdatabase(databaseNameHere)
The result of the query:
FileSizeMBUsedSpaceMBUnusedSpaceMBDBFileName
341463.06120929.25 220533.81 ***data
132.88 7.75 125.13 ***log
The reason so much space became available is because I deleted millions of records from a table containing XML documents. I don't need all that data on this development database.
I will try and shrink the data file in smaller chunks and see if that releases some space
Thanks
January 17, 2014 at 10:22 am
Thats why its not shrinking immediately the pages wont be freed straight away. Ghost cleanup handles that
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 17, 2014 at 11:00 am
Ok, of course... I did not think about the ghost records...
I tried forcing the ghost cleanup process using::
DBCC ForceGhostCleanup;
But it does not seem to have an effect..?
January 17, 2014 at 11:08 am
Casper101 (1/17/2014)
Hi,The shrink commands I used were:
DBCC Shrinkfile(dataFileNameHere,1)
DBCC Shrinkfile(logFileNameHere,1)
DBCC Shrinkdatabase(databaseNameHere)
The result of the query:
FileSizeMBUsedSpaceMBUnusedSpaceMBDBFileName
341463.06120929.25 220533.81 ***data
132.88 7.75 125.13 ***log
The reason so much space became available is because I deleted millions of records from a table containing XML documents. I don't need all that data on this development database.
I will try and shrink the data file in smaller chunks and see if that releases some space
Thanks
Does the table that you deleted millions of rows from have a clustered index?
Did you rebuild or defragment that table?
You can use the script on the link below to shrink the database in smaller chunks.
Shrink DB File by Increment to Target Free Space
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355
After the shrink completes, make sure you rebuild or defragment all the tables in the database.
January 17, 2014 at 11:17 am
Yes, it has a clustered and a few non-clustered indexes.
I did rebuild all indexes on this table.
I will apply your script to my DB..hope it works..
Thanks for you help 🙂
January 17, 2014 at 3:39 pm
Casper101 (1/17/2014)
Yes, it has a clustered and a few non-clustered indexes.I did rebuild all indexes on this table.
I will apply your script to my DB..hope it works..
Thanks for you help 🙂
I may have missed it in the posts above be do understand that shrinking a file or a database will cause the worst index fragmentation possible and you WILL need to at least do a REORGANIZE after the shrink. Also, understand that REORGANIZE will not rebuild the horribly fragmented BTREE. For that, you'll need to do a REBUILD and that will cause freespace of about 110% of your largest index (which will usually be a clustered index on your largest table). You can sometimes work around that either by disabling indexes and then rebuilding them (an offline action, for sure) or by using CREATE INDEX WITH DROP EXISTING which also has some online/offline and other limitations that you need to read about in Books Online. Also be advised that many of these actions are fully logged as you would think of the term "fully logged". Again, see Books Online for index actions and recovery models for what can be "minimally logged".
--Jeff Moden
Change is inevitable... Change for the better is not.
August 21, 2014 at 9:19 am
Hi,
Just an update - I know it is an old post... But I still had no luck in shrinking the data file.
I tried shrinking in smaller chucks but it had no effect.
I even created a new table and populated it with the data, then dropped the old table from which I originally deleted the data from - no luck
Any ideas?
August 21, 2014 at 5:47 pm
I've run into such stubborn files in the past. Normally I "give in" to the following...
1. Run DBCC CheckDB to make sure there's no corruption.
2. Do a FULL backup.
3. Suspend any jobs that do log file backups on the DB. --This is where potential for possible dataloss begins.
4. Set the database the database to SIMPLE recovery model.
5. Run a script to set the database to SINGLE user with "ROLLBACK IMMEDIATE", set it right back to "MULTI-USER", and start the shrink on given file with a target size of "0". It, of course, won't do "0" but it seems that a setting of "1" or some other number doesn't always work (I'm thinking "bug" but won't submit it as a CONNECT item because MS probably won't be able to duplicate the problem). If you have a log file that was poorly constructed and has too many VLFs, now would be a good time to shrink that t0 zero and then regrow it properly.
6. Once the shrink completes, change the DB back to FULL recovery.
7. Do a DIF backup to reestablish the log chain. (Or a full backup if you prefer and have the room).
8. Turn the jobs that do log file backups back on.
9. REORGANIZE all the indexes. It's going to be slow but it won't cause but a bit of growth on the database for sorting. IF you have the Enterprise Edition, you can specify "SORT_IN_TEMPDB = ON" and that'll help even more.
10. Use REBUILD to rebuild only those indexes where a properly organized B-TREE would really help. This will cause some file growth depending on the size of the largest index you rebuild (usually, a clustered index).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply