Solved

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

Posted on 2009-07-12
12
505 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 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