Solved

Cannot shrink datafile in SQL Server database

Posted on 2011-02-13
8
688 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
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…

803 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