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?


more ▼

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

avatar image

1.6k 55 59 62

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 voted first

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

avatar image

4.9k 33 39 43

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

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

more ▼

answered Jul 27, 2010 at 07:07 AM

avatar image

ramesh 1
2.2k 66 69 73

(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

avatar image


(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jul 27, 2010 at 06:55 AM

Seen: 1223 times

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

Copyright 2018 Redgate Software. Privacy Policy