| 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 |
|
|
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 isDBID FILEID CurrentSize MinimumSize UsedPages EstimatedPages5 1 20249840 256 3600200 3600200 |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
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! |
|
|
|
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. |
|
|
|
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) |
|
|
|
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. |
|
|
|
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 filesselect [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.namefrom sysfiles a CODO ERGO SUM |
|
|
|
cottonchopper
Starting Member
8 Posts |
Posted - 2010-10-21 : 16:41:41
|
| FileSizeMB UsedSpaceMB UnusedSpaceMB GrowthPct GrowthMB DBFileName158201.88 109675.44 48493.44 NULL 1 GE_Release_dat691.94 38.58 653.36 10 NULL GE_Release_logThe 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. |
|
|
|
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 factorThe 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 |
|
|
|
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_logSo 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. |
|
|
|
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 ... |
|
|
|
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 Spacehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355CODO ERGO SUM |
|
|
|
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. |
|
|
|
anaylor01
Starting Member
28 Posts |
Posted - 2013-06-20 : 12:30:01
|
| Did you ever get this resolved? I have the same problem? |
|
|
|
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/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
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/ |
|
|
|
Next Page
|
unable to shrink mdf file