Solved

MS SQL 2005 DBCC SHRINKDATABASE

Posted on 2008-06-19
7
946 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 125 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
disk usage reporting tools 27 52
SQL Server 2008 Std. License Key owner or vendor 4 55
How come this XML node is not read? 3 28
error in oracle form 11 24
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

749 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