locked
Database shrink takes long time RRS feed

  • Question

  • Hello,

    Recently we've application update and after it our DB grown to over 600 Gb, and inside DB there is over 200Gb of free space.

    I've tried to reclaim some space and I ran shrink DB process in small chunks (several times I've tried to release 1Mb up to 10Mb ) but I've noticed that this process is taking a lot of time. To eliminate any external (application) interference I've also switched DB to "single mode" and even when I was only single user on DB I was unable to free up disk space).

    For your reference I've ran shrink process of 1Mb, and I have waited several hours till process ended.During this process I've checked in Sql  the status of my query (looked for DbccFilesCompact process) and this was in "suspended" state with PAGEIOLATCH_SH wait_type.

    The system wasn't  heavily loaded (no locks, high cpu,ram,IO).

    So I wondered if shrink process is taking so much time, if this could be somehow connected with heavily fragmented indexes in DB ? There are many (over 100) tables where fragmentation exceeded 99% and many more where pragmentation is over 30%

    P.s I know that I should avoid shrink process as much as I can because it will heavily fragment my table indexes but still this will be one time action and after it I will reorganize/rebuild indexes.

    Thanks!



    Mac

    Wednesday, September 28, 2016 12:57 PM

Answers

  • Yes, there are quite a lot of LOB tables inside DB (120 rows)

    Then all I can recommend is patience. It might take weeks. And shrink to a target size that leaves about 50 GB free. You need some space for the reindexing, since shrink introduces fragmentation en masse.

    Or build a database from scripts and copy data over.

    Thursday, September 29, 2016 1:30 PM
  • The most of LOB data in DB is  varchar, nvarchar, xml, only few rows is ntext,text. So how much LOB tables would be considered as too much ? Or the overall amount of LOB isn't a problem only the size of each LOB ?.

    I think the question is how much of your data was in LOB space, how much of that is now free, how much is still in use.  Maybe doing some kind of reorg of the LOB data first would help ... then again maybe not:

    http://www.sqlskills.com/blogs/paul/why-lob-data-makes-shrink-run-slooooowly-t-sql-tuesday-006/

    One more wishlist item for Microsoft to fix in the next release.

    Josh

    Thursday, September 29, 2016 5:12 PM

