Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cannot shrink datafile in SQL Server database

Posted on 2011-02-13
8
Medium Priority
?
741 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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