Dumb question

[920506]

Hi all,
I have a question. I have a database which is really big. I got into the enterprise manager and right click on the database and select property, I saw the
the size:7000MB and space available is 6700MB. Does that mean when the database initially created, it allocated the storage space is around 7GB and now even I delete some data in the database, it will not shrink the database size, it only makes the available space bigger? Is there any way I shrink the database size without destroy any data?

Thanks!
Betty

[skhanal]

You are right. However 7GB includes both data and log files. To shrink database you have to run

dbcc shrinkdb
or
dbcc shrinkfile

[920506]

I can also change in the enterprise manager directly right, there is data file which has

Filename, location , allocated space etc. I just need to change the allocated space to,say, from 6GB 4 GB, is that OK.
Thank you so much for your quick response.

Betty

[920506]

I exec the following command:

DBCC SHRINKDATABASE (TestDatbase, 50)
I got the following message:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

It seems successful, but when I open another enterprise manager, the data file size is still the same, what's going on?

Betty

[Rawhide]

Create a backup first to create a checkpoint.

Also, for future ease, you may want to consider enabling the autoshrink property on the database. Right click on the database, click Properties, select the Options tab, select autoshrink, click Okay.

[rmiao]

If the db is in full or bulk-logged recovery model, you have to do log backup periodically to truncate committed transactions from log.

[920506]

Hi, all
I just found this information in SQL analyzer(search for "shrinking a database")
------------
You cannot shrink an entire database to be smaller than its original size. Therefore, if a database was created with a size of 10 megabytes (MB) and grew to 100 MB, the smallest the database could be shrunk to, assuming all the data in the database has been deleted, is 10 MB.
------------

Does that mean you cannot shrink a database to a size smaller than the initially allocated size if later on you notice that database doesn't need such a large allocation? then what you can do?

Thanks!

Betty

[rmiao]

Yes, it's in sql books online. But I tried shrink file to size smaller than initial size in enterprise manager and seemed to work.

[Rawhide]

On the same page in BOL:

To shrink a file to a size smaller than its minimum size, use DBCC SHRINKFILE and specify the new size. Executing DBCC SHRINKFILE changes the minimum file size to the new size specified.

[920506]

I issued the command

DBCC shrinkfile(XXX, 4)
I meant 4GB, but actually the unit in command is 4MB. Now I looked at it in the enterprise manager. It says: space allocated is 1.3GB and space available is 700MB. that means I didn't lose any data, right?

Betty

[Rawhide]

Right. DBCC shrinkfile will never truncate data, only space. It attempts to shrink to the value you give it, but will only go as far as it can.