Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 unable to shrink mdf file

Author  Topic 

cottonchopper
Starting Member

8 Posts

Posted - 2010-10-20 : 13:37:26
We have a database SQL Server 2005 that we restored from a backup of our production data for testing. After restoring the database, we removed a lot of the older data since it wasn't needed for testing purposes. That process removed a lot of data, so I tried to shrink the mdf file. The process completes but does not actually change the size of the file.

Currently the size on disk is 154 GB. the log file is just over 1 GB. Looking at the usage the size of the database is 159833 MB, Space available is 130087 MB.

I have rebuilt every index in the database and even tried adding a new file then emptying the original file to get it to finally release the space. We don't anticipate the test database ever growing over at most 75GB so would like to reclaim the 50+GB that is lost now.

Any help would be appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-20 : 13:38:58
Did you run DBCC SHRINKFILE?

Please explain the process you used to perform the shrink.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

cottonchopper
Starting Member

8 Posts

Posted - 2010-10-20 : 15:09:44
I have run DBCC SHRINKFILE(1,NOTRUNCATE), then DBCC SHRINKFILE(1,TRUNCATEONLY). I also looked around the forums here and found a script that is supposed to shrink the database in small increments. I have also tried shrinking the database from the SSMS shrinkfile and shrinkdatabase tasks. I have tried DBCC SHRINKFILE(dbfilename,110000). I am frankly at a loss of other things to try. When I first started, the shrinkfile tasks would take 4+ hours to perform, now they are completing in less than a minute with no errors.

The result set from the shrinkfile is
DBID FILEID CurrentSize MinimumSize UsedPages EstimatedPages
5 1 20249840 256 3600200 3600200
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-20 : 15:31:06
This is going to sound silly, but add 1MB to the current data file and then try to shrink it in a small increment (like by a 100MB). Let me know if that works.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

cottonchopper
Starting Member

8 Posts

Posted - 2010-10-20 : 15:58:01
I tried that. I altered the database and made the file size 1 MB larger, then shrank it to 100MB smaller. It went back down to the original file size. Before 158201MB. Added 1Mb to 158202MB. After shrink 158201MB. I tried a couple of more times and each time tried different sizes. It will only shrink it down to 158201MB. I even tried increasing by 500MB, then shrinking in 100MB increments. But it still stopped at 158201MB.

