Solved

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

Posted on 2014-12-09
4
16 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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 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