February 2, 2011 at 10:47 am
I launched DB shrinking which runs already more then 20 hours.
DB should get free space of 35 GB. Current DB size is ~80 GB. Free disk space is 129 GB.
DB is not currently accessed by any user.
What would be a reason that such procedure takes so long time?
Alex
February 2, 2011 at 2:07 pm
atsql (2/2/2011)
I launched DB shrinking which runs already more then 20 hours.DB should get free space of 35 GB. Current DB size is ~80 GB. Free disk space is 129 GB.
DB is not currently accessed by any user.
What would be a reason that such procedure takes so long time?
Alex
Why are you even trying to shrink this database? You have plenty of disk space available, and just a little more than what I would normally keep as free in a data file (for 80GB, I would keep !20GB of available space for growth).
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 2, 2011 at 2:27 pm
This is not only one database I need to have on that drive.
February 2, 2011 at 8:10 pm
If you need more disk space, then you need to get more disk space. Shrinking databases to try and manage this is just asking for more troubles.
What happens when you don't have any space left in any of the databases, there is no more space on the drive and they need to grow? Are you prepared to tell the organization that their system is down until you can get more space?
If you continue with this process, do you realize that shrinking database files causes index fragmentation and can cause serious performance issues?
Now, if you are looking to just shrink the file to say 60GB and the system doesn't grow very fast - I can see that. Doesn't sound like it, but I can understand doing so.
There are a lot of things that can block the shrink - if the system is heavily used, other processes are blocking, slow IO subsystem, etc...
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 3, 2011 at 3:50 pm
Database Shrinking is a very resource intensive operation. it will cause fragmentation. then if you try and re-build the indexes, it might use the space in the same database (unless tempdb is specified). this will again increase the file size. And the extra 30GB might be very well due to the index rebuilds that would be happening on the database.
February 3, 2011 at 5:36 pm
I am aware that DB shrinking is resource intensive. This is not a reason to shrink DB ~2 days without success. DB size is not enormous. No other intensive processes are on that server. Unfortunately, I can not get addition space on that server (despite my requests). So I need to free space for other more important DB. I need to identify a cause for such behavior; also 3 or 4 months ago such procedure was not a problem.
February 3, 2011 at 7:15 pm
Instead of shrinking in one shot, try shrinking in small batches.. may be 2Gb at a time or even 1GB...
I've also some across this issue where it takes forever to shrink when u try to shrink the db by 20GB for example...
February 4, 2011 at 9:40 am
Ther is realy not enough information to form an intelligent answer. How do I know your system can properly handle a single DB file of this size without knowing your SQL server configuration. I have seen this be caused just by to users being logged into a server at once and one was running a system drive Defrag while the other was trying to DBCC and Shrink the DB. The SQL server was running with a local system account and Lock Pages in Memory was shut off. The final thing that was causing the huge performance hit on the system was the page file was set to 100MB on a system with 8GB of ram becuase the sysadmin was trying to reduce the amount of page Faults. Then there was the time the clusters on the Disk where set to 1 byte less than the size of a SQL Page File segment. This for some reason required three I/O reads for every page file instead of just one.
In short... Need more input to create ouput.
February 4, 2011 at 11:59 am
"Instead of shrinking in one shot, try shrinking in small batches.. may be 2Gb at a time or even 1GB... "
Thank you for this advise. I did it in chunks of 5GB, and every run took from 30 to 6 minutes. Run time was shorter every time. After 30 GB, I shrink 100% of free space in a matter of seconds.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply