Unable to shrink datafiles

Question

Unable to shrink datafiles

Knowledge Hunter

SSCrazy

Points: 2287
More actions
May 27, 2012 at 3:02 am

#257042

Hello,
We have a 600 GB 2008 database in production. Some tables were removed from the database and we have around 240 GB empty space in the database. We need to utilize the space for other database.
The database has two data files of sized 320 and 275 GB respectively.
I tried to shrink the data files with truncate but the space was not released.
Can you please help me out in releasing the space from the database?
Appreciate your quick help.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What command did you run?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 2

truncating or deleting never returns file space to the server; it merely clears the internal tables so SQL can reuse the same space. SQL continues to reserve all that space for itself, as it's perfectly normal to delete data and add new data in it's place.

shrinking a database should be a super rare event...shrinking introduces fragmentation, and other woes; see this article for details:

http://www.sqlskills.com/blogs/paul/post/why-you-should-not-shrink-your-data-files.aspx

if you really want to shrink the database, and you are sure you don't need the freed space for new data taht will be added to your database soon, the easiest way is to use the Task in SSMS:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Knowledge Hunter SSCrazy Points: 2287 More actions · Answer 3

Thanks for your reply.

I am trying to shrink the datafiles using SSMS.

I am trying the same thing that lowel has suggessted. Still it is not working.

The datafiles are showing that there is 55% free space but when I shrink it it remains the same.

Knowledge Hunter SSCrazy Points: 2287 More actions · Answer 4

Please note that we can take the database down for 1 dayif required.

Is there any other way to achieve the solution?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

What exact options did you chose? If you don't tell us that, we can't help you.

Shrinkdatabase/shrinkfile is how you reduce the size of the data file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Knowledge Hunter SSCrazy Points: 2287 More actions · Answer 6

Soory for the inconvenience.

I have followed the following steps:

Tasks-> Shrink -> Files

Select a data file(.NDF) of Primary file group.

Select radio button for :

Reorganize pages before relaesing unused space

Shrink file to

I have provided shrink upto 1 GB more than the actual data size.

Still the size of the datafile continues to be the same.

Please let me know if you need any other details.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Could be there's something still in use, held active at the end of the file. Try shrinking in smaller chunks, that often works better than one large chunk.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Knowledge Hunter SSCrazy Points: 2287 More actions · Answer 8

Thanks Gail for your help.

Is there anyway to take a backup, eliminate the space while restoring the database?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

No.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SequelDBA Ten Centuries Points: 1093 More actions · Answer 10

I restored a copy of Prod (3 TB) on my Dev server, ran a purge routine to remove patient data and now trying to shrink. My initial pass at running the shrink database option in SSMS only reclaimed 1 TB. I then tried using the shrink datafile option on all the data files (Reorganize pages and shrink file to the minimum required) and reclaimed about another 800 GB - several passes at it and also shrinking in smaller chunks. However, I still see about 300 GB of free space.

I've checked for ghost records...None

The source DB has index fragmentation...I'm wondering if an index rebuild of a few of the larger tables will help...Any other ideas?

Thanks...

KU