Other things I have tried is to backup the data (the backup file is only about 40GB, then restoring into another database on a different server. The resulting file size on the new server is 158201MB withover 100GB free space in the database.

Any other ideas? Silly or not, at this point I will try almost anything.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-20 : 16:52:21
The only other thing that I would do is EMPTYFILE to rearrange the data, but it looks like you already tried that.

I'm out of ideas.

The 1MB thing actually fixes it sometimes, so I've added it to my script to shrink. So I shrink in small increments and add 1MB after each shrink to ensure I continue making progress.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-10-21 : 06:34:53
Is the free space in the Database file (MDF) or the TLog file (LDF)?

If the TLog has not been backed up (or Truncated, but not sure if you can do that in SQL2005, certainly can't in SQL2008, but you could in SQL2000) then the TLog file won't shrink. Also, the transaction page at the end of the Log may contain some "open" transactions, adding more (as Tara explains) will cause it to fill that page, then the next page it will use is at the start of file (which was backed up earlier). You then need to backup the Tlog again (which will mark that bit at the end of the Tlog as now-backed-up, and thus can be Shrunk.

Annoyingly complicated

If the Data / MDF file won't shrink then it may be that the free space is in half-used index blocks - i.e. the DELETE has deleted 90% of the data from each index page, but there is still some index entries left scattered through the index. Solution to that is to rebuild all indexes (if you know how force their Fill Factor to 100%, but if not just leave whatever they are set to), and then try the SHRINK on the Data / MDF file. (No need to backup first).

Slight downside is that the Shrink will fragment the indexes (moving nicely optimised / adjacent index pages to whatever-free-space earlier in the file, and that will impact performance (probably not an issue for a DEV database). Solution to that is to Reindex - but that will copy the indexes to the end of the file into clear-space which will re-grow the file. So also annoyingly complicated sorry about that!
Go to Top of Page

cottonchopper
Starting Member

8 Posts

Posted - 2010-10-21 : 10:02:35
The free space that I am trying to reclaim for the disk space is in the MDF. I am not concerned with performance. I had already rebuilt the indexes with their original fill factor, then I did it again with 100% fill factor. I also defraged the indexes (not that I thought it would work). After each above try, I attempted the shrink. Still no good.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-10-21 : 15:45:05
Sorry, re-reading your original post you did say it was the MDF and you had your LDF down to a relatively modest size.

Odd. Fairly sure this has come up before - dunno how much appetite you have for searching this forum, but you might find the answer.

Suggest you check that the minimum database size isn't jacked up at 150-something GB - you won't be able to shrink smaller than whatever that is set to (seems unlikely that would be it though)
Go to Top of Page

cottonchopper
Starting Member

8 Posts

Posted - 2010-10-21 : 16:26:10
I was able to find one other post here that described a similar situation. In that case, the user had truncated a table that contained LOB. He was able to shrink after dropping the clustered index. Not quite the same thing, but the closest I could find. I have tried at this point to drop and recreate all indexes in the system. Still no good. I wonder if there is something with the system tables in the database that could be holding me up.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-21 : 16:29:52
Post the results of this query, so that we can see the files sizes and growth settings for each database file.
Use MyDatabase

-- Show Size, Space Used, Unused Space, Growth Percent or GrowthMB, 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)) ,
GrowthPct =
case when a.status & 0x00100000 = 1048576 then a.growth else null end,
GrowthMB =
convert(int,round(case when a.status & 0x00100000 = 1048576 then null else a.growth /128.000 end,0)),
[DBFileName] = a.name
from
sysfiles a




CODO ERGO SUM
Go to Top of Page

cottonchopper
Starting Member

8 Posts

Posted - 2010-10-21 : 16:41:41
FileSizeMB UsedSpaceMB UnusedSpaceMB GrowthPct GrowthMB DBFileName
158201.88 109675.44 48493.44 NULL 1 GE_Release_dat
691.94 38.58 653.36 10 NULL GE_Release_log

The used space is up a little from all the index rebuilding and defragging I have done recently. (Mental note: Don't use a fill factor of 10 to rebuild all the indexes)

Thank you to everyone who is helping in anyway.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-21 : 17:09:17
If you really re-indexed with a fill factor of 10%, that's bad, and it would explain what is using all your space. Probably should be 80% as a good starting point.

A good way to start would be to reindex with 80% fill factor, and look at the space used again. Then shrink the database, leaving enough free space for your largest table, and then reindex again with and 80% fill factor

The unused space is not that out of line, maybe 30% of total space. That is probably overhead that is needed to be able to run re-indexing of the largest tables.

The growth setting of 1 MB is way too low for a database that size. Probably 500 MB would be a good start for the data file and 100 for the log file, and I would increase the log file to at least 5 GB.



FileSizeMB UsedSpaceMB UnusedSpaceMB GrowthPct GrowthMB DBFileName
158201.88 109675.44 48493.44 NULL 1 GE_Release_dat
691.94 38.58 653.36 10 NULL GE_Release_log


CODO ERGO SUM
Go to Top of Page

cottonchopper
Starting Member

8 Posts

Posted - 2010-10-21 : 17:27:49
I was just playing around trying to get the database to respond to a shrink by causing it to grow when I did the fillfactor of 10. I just reran the reindex with 90 for now and these are my results:
FileSizeMB UsedSpaceMB UnusedSpaceMB GrowthPct GrowthMB DBFileName
158201.88 28513.25 129688.63 NULL 1 GE_Release_dat
691.94 20.41 671.52 10 NULL GE_Release_log

So the database is 100+ GB larger than it needs to be for a dev solution. Regardless of what I try, I cannot get the file size any lower than 158201. When I was running a fillfactor so high earlier. The database grew to 168256, but the shrink task was able to take it back to 158201 but no smaller.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-10-21 : 17:29:30
I think you've misunderstood that bit MVJ as earlier on the O/P said "I had already rebuilt the indexes with their original fill factor, then I did it again with 100% fill factor.".

30% overhead that cannot be shrunk doesn't sound right to me. Sure, that amount of elbow-room is needed in the real world, but this is a Production database that has been restored to DEV, irrelevant data removed, and in our shop too those sort of databases that are on the DEV server "just in case" we need to test something we aim to have Shrunk down to the last spare byte so they aren't wasting our disk space ...
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-21 : 18:06:54
Try shrinking the data file with this script leaving a target free space of around 10000 to 15000 MB. After you shrink the file, run the reindex again.

Shrink DB File by Increment to Target Free Space
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355



CODO ERGO SUM
Go to Top of Page

cottonchopper
Starting Member

8 Posts

Posted - 2010-10-21 : 18:14:15
I actually already found this script on the site. When I run it, if the database is larger than the 158201MB that it seems stuck at, it will shrink to that number and no lower. The script will then run for several hours, if I let it, and it will not shrink to any smaller than 158201MB. There is something at the very end of that part of the file that just will not move.

Just so you know. I ran it with 5000MB free space shrinking at 100MB a pass. I have also tried with varying increments from 1MB to 500MB. But each time the file will stick at 158201MB.
Go to Top of Page

anaylor01
Starting Member

28 Posts

Posted - 2013-06-20 : 12:30:01
Did you ever get this resolved? I have the same problem?
Go to Top of Page

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-20 : 14:04:09
Me too.
It does however seem that any new data been written to the db is being plugged into the holes as my size is not growing that much. But the worrying thing is that I don't actually know when I really will start running into trouble.


Duane.
http://ditchiecubeblog.wordpress.com/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-06-24 : 18:37:05
quote:
Originally posted by ditch

Me too.
It does however seem that any new data been written to the db is being plugged into the holes as my size is not growing that much. But the worrying thing is that I don't actually know when I really will start running into trouble.



Have you rebuilt all indexes?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-27 : 07:32:37
Nope I haven't. But what is working is your approach where you shrink by blocks of 100MB.
That seems to be working great.

My concern with rebuilding indexes is the amount of time that it will interfere with production as some of our tables are quite large (in excess of 3 Billion rows).

Duane.
http://ditchiecubeblog.wordpress.com/
Go to Top of Page
    Next Page

- Advertisement -