Database size not reduced after tables deletion

Hi,
   I have a database which is 400GB, I've deleted many tables in it and ran the following query many times but the size still remains pretty much the same.

USE [Tables Backup]
GO

DBCC SHRINKFILE([Tables Backup_Log], 1)
BACKUP LOG [Tables Backup] WITH TRUNCATE_ONLY

DBCC SHRINKDATABASE ([Tables Backup], TRUNCATEONLY);


   Did I miss out some steps? Thanks
DB-ITAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
You're shrinking the log file, which is already very small -- WAY too small in fact for a db that size.

You need to shrink the data file, like so (run them one at a time, NOT altogether in a batch):

DBCC SHRINKFILE([Tables Backup], 400000)
DBCC SHRINKFILE([Tables Backup], 380000)
DBCC SHRINKFILE([Tables Backup], 360000)
...

You need to gradually reduce the size.  As you're running them, they'll start out fast and eventually you'll hit a point where it takes a LONG time to run -- might as well stop then.

You will then to review critical tables to see if they need rebuilt, since shrink pays absolutely no attention to table organization and thus can cause extreme fragmentation in tables.

Be sure to specify SORT_IN_TEMPDB = ON on all rebuild commands, or you'll just grow your main db file back to what it was before.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please run this statement, and report:
SELECT name ,size/128.0 Size ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DB-ITAuthor Commented:
Hi angelIII,
  Thanks for your reply. Here's the output


ouput
0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
As you can see in the output you have a 200GB empty space inside your tables backup database.

if you use the database Shrink and the size doesn't go down.

can you give me the following settings of the database?
properties :  Files : autogrowth :
properties :  Files : Initial Size :
0
 
DB-ITAuthor Commented:
hi poor_beggar,
   The settings as below

Autogrowth : By 1MB, unrestricted growth
Initial size : 411190
0
 
DB-ITAuthor Commented:
Thank you all for your input. Good day!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.