locked
Shrink Operation RRS feed

  • Question

  • Could someone please help me understand the different between below 2 shrink operations.

    - shrink database with truncate only option.

    - shrink file command passed on log file.

    DBCC SHRINKDATABASE(N'DB1', 20, TRUNCATEONLY)
    DBCC SHRINKFILE (DB1_log,10)

    Friday, August 30, 2019 2:31 PM

Answers

  • Eg: If a file size is 10GB & currently used space is 6GB. Available free space = 4GB (4%)

    Can we shrink only the unused space(4GB) or is it possible to shrink more than that? What happens when we provide the target percent as more than 4%?

    No, you cannot shrink beyond what is allocated. Nor is it meaningful to shrink down to the last free byte. Shrink introduces fragmentation, so you need to rebuild your indexes once you are done. And rebuilding indexes will need space, since a new copy of the index is built.

    And what's the difference between notruncate & truncateonly option. Notruncate doesn't release the free space to OS & truncateonly releases free space to OS. How about the page movement in these 2 options?

    With TRUNCATEONLY, no data is moved, but only the last couple of pages that are unallocated are released to the file system. With NOTRUNCATE, pages are moved around and tables truncated, but no space is returned to the operating system.

    Be aware of that shrinking a database file is an exceptional operation. Shrinking a database file may make sense if you have taken a copy of production and then deleted large amounts of the data to get a smaller test database. Shrinking the transaction log may make sense if the log exploded due to some accident that you expected not to occur again.

    Since the reasons to shrink the data file and the log file are unrelated, the command DBCC SHRINKDATABASE makes little sense. Always use DBCC SHRINKFILE:


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, September 1, 2019 8:37 PM

All replies

  • Hello Friend,

    Basically the difference is that:
    DBCC SHRINKDATABASE shrinks the entire base (.mdf and .ldf files).

    DBCC SHRINKFILE shrinks the file (data = .mdf or log = .ldf) you choose.

    DBCC SHRINKFILE is faster because you choose whether to be .ldf or .mdf.

    Following is the reference for more details on the 2 commands:

    DBCC SHRINKDATABASE (Transact-SQL)

    DBCC SHRINKFILE (Transact-SQL)


    Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]

    Friday, August 30, 2019 3:12 PM
  • Hi Sar,

    A default database has two files

    MyDb.MDF and MyDb.LDF

    The MDF file is the data file where the primary partition resides. Depending on your needs, you can partition a database into multiple files. This is done so that data (single or multiple tables) can span multiple files that usually are put on separate hard drives to achieve a higher performance.

    If you shrink a database, all files associated with that database will be shrunk.

    If you shrink a file, then only the chosen file will be shrunk.

    You only need to use the Shrink Database command. But it is not usually a good practice to do this and is not a recommended practice either.


    Please click "Mark as Answer" if it solved your issue and/or "Vote as helpful" if it helped. This can be beneficial to other community members reading this thread.

    Friday, August 30, 2019 3:58 PM
  • Hi Jefferson,

    Thanks for the response. Can you help me understand the below query as well.

    Eg: If a file size is 10GB & currently used space is 6GB. Available free space = 4GB (40%)

    Can we shrink only the unused space(4GB) or is it possible to shrink more than that? What happens when we provide the target percent as more than 40%?

    And what's the difference between notruncate & truncateonly option. Notruncate doesn't release the free space to OS & truncateonly releases free space to OS. How about the page movement in these 2 options?


    • Edited by SSG92 Tuesday, September 3, 2019 4:00 AM
    Friday, August 30, 2019 5:05 PM
  • Hi Jefferson,

    Thanks for the response. Can you help me understand the below query as well.

    Eg: If a file size is 10GB & currently used space is 6GB. Available free space = 4GB (4%)

    -In case, you say, 40%. Yes . that's right, free space is left (left over).

    Can we shrink only the unused space(4GB) or is it possible to shrink more than that? What happens when we provide the target percent as more than 4%?

    -To reduce more than that only if you do maintenance, and that's a very large base. An index rebuild (a lot of big Index) can give some space gain.
    When issuing a percentage higher than allowed to release the Sql Server will release to the limit that it can without error.

    And what's the difference between notruncate & truncateonly option. Notruncate doesn't release the free space to OS & truncateonly releases free space to OS. How about the page movement in these 2 options?

    -The pages in this case are already free and are only released to the O.S. or not. Depends on Truncate or Notrucant



    Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]

    Friday, August 30, 2019 7:13 PM
  • Eg: If a file size is 10GB & currently used space is 6GB. Available free space = 4GB (4%)

    Can we shrink only the unused space(4GB) or is it possible to shrink more than that? What happens when we provide the target percent as more than 4%?

    No, you cannot shrink beyond what is allocated. Nor is it meaningful to shrink down to the last free byte. Shrink introduces fragmentation, so you need to rebuild your indexes once you are done. And rebuilding indexes will need space, since a new copy of the index is built.

    And what's the difference between notruncate & truncateonly option. Notruncate doesn't release the free space to OS & truncateonly releases free space to OS. How about the page movement in these 2 options?

    With TRUNCATEONLY, no data is moved, but only the last couple of pages that are unallocated are released to the file system. With NOTRUNCATE, pages are moved around and tables truncated, but no space is returned to the operating system.

    Be aware of that shrinking a database file is an exceptional operation. Shrinking a database file may make sense if you have taken a copy of production and then deleted large amounts of the data to get a smaller test database. Shrinking the transaction log may make sense if the log exploded due to some accident that you expected not to occur again.

    Since the reasons to shrink the data file and the log file are unrelated, the command DBCC SHRINKDATABASE makes little sense. Always use DBCC SHRINKFILE:


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, September 1, 2019 8:37 PM