asked
DBCC Shrink Database process time ?
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.
Simply by removing the contention/checking that having potentially other users in the database can cause. In SINGLE USER, you are the only connection, so no other workload is using the resources, no locks have to be honoured, and there is no option for anything else to come in and start to affect the operation.
@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).
I found that Shrink Database time can be reduce if we run shrink database in single user mode.
Of course you also have a system that can't be used for its primary purpose during that window.
