Shrinking Data File using TRUNCATEONLY

Question

Shrinking Data File using TRUNCATEONLY

coolchaitu

SSChampion

Points: 10332
More actions
March 16, 2016 at 9:13 am

#312116

Good Morning Experts,
Shrinking data file is not recommended, it causes fragmentation etc. How about shrinking using TRUNCATEONLY? Will it cause any issues?
March 18, 2016 at 12:26 am

This was removed by the editor as SPAM
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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

No, that doesn't do the same thing, and it's not recommended[/url], either.

John

Edit - ignore me. I was talking about something different. I wasn't aware of this option for shrinking.

coolchaitu SSChampion Points: 10332 More actions · Answer 3

If drive is full and it contains only data files, can we consider shrinking using TRUNCATEONLY?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ManicStar SSCoach Points: 17992 More actions · Answer 5

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)

coolchaitu SSChampion Points: 10332 More actions · Answer 6

coolchaitu

SSChampion

Points: 10332

March 16, 2016 at 10:38 am

#1865669

Thanks a lot for the advise madam

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass