Solved

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

Posted on 2009-07-12
12
465 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 76

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 76

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 76

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need help with a query. 10 43
SQL Server R2 Stored procedure make faster 7 32
Contained Database Collations 6 20
sql query Help 12 29
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 …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

20 Experts available now in Live!

Get 1:1 Help Now