ShrinkFile

Question

Post reply

ShrinkFile

Junior Galvão - MVP

SSCertifiable

Points: 6930
More actions
February 7, 2017 at 10:12 pm

#318582

Comments posted to this topic are about the item ShrinkFile

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

SADSAC SSCrazy Points: 2401 More actions · Answer 1

This is a good question, Junior

just a bit confused about the answer

Correct answer:
Only data files
Explanation:
The correct answer is: In both types of files.
TRUNCATEONLY is applicable only to data files. This option is not supported for FILESTREAM filegroup containers. This option 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.
Even if you run the DBCC ShrinkFile command using the TruncateOnly option for a log file, the command will run but will not be performed no operation of release of space occupied by the file physically.

Thomas Franz Hall of Fame Points: 3893 More actions · Answer 2

This is not true.
I just used
DBCC SHRINKFILE (N'mydb_log' , 0, TRUNCATEONLY)
on my 4 GB log file and it is now down to 10 MB (10 MB is the initial size that is / was configured in the database properties).

Edit: I'm using SQL 2014 Dev / Standard / Enterprise

God is real, unless declared integer.

Michael Riemer SSCertifiable Points: 5190 More actions · Answer 3

I'm sure it'll get fixed, but I'm also confused by the answer! I've also done this on a number of occasions to both Data and Log files...

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 4

t.franz - Wednesday, February 8, 2017 12:46 AM
This is not true.
I just used
DBCC SHRINKFILE (N'mydb_log' , 0, TRUNCATEONLY)
on my 4 GB log file and it is now down to 10 MB (10 MB is the initial size that is / was configured in the database properties).
Edit: I'm using SQL 2014 Dev / Standard / Enterprise

The behaviour is not changed since sqlserver 7.0
I'm sure both files are truncated.

BillLudlow SSCertifiable Points: 6553 More actions · Answer 5

BillLudlow

SSCertifiable

Points: 6553

February 8, 2017 at 2:13 am

#1927261

2012 both file types ??

BillLudlow SSCertifiable Points: 6553 More actions · Answer 6

DBCC SHRINKFILE (Transact-SQL)

Other Versions

SQL Server 2012

Updated: August 1, 2016

THIS TOPIC APPLIES TO: yes SQL Server (starting with 2008)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse

Shrinks the size of the specified data or log file for the current database, or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

Whoops, mistake in the question. I was thinking I selected the correct answer, so I was a bit surprised that I was - supposedly - wrong.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Mighty SSCrazy Eights Points: 9063 More actions · Answer 8

Koen Verbeeck - Wednesday, February 8, 2017 2:28 AM
Whoops, mistake in the question. I was thinking I selected the correct answer, so I was a bit surprised that I was - supposedly - wrong.

I think I had the same. I selected the correct answer, submitted it, but it showed that it was wrong, showing the answer that I selected as the correct answer.

BillLudlow SSCertifiable Points: 6553 More actions · Answer 9

Is it that it releases the log space but doesn't change the physical file size? Confused

BillLudlow SSCertifiable Points: 6553 More actions · Answer 10

BillLudlow

SSCertifiable

Points: 6553

February 8, 2017 at 2:52 am

#1927269

Very Confused!

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 11

BillLudlow - Wednesday, February 8, 2017 2:51 AM
Is it that it releases the log space but doesn't change the physical file size? Confused

No, the file size is reduced to minimum size or to the last extent used.
Of course, if run the command twice, maybe, that the size doesn't change.

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 12

Nice try...

Quite interestingly, not even the link to the supporting MSDN article is correct.

Just because you're right doesn't mean everybody else is wrong.

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

The TRUNCATEONLY option is ignored on log files, because there's no concept of shuffling data in a log file. Essentially all shrinks of a log file are TRUNCATEONLY.
The given answer is wrong.

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

tom.w.brannon Hall of Fame Points: 3976 More actions · Answer 14

Mighty - Wednesday, February 8, 2017 2:45 AM
Koen Verbeeck - Wednesday, February 8, 2017 2:28 AM
Whoops, mistake in the question. I was thinking I selected the correct answer, so I was a bit surprised that I was - supposedly - wrong.
I think I had the same. I selected the correct answer, submitted it, but it showed that it was wrong, showing the answer that I selected as the correct answer.

same here