Solved

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

Posted on 2014-12-09
4
17 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 15

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 48

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL 2008 Divide by zero error encountered. Error 3 45
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 41
convert null in sql server 12 46
MS SQL Server select from Sub Table 14 23
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

856 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