Solved

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

Posted on 2009-07-12
12
473 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
  • 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
 
LVL 77

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 77

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 77

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

914 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

12 Experts available now in Live!

Get 1:1 Help Now