0

I have installed SQL Server 2017. I have created a test_db of size 512 mb and am trying to shrink the database using SQL query:

dbcc shrinkfile(N'test_db', truncateonly)

But i do not see the the database being shrinked. In case i use the following SQL query:

dbcc shrinkfile(N'test_db', 1)

the file gets shrinked to 3mb.

Could you please explain the difference in the behavior? Since TRUNCATEONLY releases empty spaces at the end of the file, why is it not able to shrink the file when the data file is empty.

rohan
  • 11
  • 1
  • 5
  • 1
    https://www.sqlpassion.at/archive/2014/08/18/database-shrink-notruncate-vs-truncateonly-2/ – Tim Biegeleisen Feb 13 '19 at 07:12
  • TRUNCATEONLY basically releases all free space at the end of the file to the operating system. So in this case, since there is no data, shouldn't be the space released to OS? – rohan Feb 13 '19 at 07:46
  • That would depend on empty space existing at the end of your data file. It nothing is free, then truncate only won't shrink the size of your DB. Note that truncate only is safer than no truncate, because the former won't mess with your index structures. – Tim Biegeleisen Feb 13 '19 at 07:49
  • But in my case there is no data in the test_db i.e the data file is empty. Still will this hold true? – rohan Feb 13 '19 at 08:47

2 Answers2

2

If you open documentation DBCC SHRINKFILE (Transact-SQL) you'll find

target_size

Is the size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created.

So if you want to reduce the size below the size specified when the file was created, you should specify target_size even for truncateonly:

dbcc shrinkfile(1, 1, truncateonly)
sepupic
  • 8,409
  • 1
  • 9
  • 20
0

SQL Server runs the database shrink operation with the NOTRUNCATE option to gain some free space at the end of your data file. Nothing happens here to the physical size of your file in the Windows file system. This step also introduces the index fragmentation.

SQL Server runs the database shrink operation with the TRUNCATEONLY option, and finally removes the unallocated extents at the end of the data file. It also shrinks down the physical size of the file in the Windows file system.

JERRY
  • 1,165
  • 1
  • 8
  • 22