Solved

why does my database not reduce in size when i delete records

Posted on 2009-07-12
12
496 Views
Last Modified: 2012-05-07
Hello,

I have a 9gb mdf file that I need to re-use the schema for (plus around 5% of the data in it). When I restore the backup to a new database and delete all the records I don't need from it, the mdf file remains at 9gb. How can this be?

Thanks,
Dave
0
Comment
Question by:davidhgreen
[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
  • 3
  • +1
12 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24834134
You have deleted the records, but it doesn't automatically shrink the db.  You can use DBCC SHRINKFILE to try to reclaim the unused space.

http://msdn.microsoft.com/en-us/library/ms189493.aspx
0
 
LVL 7

Expert Comment

by:Valleriani
ID: 24834152
Basicly you need to shrink the DB, you can do this if you have MS SQL Server Enterprise/etc by selecting the database via right click, hit all tasks, then 'shrink database'.

In general DB's won't shrink on there own, after deleting records you need to shrink yourself.

Normally in a query its "dbcc shrinkdatabase (database name, 'target percent')"
0
 
LVL 7

Expert Comment

by:Valleriani
ID: 24834155
Ah mwvisa beat me to it :) He is right and provided a good link.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 78

Expert Comment

by:arnold
ID: 24834219
I think under the database properties you can set an option that the DB should autoshrink, but you have to weigh the resources used to shrink/expand the DB when records are deleted/added versus letting the DB keep the space.
Alternatively, if you add/delete records frequently, you may want to store a backup of an empty database or script out the create database/tables instructions.

This way when you need to create a new empty DB, you can just run the create script.
0
 

Author Comment

by:davidhgreen
ID: 24838018
Hello,

I have performed the operation - DBCC SHRINKFILE (alerts_data,100) - but I have left with a db with size of 7.5gb. This is literally a database with schema only (no records).

How can that be?

Cheers,
Dave
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24839281
Try running it again.  Also try shrinking the log.  Since you are starting from scratch, the transaction log.
DBCC SHRINKFILE (alerts_data, 2)
DBCC SHRINKFILE (alerts_log, 2)

Open in new window

0
 
LVL 78

Expert Comment

by:arnold
ID: 24840057
Could you check the properties of the DB to see how much free space it has?
I.e. if its raw size because of the various tables, sp, indexes, etc. it has this size, there is no way to shrink it.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24840568
Take a look at Emmet's article also:
http://www.emmet-gray.com/Articles/SQL_LogMaintenance.htm
0
 

Author Comment

by:davidhgreen
ID: 24840952
hi, it's telling me 104.61 MB

i have 100 tables in the database and maybe 200 sps - this can't total 7.5gb if all these tables are empty.

i have created the full schema/sps from the 'create scripts' function and the total db size is literally 4mb

the long term problem i have is that i need a copy of my schema but with a subset of the data within it. i can do this no problem within my application, but i am left with this massive database.

the shrinkdb is running again now - i had already cleared the transaction log to 1mb without any problems (it was 20gb).
0
 
LVL 78

Expert Comment

by:arnold
ID: 24841152
It sounds out of whack.
What is the raw size of the MDB file?
I do not think that creating 100 tables, along with 200 sps in create scripts could start at 4MB in the DB structure.

Your equating a directive with what the results are. I.e. a single page of instructions can take an hour to complete or it can take a week depending on the requirements set within.

Do you use foreign keys, constraints, index, statistics,triggers?



0
 

Author Comment

by:davidhgreen
ID: 24846898
hey. working some things out here on this - will post update soon.

thanks for your help guys.
0
 

Author Comment

by:davidhgreen
ID: 24857775
hi there,

i have now resolved this - basically there was a table in my db that ASP.NET installed for health monitoring on the back of a web.config change. This has been running on the DB for over a year and I wasn't aware of it. Basically over 1m records - hence no space and massive db.

Have got it down to where it should be now.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

740 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