Any way to reduce Space Allocated on Database?

[Ovatvvon]

We have a database that has around 52 GB of allocated space, yet they are now only using around 20 GB of that. We'd like to free up the other 30 GB, but DM doesn't want to let us reduce the space allocated, and, of course, Shrinking the DB doesn't do any good as it won't go below that allocated amount of space.

Does anyone know a command for Query Analyzer to defeat this allocation, or some way around it?

-Ovatvvon :-Q

 

[ptheriault]

Did you run the DBCC SHRINKFILE(file_name, size in MB) command? Also, you want to be careful how much you shrink the file. If you have autogrow turned on and the database hits the autogrow threshold it will cause fragmentation.

- Paul
- Database performance looks fine, it must be the Network!

 

[Ovatvvon]

I did the DBCC shrinkfile...it didn't work. :-(


-Ovatvvon :-Q

 

[aolb]

go into Enterprise manager.
highlight the database you want to shrink.
right click >> All tasks >> Shrink db
select the shrink files option at the bottom of the screen
select the file you want to shrink data or log
select shrink action shrink file to and define file size to shrink to

that should do the trick.

Note you might want to truncate the log to get the log file down

 

[Ovatvvon]

aolb,

Thanks, but I did that, along with the other options under the shrink db. None of them work; EM doesn't want to let me shrink it.


-Ovatvvon :-Q

 

[ptheriault]

Is this a production db? If this is a test db then drop it and restore it a from backup. The backup file will should only be the size of the actual data.

- Paul
- Database performance looks fine, it must be the Network!

 

[Ovatvvon]

ptheriault,

This is a QA environment DB. The database backup is the size of the data, however, I'm trying to prevent it from requiring the full 54 GB at the time of restore. If I shrink it, the backups will only try to acquire the smaller space during a restore if needed, rather than acquiring another 30 some GB of unused space. Does this make sense?

(Only 20 GB of data. Don't mind if it had an extra 2 GB of unused space, so that during a restore it will only acquire 22 GB of disk space. But currently any restore will acquire 54 GB of disk space, because there is [logically] 34 GB of unused space that it thinks it needs for the database.)


-Ovatvvon :-Q

 

[ptheriault]

but If you drop the db first then restore the file it will restore only the 20GB correct?

- Paul
- Database performance looks fine, it must be the Network!

 

[Ovatvvon]

I don't believe so; I belive it will try to achieve the entire 54 GB. If it will only grab the 20 GB (or however much the data consumes in that particular backup), then I won't bother with this, but I'm pretty sure it has the entire space marked off logically in the backup so that during a restore, it thinks the db needs to be that big, and requires that amount of space.

-Ovatvvon :-Q

 

[ptheriault]

Not that I didn't want to believe you but I tested that out and you are correct. It will restore the 54GB. : (

Have you tried the truncate free space from end of file option?

- Paul
- Database performance looks fine, it must be the Network!

 

[Ovatvvon]

*laughs* Good deal. (or, not good deal I guess, since that is the case).

Yep, tried that as well. Tried every option available in Enterprise Manager, and also tried from Management Studio, the script that is generated for shrinking the DB. Nothing I found seemed to work.

I even thought perhaps there was some kind of problem with the database, where it might be corrupt somehow, and ran a DBCC CheckDB against it, but it came back clean.

So I'm at a loss...

-Ovatvvon :-Q

 

[ptheriault]

Well you can look at it this way...You won't have any fragmentation in for that file. Sorry I couldn't help.

- Paul
- Database performance looks fine, it must be the Network!

 

[Ovatvvon]

heh heh, yep, that's probably true. No problem...we'll just deal with the extra disk space being consumed I guess.

Thanks for your efforts!

-Ovatvvon :-Q

 

[ptheriault]

I have dug up a few more things to try.
Have you run these?
DBCC SHRINKDATABASE('userDB', 10) --The 10 specifies 10% free space at the end of the file.
OR
DBCC SHRINKDATABASE('userDB', TRUNCATEONLY)

I did also find out that the database may never be shrunk past the minimum size

- Paul
- Database performance looks fine, it must be the Network!

 

[Ovatvvon]

Yep, tried those.

The minimum size is the 54357 or something like that. That's what I was trying to get at in my first post, by saying this is the allocated space, and how can I defeat that. It seems like a very weird number to put as a minimum space requirement (i.e. rather than just 50000 or something). Also find it very weird that there would be a 50 or 55 GB minimum space for a database. So I'm not sure how it ended up being set to that number, but I was hoping there was some way to bypass that.

-Ovatvvon :-Q

 

[ptheriault]

I believe someone would have had to set that minimum size. That is an odd number. Did the db ever have that much data in it? If it really is bothering you, You could create a new db and migrate all the data and objects over. However, at 20GB that could take some time. But you could then drop the old db and rename the new one.
Sorry I can't be of more help.


- Paul
- Database performance looks fine, it must be the Network!

 

[Ovatvvon]

It bothers me in my mind, but it isn't as big of a deal as I'm probably making it seem like. What bothers me more is that you can't ever lower that minimum number, or chop off (truncate) the free space at the end, regardless of that number. To me, it just seems like something that should be feasible, and is confusing why it is impossible.

I could see if it is just a safeguard, and under normal conditions you can't, but there should be a way to override it I would think - particularily for situations like this.

I dunno, maybe I'm just crazy for thinking that...it would be too convenient afterall.

Once again, thanks for trying!

-Ovatvvon :-Q

 

[Ovatvvon]

I think I found out why the database wouldn't shrink for me.

I guess a developer DBA was shrinking the database after they purged the extra data, and that was at the same time I was trying to shrink it. Apparently it just must not work with a double-shrink in place...although I would've exptected a different error message in return.

Either way, the database is shrunk now, so all's good. (Just thought I'd let you all know incase you run into a similar situation in the future.)


-Ovatvvon :-Q