?
Solved

Cannot shrink datafile in SQL Server database

Posted on 2011-02-13
8
Medium Priority
?
773 Views
Last Modified: 2012-05-11
I am trying to shrink database and having issues, i.e. I ran DBCC SHRINKFILE('data_file', target_size) for a few hours and it finished without any errors, but the size did not change. I ran DBCC SHRINKFILE('data_file', notruncate) and then tried to shrink again with no luck. And I put db into Simple Recovery. It's SQL Server 2005.
Currently allocated space: 421846.81 MB
Available free space: 226521.25 MB (53%)
Shrink file to: Minimum is 195326 MB
Any help appreciated.
0
Comment
Question by:zilll53
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34884209
if the data_file does not shrink, it could be because of:
* data being written at the end of the file(s)
* tables being heaps without a clustered index.

in both cases, you could try to (drop and re) create clustered indexes on the table(s) to see if that helps.
0
 

Author Comment

by:zilll53
ID: 34884426
I thought that NOTRUNCATE moves unused space to the end of the file. I will try to rebuild indexes for all tables though and let you know. Thanks.
0
 

Author Comment

by:zilll53
ID: 34885025
I rebuiled indexes on all tables and tried to shrink database with no luck. The weird part is it shows allocated space 421000, used space: 250000 and yet still can't shrink. I done this so many times and never had any issues.
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
LVL 13

Expert Comment

by:geek_vj
ID: 34885601
Please check if there are any active transactions on the database (like backup etc) which may prevent from shrinking the database effectively.
Also, shrinking will impact the performance of the database, so it is not suggestible. Instead, allocate the data file required size at initial stage itself and keep a maximum limit for it.
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 34886918
Try this First take full backup and then shrink.
0
 

Author Comment

by:zilll53
ID: 34887585
There are no active transactions against this database. I put this db in single user mode and yes I know that this will impact performance though it is a weekend and there's no activity on the server at all.
Not sure what full backup would accomplish, but I did full backup and still can't shrink db.
0
 

Accepted Solution

by:
zilll53 earned 0 total points
ID: 35132519
Sorry,
did not have a chance to get back with a comments, but the issue has been resolved. THe problem was with Ghost Cleanup process, i.e. there was a 2 big heap tables which had a massive amount of Ghost records. So I created a clustered idnexes and dropepd them right away and then was able to perform shrink without any issues.
Basically the ghost porcess has a bug which was confirmed by MS tech guy, i.e. when ghost process does not cleanup ghost records.
Thanks.
0
 

Author Closing Comment

by:zilll53
ID: 35171001
THe reason why I am accepting my own comment is because no other comments gave me the solution.
And the reason why I selected grade lower than 'A' is because MS Support helped me.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based developeā€¦
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recovā€¦
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month7 days, 6 hours left to enroll

592 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