Solved

Can't able to get back deleted table space from sql server 2012 database .

Posted on 2014-12-09
4
15 Views
Last Modified: 2016-06-16
Hi ...

I have one sql server 2012 database, and it's size is near about 64 GB

i had deleted unwanted tables which are near about 30 GB size .
but , still my database have 64 GB size.
If i am taking full backup of database then it is 34 GB.
I had restarted server, also rebuilds all tables indexes , but still my database size is 64 GB ,
can any one tell me how can i get back deleted tables size from my database.
It's mdf file size.
i had shrink   Log file size,
0
Comment
Question by:Ashvin Ajagiya
4 Comments
 
LVL 14

Accepted Solution

by:
Vikas Garg earned 250 total points
ID: 40490704
SQL will not free the unused space after deleting data but will insert new data in that place so the size is not reducing without shrinking the MDF file.

You have to shrink the mdf file if you want the free size back but this will lead to fragmentation which you can overcome by rebuilding Indexes.

Hope this would help you.
0
 
LVL 7

Assisted Solution

by:Dung Dinh
Dung Dinh earned 250 total points
ID: 40490829
Hi,

Shrinking file is not recommend idea because it cause fragmentation of data. I recommend that you rebuild indexes and move to new data files.

Thanks,
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40490949
Can you run the following command and post here the result?
sp_helpdb YourDatabaseNameHere

Open in new window

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IN with @variable 5 25
SQL Server 2008 to SQL Server 2016 Replication 8 54
SQL Query 3 48
SQL Server - Check Constraint or NOT NULL? 11 67
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 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

17 Experts available now in Live!

Get 1:1 Help Now