Solved

MS SQL 2005 DBCC SHRINKDATABASE

Posted on 2008-06-19
7
937 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
  • 4
  • 3
7 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Comment Utility
But I dropped the tables involved.  How can I rebuild tables that are dropped?
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:transitcenter
Comment Utility
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:
ScottPletcher earned 125 total points
Comment Utility
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
Comment Utility
Thank you!
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now