March 16, 2016 at 9:13 am
Good Morning Experts,
Shrinking data file is not recommended, it causes fragmentation etc. How about shrinking using TRUNCATEONLY? Will it cause any issues?
March 16, 2016 at 9:21 am
It doesn't cause fragmentation, but it only reduces the file size if the free space is at the end of the file. It's still not something you should be doing on a regular basis.
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
March 16, 2016 at 9:22 am
March 16, 2016 at 9:27 am
If drive is full and it contains only data files, can we consider shrinking using TRUNCATEONLY?
March 16, 2016 at 9:31 am
No, because it's pointless.
If there's space in the data file, SQL will use that and hence won't complain that the drive is full.
If you shrink and remove the free space, the next thing that's going to happen is that the files are going to grow.
Doing what you ask is like suggesting that a 50-litre barrel is too small to hold the rain, and replacing it with a 20-litre.
Databases grow, that's the nature of databases. If you can't fit the data you have on the drive you have, there are two options. Remove data or get more drive space.
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
March 16, 2016 at 9:48 am
GilaMonster (3/16/2016)
No, because it's pointless.If there's space in the data file, SQL will use that and hence won't complain that the drive is full.
If you shrink and remove the free space, the next thing that's going to happen is that the files are going to grow.
Doing what you ask is like suggesting that a 50-litre barrel is too small to hold the rain, and replacing it with a 20-litre.
Databases grow, that's the nature of databases. If you can't fit the data you have on the drive you have, there are two options. Remove data or get more drive space.
Or you can use a magical compression algorithm that allows one to store 3 pieces of information per bit. (hey it might work in some sort of alternative universe)
March 16, 2016 at 10:38 am
Thanks a lot for the advise madam
March 18, 2016 at 12:26 am
This was removed by the editor as SPAM
March 18, 2016 at 2:46 am
jacksonandrew321 (3/18/2016)
TRUNCATEONLY affects both the LOG and the DATA files in SQL Server.
No it doesn't. From the very blog post you referenced:
NOTE: The NOTRUNCATE and TRUNCATEONLY arguments are only applicable to data files, but they don’t affect the log file.
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
March 18, 2016 at 2:53 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply