Change MDF Space Allocated

[navshiv]

Hi

We have a database which is rarely used now but taking a lot of disk space (19Gb). I have truncated a lot of tables and ran the Shrink Database option (right click on database) but db is still 19GB. When I go into the properties for the Data File the Space Allocated (MB) says 19494. When I try to change this here it to say 30000 I get the error file 'Cognos Data Warehouse_Data does not exist (I notice no ' at the end of the name). I can see the name to the left of the path reads Cognos Data Warehouse_Data so I cannot see why it tells me it cannot find it. I can also see the .mdf file in the correct path

When I run the command below I get the same error.

ALTER DATABASE Cognos
MODIFY FILE
(NAME = [Cognos Data Warehouse_Data],
SIZE = 3GB)
GO

Anyone have any ideas at all where I am going wrong? Any help would be appreciated.

Cheers

 

[bboffin]

Sounds like the DB really is 19GB but it would probably compress quite well so why not try putting it into a compressed folder? If necessary, detach it first, move it to the new compressed folder and then reattach it.

Since the DB is only used rarely the overheads of using a compressed file should be acceptable.



Bob Boffin

 

[vongrunt]

Check logical file names in sysfiles:

select *
from sysfiles.

Is there any row with name='Cognos Data Warehouse_Data' (exactly)?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[navshiv]

Thanks for that Bob. In the end I exported all the data into a new DB, deleted the old one, re-created it and imported the data back in. Not sure why I didn't do that in the first place.

It has now gone from 19Gb to 3Gb.

 

[mrdenny]

Putting database files into a compressed folder won't do anything for you.

Not to mention that SQL doesn't support compressed volumes

http://support.microsoft.com/kb/231347/en-us.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com