shrink operation is slow.
-
Shrink is very slow
Hi All,
Recently in one of our sub-prod env , the drive space got filled up.
I tried to pull out free space information using dmvs and see if there is any room for shrink operation.
USE dbname
GO
DBCC SHRINKFILE (N'db_name_dat' , 0, TRUNCATEONLY)
GO
It was not at all releasing space to OS. Later after waiting for 30-40 mins , I killed the Shrink statement.
I wrote a custom sql script which actually try to shrink the mdf file in small chunks (i.e. 50 MB). even then it is taking long time. its been more than a day, it released only 3 GB space so far
and still the shrink job is running.
Note: The SPID was never blocked.
Regards,
Sam
-
Why are you shrinking it? There must be a reason the data file grew that big? Have you ironed out that problem? You're only going to have to do an index rebuild after the fact so shrinking may/maynot be the right approach.
Shrinks do take a long time, it is all down to how much data movement is needed and how fast the IO subsystems can keep up. You will be surprised how slow they can be. Which again goes back to why do you really need to shrink it. This day and age where storage is cheap, there really should be no need to shrink. Appreciate a one off load or something totally out the blue may warrant it, but in general day to day, no.
Also remember TRUNCATEONLY only removes the free space at the END of the file, it will not do any data movement, so if there is only 1MB free between the last page and the end of file, you only get 1MB back.
Whats the size of your biggest index? What ever it is multiple it by 1.5 or 2 times the amount, if that's more than the space your getting back, don't shrink, you're only going to add that space back when you rebuild the indexes.
-
To say the spid was never blocked doesn't mean it's not waiting on resources. It is. It's moving all the pages around. It can't move them while there are shared locks, let alone exclusive locks. This is an inherently slow operation, made slower as your database size increases and as you have users running queries at the same time as you try to shrink.
Wait it out. Don't do that again.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Not sure what you are trying to accomplish here - shrinking isn't going to magically free up space that is allocated and used in the database. The first file has a size of 2545.11GB - with available free space of 161.95GB...which means the file is still using 2,383.16GB.
The second file size is 2473.83 - and only has 90.66GB free...so the file is using 2383.17GB.
There isn't a lot of space available to be recovered by shrinking the file...so all this is going to do is to reorganize the indexes and make the incredibly fragmented. Trying to rebuild any of the indexes will then cause the files to grow or the process to fail if there isn't enough space on the drive.
If you need to recover space in this environment - the first thing you need to do is remove (archive, purge) data that is no longer relevant. With that said...most likely *all* of the data in these files is relevant and required even in a *sub-prod* environment so it may not be possible.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Maybe I'm looking at it the wrong way, but if there is 161 GB of space free within the database, why does it need to grow more? What is the growth settings on these 2 files listed? I hope they are not using the old default of 10% growth.
One other thing to consider is how much usage each database grows by per week or per month. I keep information about each database for 13 months so I can figure out growth rate and plan for how much drive free space I need before it runs out.
-
Thanks everyone for the suggestions. We have decided to purge some unwanted data.
-
Hold the phone on that... consider the following graphic from your original post...
According to that graphic, your first database is 2.5 TERA-Bytes and "only" 6% of it is "free space". How big is your largest index or partition of an index? Same question for the other database.
You also say your "fix" is to "purge some unwanted data". Uh huh... how long is that going to last before you have to do that again and watch your log file explode in the process? And why did all that unwanted data take you by surprise?
Keep some really good notes about your DELETE process coming up. When you're done doing the deletes, realize that you're not actually done yet. Go through your notes to figure out what the "limits" are for your deletes compared to today's date and then write some code that will run every night or week or month (whatever) to do a rolling window of deletes or archives or both. You'll also need to build an index maintenance plan to make up for the all the free but unusable space the deletes are going to have.
And the reason I asked about what's you largest index is because REORGANIZE doesn't do what you think it does and it frequently perpetuates page splits because it removes free space from below whatever the Fill Factor is without adding free space to the critical area between the Fill Factor and 100% page fullness. That means that you should actually be doing REBUILDs and any rebuild over 128 Extents (which is just 8MB) will keep the old index in place until a new index is created. If you don't have room for that, then you'll need to make other plans (and there are a couple of methods for dealing with this).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply