How long does dbcc shrinkfile take

First, I declare three variables:. The system view sys. In my case, this query:. There are two files in this little database. The first one contains data rows — tables and indexes and other objects; the second one is for the log. The name column is what I need for the FileName parameter, above. The size column is the size of the file in SQL Server pages. In the body of the loop, I first reduce the target size by the factor. In this case that is. That seems small but it makes sense for my 1 TB database.

That is 1GB per shrink operation. DBCC will also produce a result set for each call. It looks like this:. It shows the current size after shrinking , the minimum size this database could be, the current number of used pages and the estimated minimum size after shrinking.

Why are there 8 more pages in the current size than the actual used pages? SQL Server has also reserved one extent for housekeeping. Sign me up. Already have a WordPress. Log in now. Loading Comments Email Required Name Required Website. Post was not sent - check your email addresses! Sorry, your blog cannot share posts by email. Create Date: FROM sys. JOIN sys. AND p. WHERE p. SUM p. Issue , t. It will also output the name of any tables and indexes the session is currently locking.

You may need to run it multiple times to "catch" the relevant info. WHERE req. Shrink a Database File in Specified Increments. Author: Eitan Blumin t: EitanBlumin b: eitanblumin. Creation Date: Last Update: This script uses small intervals to shrink a file in the current database. Change the parameter values below to customize the behavior. Change log:. Leave NULL to disable delay. Leave as NULL to ignore. Use this to prevent overload on the AG.

GOTO Quit;. Must be between an integer 0 and AND type IN 0,1 — data and log files only. AS v val. Skipping execution. THROW ;. BREAK ;. Asked 12 years, 4 months ago. Active 2 years ago. Viewed 82k times. Status, R. Improve this question. Paul Randal 7, 1 1 gold badge 34 34 silver badges 45 45 bronze badges. Add a comment. Active Oldest Votes. Improve this answer. Aaron Alton Aaron Alton 1, 11 11 silver badges 10 10 bronze badges.

Currently checking out how to read data returned from that DMV. Checkout this blog post I wrote yesterday that shows you what I mean and advises how to effect a shrink without actually doing a shrink: Why you should not shrink your data files Hope this helps! Paul Randal Paul Randal 7, 1 1 gold badge 34 34 silver badges 45 45 bronze badges.

It was taking forever to shrink G data file I will read it over and consider using index defrag. Thanks Paul! I am moving data between drives by adding a file to the filegroup on the new drive, emptying the original and dropping it.

Paul, I am observing that shrinking a file to a target size takes a while but completes I see it looking at the file size -but although it SEEMS to be completed succesful, the process of shinking still continues and runs forever.