Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS SQL 2005 DBCC SHRINKDATABASE

Posted on 2008-06-19
7
Medium Priority
?
957 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 70

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 70

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 70

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 70

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

618 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