SQL DB Size

I have one production server with couples of Databases. One of the DB size was 200GB because of some tables are containing millions of records.
However, I have transfered biggest tables to another DB and dropped those tables in the Database. Now DB is only left with small size of Tables which are around 40 tables. I have checked the size again and it was the same as before, that won't surprise me.
Anyway, I did run the full backup and execute the following query - 'Backup Log with xxx truncate_only' and Shrink the DB. Also Log file as well.But Size are Still the SAME (I was expecting the size will be less then 100MB)
What I done wrong?? Am I missing something?

Thanks.

asked Jul 27 '10 at 06:55 AM in Default

Leo
1.6k ● 48 ● 54 ● 58

How did you attempt to shrink the DB?

Jul 27 '10 at 07:05 AM ThomasRushton ♦

@Thomas, please see my comment below. Thanks.

Jul 27 '10 at 08:14 AM Leo

(comments are locked)

3 answers:

You may try shrinking the database(not the log) to a specific size. (This is the only scenario that I know of where shrinking data is a way to go) that way SQL server will release the free space to operating system .

answered Jul 27 '10 at 07:04 AM

DaniSQL
4.8k ● 30 ● 35 ● 39

I am absolutely agree with you. But Database is set to Simple at the moment and Log is not much important for that Database. Again, YES, I want data file to decrease the size.

DB Size - 215 GB, Free Space - 155 GB in properties. So data should gone back to 60GB regardless. Is that right?

Jul 27 '10 at 07:10 AM Leo

What command did you use to shrink the database?

Jul 27 '10 at 07:22 AM DaniSQL

try this:

USE [YourDatabase] GO DBCC SHRINKFILE (N'Logical_Name_of_Your_data_file' , 0, TRUNCATEONLY) GO

Do it a couple of times if you have to. Also you can do it using GUI: http://technet.microsoft.com/en-us/library/ms190757.aspx

Jul 27 '10 at 07:27 AM DaniSQL

I am testing on another DB at the moment by setting Shrink File to xxxMB in Shrink File Option. That is working fine and release the allocated free space. Is that good way to do it on Production Database?

Jul 27 '10 at 08:08 AM Leo

Once you tested in your test environment and you make sure you have a good backup, I say its okay to give it a try on production.

Jul 27 '10 at 08:45 AM DaniSQL

(comments are locked)

you just want to decrease the size occupied by the db.

answered Jul 27 '10 at 07:07 AM

ramesh 1
1.7k ● 46 ● 63 ● 68

(comments are locked)

Once you delete a big size objects. then you need to shrink the data file. so that only it can reduce the database size. otherwise data file occupied those objects size.

Thanks & Regards Balaji.G

answered Aug 04 '10 at 03:12 AM