September 30, 2016 at 8:48 am
Hi Experts,
We have a database. It will grow max around 5 GB but we allocated 10 GB.
Due to large size backup size is also growing large. If we reduce the allocated file size, will there be any issue?
September 30, 2016 at 8:52 am
You can reduce the file size, but backups don't contain empty space, so shrinking is not going to reduce your backup size.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2016 at 10:27 am
Will there any performance impact by reducing the mdf file size?
September 30, 2016 at 10:35 am
Yes, you'll be fragmenting the indexes which will require you to rebuild them all (which will grow the file again)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2016 at 11:00 am
ramana3327 (9/30/2016)
Will there any performance impact by reducing the mdf file size?
Probably not, if the space was never used.
The way to try to shrink with no fragmentation is to use TRUNCATEONLY on the file shrink command:
USE [your_db_name];
DBCC SHRINKFILE ( 1, 5120, TRUNCATEONLY )
--NOTE: Do not shrink the entire db, only the specific file(s) you need to shrink.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 3, 2016 at 7:46 am
Thank you.
Yes. I didn't see any size difference in backup file.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply