Solved

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

Posted on 2009-07-12
12
513 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 60

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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 79

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 60

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 79

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 60

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 79

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

630 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