All replies

  • Hi MacKrk,

    You are doing in Business hour ?

    Shrinking a data file is an enormously resource intensive operation. SQL Server tries to move individual pages and sometimes individual rows from the end of the file to wherever there happens to be room near the beginning of the file. And as it's moving the data, it has to update indexes, and log all the data movement and the index updates. It's LOTS of work.

    If you are on version 2005 or later (please always tell us what version you are using) you can look in the view sys.dm_exec_requests. There is a column called percentage_completed and one called estimated_completion_time. These columns are not populated for every operation, but they are for shrink. You can find the row for your connection during the shrinking, and inspect the values to get an estimate of completion time. If the values are not changing, you'll need to investigate whether the process is blocking something.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e368ea43-ab0a-4d65-9bf0-2f9221192c1e/anyone-know-why-dbcc-shrinkfile-takes-so-long?forum=sqltools


    Please click Mark As Answer if my post helped.

    Wednesday, September 28, 2016 1:02 PM
  • Here is a query you can use to get the status of a shrink -

    
    SELECT 
    	percent_complete, 
    	start_time, 
    	status, 
    	command, 
    	estimated_completion_time, 
    	cpu_time, 
    	total_elapsed_time
    FROM 
    	sys.dm_exec_requests
    WHERE
    	command = 'DbccFilesCompact'
    

    Wednesday, September 28, 2016 1:14 PM
  • Thanks RVSC48 for query. I have this query already.

    I've checked that after I ran shrink process, firstly "DbccSpaceReclaim process started (ended quite quickly), then "DbccFIlesCompact" process started with 99,99982 percentage completion and status of this process is "suspended". Unfortunately "minutes to Completion" showing continuously "0" :).


    Mac

    Wednesday, September 28, 2016 1:41 PM
  • Sorry I forgot to mention that we're using Sql 2012 Standard.

    I will try to follow with guidelines from suggested article.


    Mac

    Wednesday, September 28, 2016 1:47 PM
  • Shrinking is also single threaded so this makes it even more slow, it cannot use parallel threads. Atleast in SQL Server 2012 it is still single threaded.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, September 28, 2016 1:54 PM
  • If its again grows & no purging in place then there is no point of shrinking would benefit.

    we cannot push to shrink to fast, thats an expected behavior.


    Regards, S_NO "_"

    Wednesday, September 28, 2016 2:17 PM
  • If its again grows & no purging in place then there is no point of shrinking would benefit.

    we cannot push to shrink to fast, thats an expected behavior.


    Regards, S_NO "_"

    I guess in this situation OP should shrink because as you can see he just freed 200G free space and he knows that shrinking causes fragmentation but may be space requirement is so much that he needs to do that. I have done this many a times the advise given on net is mostly to know the reader not to make it a habit.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Proposed as answer by Teige Gao Thursday, September 29, 2016 6:56 AM
    Wednesday, September 28, 2016 2:42 PM
  • If its again grows & no purging in place then there is no point of shrinking would benefit.

    we cannot push to shrink to fast, thats an expected behavior.


    Regards, S_NO "_"

    I guess in this situation OP should shrink because as you can see he just freed 200G free space and he knows that shrinking causes fragmentation but may be space requirement is so much that he needs to do that. I have done this many a times the advise given on net is mostly to know the reader not to make it a habit.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Agree! I'm aware of OP concern but leaving.

    Regards, S_NO "_"


    • Edited by S_NO Wednesday, September 28, 2016 2:52 PM add
    Wednesday, September 28, 2016 2:45 PM
  • Any LOB columns in the database? Shrinking LOB data takes forever, I have gathered.

    Wednesday, September 28, 2016 9:51 PM
  • I've tried to reclaim some space and I ran shrink DB process in small chunks (several times I've tried to release 1Mb up to 10Mb ) but I've noticed that this process is taking a lot of time.

    How much time is a lot of time?

    Last time I was involved with something like this was a few years ago so I may not remember it clearly (and my partner was setting up and running the job, I was just waiting for it), but my recollection is that we had a 500gb table we would rebuild, and then shrink the database as well, and this all ran in a mere 14 hours or so.

    ... or it may be this was just the rebuild and it was a separate 14 hours to do the shrink, but whichever I think we managed it overnight, barely.  We did these repeatedly on a dev environment, and then eventually in production.

    Josh

    Wednesday, September 28, 2016 10:22 PM
  • Yes, there are quite a lot of LOB tables inside DB (120 rows)


    Mac

    The most of LOB data in DB is  varchar, nvarchar, xml, only few rows is ntext,text. So how much LOB tables would be considered as too much ? Or the overall amount of LOB isn't a problem only the size of each LOB ?.

    Thursday, September 29, 2016 8:40 AM
  • To be honest I donlt have exact time because time to time I checked progress of this task but I am sure that when I ran ran shrink process to release only 1Mb it took more that 3hrs.

    Mac

    Thursday, September 29, 2016 8:42 AM
  • Yes, there are quite a lot of LOB tables inside DB (120 rows)

    Then all I can recommend is patience. It might take weeks. And shrink to a target size that leaves about 50 GB free. You need some space for the reindexing, since shrink introduces fragmentation en masse.

    Or build a database from scripts and copy data over.

    Thursday, September 29, 2016 1:30 PM
  • The most of LOB data in DB is  varchar, nvarchar, xml, only few rows is ntext,text. So how much LOB tables would be considered as too much ? Or the overall amount of LOB isn't a problem only the size of each LOB ?.

    I think the question is how much of your data was in LOB space, how much of that is now free, how much is still in use.  Maybe doing some kind of reorg of the LOB data first would help ... then again maybe not:

    http://www.sqlskills.com/blogs/paul/why-lob-data-makes-shrink-run-slooooowly-t-sql-tuesday-006/

    One more wishlist item for Microsoft to fix in the next release.

    Josh

    Thursday, September 29, 2016 5:12 PM