x

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.
more ▼

asked Jul 27, 2010 at 06:55 AM in Default

Leo gravatar image

Leo
1.6k 54 56 58

How did you attempt to shrink the DB?
Jul 27, 2010 at 07:05 AM ThomasRushton ♦
@Thomas, please see my comment below. Thanks.
Jul 27, 2010 at 08:14 AM Leo
(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest
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 .
more ▼

answered Jul 27, 2010 at 07:04 AM

DaniSQL gravatar image

DaniSQL
4.9k 33 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, 2010 at 07:10 AM Leo
What command did you use to shrink the database?
Jul 27, 2010 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, 2010 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, 2010 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, 2010 at 08:45 AM DaniSQL
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Aug 04, 2010 at 03:12 AM

gangadharanbalaji gravatar image

gangadharanbalaji
1

(comments are locked)
10|1200 characters needed characters left
you just want to decrease the size occupied by the db.
more ▼

answered Jul 27, 2010 at 07:07 AM

ramesh 1 gravatar image

ramesh 1
2.2k 63 67 69

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1840
x37

asked: Jul 27, 2010 at 06:55 AM

Seen: 1051 times

Last Updated: Jul 27, 2010 at 06:56 AM