?
Solved

MS SQL 2005 DBCC SHRINKDATABASE

Posted on 2008-06-19
7
Medium Priority
?
954 Views
Last Modified: 2008-06-19
I deleted a massive amount of data (6GB) from the database last night due to someone backing up massive tables in  a production environment.  I noticed this from the size of the backup increasing by 6GB.  After I truncated and dropped the tables, I performed a DBCC SHRINKDATABASE.  The new backup is 13GB, which is the expected size without the unnecessary deleted tables.  

My question is, why has the size of the backup decreased, but the size of the MDF file is still 19GB?

I performed - DBCC SHRINKDATABASE ('DBNAME',TRUNCATEONLY)

Thanks in advance!

Rob
0
Comment
Question by:transitcenter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21824031
You need to reorg / rebuild the table(s) involved (with a strong preference for rebuild if possible after such large delete(s)).  Otherwise, the freed space is still spread out in the table, and so the total size is not necessarily reduced.
0
 

Author Comment

by:transitcenter
ID: 21824983
But I dropped the tables involved.  How can I rebuild tables that are dropped?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21825316
D'OH, I overlooked that.

Run the shrink w/o specifying a parameter, allowing SQL to move the pages around to use less total space:

DBCC SHRINKDATABASE ( 'DBNAME' )
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:transitcenter
ID: 21825548
Thank you! But one last question...should I provide a percent that the database should save? Also, won't this take a long time on a 13GB database?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 21825879
That will be tricky if you don't know the proper %.  I wouldn't worry about it that much.

Yes, it will take a while, depending on the drive speed.  But it shouldn't be that bad.
0
 

Author Comment

by:transitcenter
ID: 21826010
Thank you!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21826658
Np.

Ironically you can hurt your overall db performance by doing that shrink.  If you have key tables and indexes, you should check them -- DBCC SHOWCONTIG() -- and rebuild them if needed.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

770 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