3

We just deleted about 45% of the data in a database, and we want to reduce the size of the mdf files (now we have a "delete old data job", so it won't grow that much again in the near future).

My question is:

If I execute more than one DBCC Shrinkfile at the same time, and the files are on different array disks, will the total time of shinking be shorter? Compared against executing DBCC Shrinkfile one file at a time.

This will be executed on a maintenance window, and the idea is to have this maintenance window as short as possible.

Regards

1

Your task is to reduce the size of mdf file, right?

Shrinking a database by dbcc shrinkdatabase is extremely slow. It is single threaded.

There is a faster way to reduce DB size:

  1. Create new filegroup and allocate space for that filegroup as needed (determine how much space is actually used in the old files)
  2. Move all the data to that filegroup and drop the old one

This solution can be multi threaded so it is going to be much faster

  • 2
    What if the old file group is PRIMARY? – Erik Darling Sep 27 '17 at 10:14
  • 2
    @George K Could you please provide the script for doing it? (I mean a script that moves all the data to another FG). It seems to be easier said than done... – sepupic Sep 27 '17 at 10:21
  • @sepupic, probably you are right. But what's the alternative? – George K Sep 27 '17 at 10:35
  • >>>But what's the alternative?<<< Leave the db as it is. Don't shrink. If the data deleted was only a part of some tables, rebuild that tables/indexes during maintenance window – sepupic Sep 27 '17 at 10:38
  • @sepupic, well, this is your answer and a viable option. As to the script - if it is only several tables - we can rebuild them. – George K Sep 27 '17 at 10:42
1

If you're looking for quicker performance, you may want to initially run DBCC SHRINKFILE using the TRUNCATEONLY option. Per BOL:

TRUNCATEONLY

Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. target_size is ignored if specified with TRUNCATEONLY. The TRUNCATEONLY option does not move information in the log, but does remove inactive VLFs from the end of the log file. This option is not supported for FILESTREAM filegroup containers.

If you're lucky, a lot of the space you cleared with your cleanup operation will be located at the end of your data files, and specifying the TRUNCATEONLY option will release that space back to the OS quicker than any other DBCC SHRINKFILE operation because no data movement will be occurring within the datafile itself.

Running this command is as simple as follows:

DBCC SHRINKFILE (DatabaseFile_LogicalName, TRUNCATEONLY)

If this doesn't release a sufficient amount of space back to the OS, you'll then need to evaluate other approaches such as the prior answer provided by George K which is likely in reference to Paul Randall's article, Why you should not shrink your data files.

Finally, another, yet more manual approach, would be to run DBCC SHRINKFILE using the TRUNCATEONLY option a number of times during a number of different maintenance windows. After the initial operation, you would disable automatic file growth on whatever data file(s) you feel need further attention. If lucky, your standard maintenance will shuffle data around enough within the file so that you can run additional DBCC SHRINKFILE operations with the TRUNCATEONLY option in the future to further reduce the file(s) without having to move data around within it. After the file gets to a more desirable size, re-enable auto growth so you don't run into database halting issues as your data file begins to grow over time again.

To disable auto growth on a data file, run the following:

ALTER DATABASE [DatabaseName] MODIFY FILE ( NAME = N'DatabaseFile_LogicalName', FILEGROWTH = 0)

To re-enable auto growth on a data file so that it increases in size by 500MB during each auto growth event, run the following:

ALTER DATABASE [DatabaseName] MODIFY FILE ( NAME = N'DatabaseFile_LogicalName', FILEGROWTH = 500MB)
1

Answering my own question: Yes, it was faster to run several DBCC Shrinkfile, but just because they were on different array disk.

We only had several array disk on production, so I didn´t have an environment to test it.

I actually suggested George K option (create a new file group/move data) but my client didn't want to do that because of cost reasons (another provider would have been involved).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.