Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
My database full backup file size is 760 GB and differential would be around 20 GB but the database size is around 2TB. As it is a production database, so to apply only a minimum downtime, i want to run DBCC SHRINKFILE with TRUNCATEONLY before restoring differential backup.
Please advise if i can run the command DBCC SHRINKFILE with TRUNCATEONLY after full backup restored but before differential backup restoration.
Please advise if i can run the command DBCC SHRINKFILE with TRUNCATEONLY between full backup restored and before differential backup restoration.
No, you cannot.
You'll get the error that your database is in the middle of restore, and this is logic, you cannot do anything with database that is restoring.
You should analyse your db free space using sp_spaceused to understand if you can and should do anything with this database BEFORE you take the last backup, may be your db is in the full recovery model and log backups are not made regularly?
Shrinking database has to be done after restore backups (any backup) or before taking the backup.
And you should Restore data-files as they were at the backup.
The following steps might give you what you need and shouldn't impact production:
Run a DBCC SHRINKFILE ('.MDF', TRUNCATEONLY) on the production data file to temporarily release any free space at the end of the file (a TRUNCATEONLY is not IO intensive, and will not fragment indexes)
If the log file is also big, run a DBCC SHRINKFILE on the production log file during a time of low activity, just after taking a log backup.
DBCC SHRINKFILE...command before or after the restore? and Should I run theDBCC SHRINKFILE...at all? – John aka hot2use Jul 24 '18 at 5:35