| Author |
Topic |
|
gogo7188
Starting Member
12 Posts |
 Posted - 2003-10-17 : 07:42:12
|
| Hi all --I have a Database that is approximately 24G. There will be no more data added to it again (ever!) and I would like to release the unused space to the OS. So far this has not worked. The TASKPAD view shows the following:32439.69MB 23731.44MB(USED) 8708.25(UNUSED)Basically I want to reclaim the ~9G of space that will never be used. I have tried DBCC Shrinkfile and DBCC ShrinkDatabase to no avail. Can I NOT reclaim this space? Is it because my Minimum Size is so high? Here is the result I get when I run shrinkfile.DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages ------ ------ ----------- ----------- ----------- -------------- 12 3 4152280 4152280 3037552 3037552(1 row(s) affected)I would appreciate any and ALL comments/help.Thanks, Chad |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-17 : 12:28:47
|
| The MinimumSize value is the minimum size that your database can be shrunk down to. You might consider doing a DBREINDEX so that the database is defragmented. That might help if you have lots of fragmentation in your indexes.Tara |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-10-18 : 04:01:09
|
| Have you tried backing up the transaction log ?Doing that a few times in conjunction with DBCC commands will do it.Damian |
|
|
|
gogo7188
Starting Member
12 Posts |
Posted - 2003-10-20 : 08:19:46
|
quote:
Originally posted by tduggan
The MinimumSize value is the minimum size that your database can be shrunk down to.
So are you saying I am essentially screwed here? Is there anyway to backup and restore this database and change the definition of the files so that I can get my 9 GB of data back?Thanks a bunch for your help Tara. ChadOh, and Damian, I couldn't get your suggestion to work. Thanks for the input though. Every idea is worthy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-20 : 12:13:31
|
| Did you run DBCC DBREINDEX? Which file are you trying to shrink? The MDF or LDF? If LDF, then look at Merkin's post.Tara |
|
|
|
gogo7188
Starting Member
12 Posts |
Posted - 2003-10-22 : 10:04:34
|
quote:
Originally posted by tduggan
Which file are you trying to shrink? The MDF or LDF? Tara
I am trying to shrink the NDF (my secondary data file). My log file size is of little concern. The file structure looks like this:images_sys.mdf :: 100 MBimages_data.ndf :: 32 GBimages_log.ldf :: 500 MBThe DB is loaded with OrthoPhotography for our city and thus will never grow in size. Of the 32 GB only 23 GB are being used currently. I am just trying to figure out if I can ever reclaim the unused space in the data file, or am I just at a loss here?!?Thanks SO MUCH for the help so far but, alas, nothing has worked.Chad |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-22 : 12:10:17
|
| You still haven't answered if you ran DBCC DBREINDEX? It'll defragment your indexes which could help you out.Tara |
|
|
|
gogo7188
Starting Member
12 Posts |
Posted - 2003-10-22 : 15:00:48
|
| I figured it out (sort of)?!? Well, I made it work and that's all that matters right?When I tried to shrink the files using T-SQL I could never get it to go below the minimum size of the data file as I had originally created it. I tried several different methods over several different days to no avail.Here is what worked...if anyone can explain to me how this is different from DBCC Shrinkfile I would love to hear it. Thanks to all for their help. Chad-- from enterprise manager I chose "shrink database" from the context menu-- then shrink database -- shrink files section (FILES button) -- Shrink action (Shrink file to: 23732) ...the actual data sizeYIPEEE...now I have my ~9 GB of space back!!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-22 : 15:15:27
|
| Well the only way to tell is if you use SQL Profiler while you did the work. SQL Profiler would capture the commands that EM used. Then you'd be able to tell what the difference is.Tara |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-10-23 : 08:07:21
|
| This must be on SQL 2000?The choice "shrink files section (FILES button)" and onwards doesn't seem to be available to me in SQL 7. |
|
|
|
gogo7188
Starting Member
12 Posts |
Posted - 2003-10-23 : 08:52:54
|
| Andrew -- yes, this is SQL2000. Chad |
|
|
|
|
Releasing File Space to the OS