ShrinkFile

Question

ShrinkFile

Viewing 7 posts - 16 through 21 (of 21 total)

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

PurpleLady SSCertifiable Points: 7815 More actions · Answer 1

Whoops, I selected both file types so I was a surprised that I was - supposedly - wrong.

timwell SSCertifiable Points: 5315 More actions · Answer 2

GilaMonster - Wednesday, February 8, 2017 5:10 AM
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.

But the question doesn't ask if TRUNCATE ONLY makes any difference. It asks if the file size is changed.

Are you saying the "Correct answer" is wrong which says "only data files" or the explanation which says "both types?"

BillLudlow SSCertifiable Points: 6553 More actions · Answer 3

Create Test Database

Recovery model = FULL

Insert 1,000,000 rows into a table

Physical size
test.mdf 214,208 KB
test_log.ldf 579,008 KB

Database Logical Name File Name                                                                                                                                                       Size MB        Space Used MB Free Space MB
test           test                 C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\test.mdf        209.19              208.25           0.94
test            test_log          C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\test_log.ldf    565.44             309.74         255.70

Truncate the log ...

DBCC SHRINKFILE (N'test_log' , 0, TRUNCATEONLY)

Physical size
test.mdf 214,208 KB
test_log.ldf 1,072 KB

Database Logical Name File Name                                                                                                                                                      Size MB Space Used MB Free Space MB
test          test                 C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\test.mdf       209.19            208.25              0.94
test         test_log          C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\test_log.ldf       1.05                0.37             0.68

So the log file has reduced in size

Roberts your Mothers Brother!

pstanislav SSC Eights! Points: 940 More actions · Answer 4

Disagree with the answer - or agree - depending on where you look in the question write-up.
From MSDN:
"Shrinks the size of the specified data or log file for the current database"...
https://msdn.microsoft.com/en-us/library/ms189493.aspx

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

timwell - Wednesday, February 8, 2017 7:52 AM
GilaMonster - Wednesday, February 8, 2017 5:10 AM
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.
But the question doesn't ask if TRUNCATE ONLY makes any difference. It asks if the file size is changed.
Are you saying the "Correct answer" is wrong which says "only data files" or the explanation which says "both types?"

The given answer 'only data files' is wrong.
The truncateonly option is ignored when shrinking log files, it makes no difference to the behaviour of shrinkfile whether it's there or not.

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

briandevaney SSC Veteran Points: 253 More actions · Answer 6

briandevaney

SSC Veteran

Points: 253

February 9, 2017 at 10:32 am

#1927595

So are we going to get points awarded posthumously?

aprathour.89 Default port Points: 1495 More actions · Answer 7

Wrong Answer. DBCC Shrinkfile applies to both data and log files. Also it changes physical log file size.

-----------------
Aditya Rathour
SQL DBA

Not Everything that is faced can be changed,
but nothing can be changed until it is faced.