?
Solved

Database size not reduced after tables deletion

Posted on 2012-08-26
8
Medium Priority
?
908 Views
Last Modified: 2012-09-04
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
0
Comment
Question by:DB-IT
8 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 38335398
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
 

Author Comment

by:DB-IT
ID: 38335530
Hi angelIII,
  Thanks for your reply. Here's the output


ouput
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:John Claes
ID: 38335599
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
 

Author Comment

by:DB-IT
ID: 38335633
hi poor_beggar,
   The settings as below

Autogrowth : By 1MB, unrestricted growth
Initial size : 411190
0
 
LVL 3

Expert Comment

by:masteripper
ID: 38335731
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1600 total points
ID: 38337817
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
 

Author Closing Comment

by:DB-IT
ID: 38339357
Thank you all for your input. Good day!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question