Solved

Cannot shrink datafile in SQL Server database

Posted on 2011-02-13
8
680 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 142

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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need some help wiht :CAST AS Double 11 38
SQL Select - Finding chars in a column 2 48
Sql Permission 6 44
sql Audit table 3 48
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore 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.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 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

22 Experts available now in Live!

Get 1:1 Help Now