How to reduce shrink database process time? I know shrink database is not good for database and it increase index fragmentation but in mine case I do so many database deletion and truncation process due to this reason database unallocated size increase approx 40-50 %. I want to shrink database once in a month with given below command but I want to finish it as fast as possible. Please see the given below points that will help to provide solution:- Command: DBCC SHRINKDATABASE (DBNAME, 10) WITH NO_INFOMSGS Database Recovery Mode: SIMPLE Any parallel process with shrink database: NO Database Size: 16 GB Execution Time: 35 Minutes Index: Index fragmentation issue I will handle through index re-organization after shrink database. I did not find any way to reduce this execution time SO please help me if any option available.
It's good that you recognise that shrinking a database is not best practice, but then you describe a process in which your database regularly (monthly) grows and you shrink it. If your normal processing requires that you need a headroom of 40-50%, then ideally you should keep that space allocated to the database, otherwise you are just wasting time and causing the problems you have mentioned! Aside from that, there are no options to run the shrink 'faster' as the process has to physically move all the data from above the target size to space within the target size - the amount of work that this represents will vary each execution time.
@Kev is really right here, but a pragmatic answer to speeding up DBCC Shrink would be to throw more disks at the problem. More spindles = faster data processing. But seriously, don't shrink if the DB is growing that much anyway! You are hurting performance more that way than you will gain from shrinking.
Some of the space you release will be reclaimed when you reorganize/rebuild the indexes without "SORT\_IN\_TEMPDB" option. To rebuild the indexes you'll need almost as much free space as clustered index of the largest table in the database of the tempdb (if you use sort\_in\_tempdb).