none
Shrinking SQL .MDF & LDF file size not working? RRS feed

  • Question

  • Hi All,

    May I know what's the command to shrink down the .MDF and .LDF files?

    Because when I execute this command below, it does not reduce the disk space used, even after I purge some of the old data content.

    ALTER DATABASE AppDB SET RECOVERY SIMPLE
    GO
    
    DBCC SHRINKFILE (N'AppDB_log' , 0, TRUNCATEONLY)
    GO
    DBCC SHRINKFILE (N'AppDB' , 0, TRUNCATEONLY)
    GO


    Thank you in advance.


    /* Server Support Specialist */

    Wednesday, January 22, 2020 10:18 PM

Answers

  • Hello ,

    Shrinking data file is not recommended as it will lead to performance issue because it will increase the fragmentation, Instead of shrinking log file you can ask your storage team to add some space into drive to avoid space issue.

    you can only try to shrink the log file after taking the log backup,  but if you still want to shrink the data file, you need to first check how much space is available in the data file to shrink , below is the command to check space available in the database (mdf) :

    use databasename

    go

    sp_spaceused 

    one more thing you need to remember that don't shrink database at once you need to shrink in small size. So there might not be free space available in the mdf file hence its not getting shrink , so please check it with sp_spaceused .

    If Log file is not shrinking you need to check the VLF status by DBCC loginfo()

    if the status of vlf is 2 it means the vlf is being used , then you need to take log backup to release the vlf , as mentioned by uri and olaf you also need to check the free space available in log file with DBCC SQLperf(logspace) ,

    If still ts not shrinking you need to check what is running into database so you can check with below query :

    select log_reuse_wait_desc,* from sys.databases

    you need to troubleshoot according to the output of above query.  

      Please mark me as answer if my post helps you.

    Br

    ChetanV


    Thursday, January 23, 2020 7:21 AM
  • Hi Senior System Engineer,

    1.Do you create clustered index for your table in your database? Shrinking file will be effective after you create the clustered index.
    About how to create clustered index you can reference next link:
    ( https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-clustered-indexes?view=sql-server-ver15)
    2. change your code as next to shrink a data file to a specified target size(The data file is shrunk only to the last allocated extent. target_size is ignored if specified with TRUNCATEONLY).

          ALTER DATABASE AppDB SET RECOVERY SIMPLE
           GO
          DBCC SHRINKFILE (N'AppDB_log' , 0)
          GO
          DBCC SHRINKFILE (N'AppDB' ,0)
          GO

    More information about shrink file:
    (https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15)

    Best Regards.
    yuxi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, January 23, 2020 9:07 AM

All replies

  • I would suggest to perform shrinking only on the log file

    You can also use DBCC SQLPERF(LOGSPACE) to make sure that there really is space in the log file to be freed. Put the DB back into Full mode, run the transaction log backup (not just a full backup) and then the shrink.

    , see if it shrinks

    USE dbname

    GO

    DBCC SHRINKFILE (2,100)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Thursday, January 23, 2020 4:58 AM
    Answerer
  • DBCC SHRINKFILE (N'AppDB_log' , 0, TRUNCATEONLY)

    Always refer to BOL first, see DBCC SHRINKFILE (Transact-SQL) => TRUNCATEONLY: "Releases all free space at the file's end...", so if there is just one simple used byte a the very end of the file, nothing will happen/shrink with that command, so your result is not surprising.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, January 23, 2020 6:39 AM
    Moderator
  • Hello ,

    Shrinking data file is not recommended as it will lead to performance issue because it will increase the fragmentation, Instead of shrinking log file you can ask your storage team to add some space into drive to avoid space issue.

    you can only try to shrink the log file after taking the log backup,  but if you still want to shrink the data file, you need to first check how much space is available in the data file to shrink , below is the command to check space available in the database (mdf) :

    use databasename

    go

    sp_spaceused 

    one more thing you need to remember that don't shrink database at once you need to shrink in small size. So there might not be free space available in the mdf file hence its not getting shrink , so please check it with sp_spaceused .

    If Log file is not shrinking you need to check the VLF status by DBCC loginfo()

    if the status of vlf is 2 it means the vlf is being used , then you need to take log backup to release the vlf , as mentioned by uri and olaf you also need to check the free space available in log file with DBCC SQLperf(logspace) ,

    If still ts not shrinking you need to check what is running into database so you can check with below query :

    select log_reuse_wait_desc,* from sys.databases

    you need to troubleshoot according to the output of above query.  

      Please mark me as answer if my post helps you.

    Br

    ChetanV


    Thursday, January 23, 2020 7:21 AM
  • Hi Senior System Engineer,

    1.Do you create clustered index for your table in your database? Shrinking file will be effective after you create the clustered index.
    About how to create clustered index you can reference next link:
    ( https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-clustered-indexes?view=sql-server-ver15)
    2. change your code as next to shrink a data file to a specified target size(The data file is shrunk only to the last allocated extent. target_size is ignored if specified with TRUNCATEONLY).

          ALTER DATABASE AppDB SET RECOVERY SIMPLE
           GO
          DBCC SHRINKFILE (N'AppDB_log' , 0)
          GO
          DBCC SHRINKFILE (N'AppDB' ,0)
          GO

    More information about shrink file:
    (https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15)

    Best Regards.
    yuxi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, January 23, 2020 9:07 AM
  • Do you create clustered index for your table in your database?

    No, I do not know how to do that in SQL Server 2019 Standard.


    /* Server Support Specialist */

    Thursday, January 23, 2020 11:28 AM
  • This physical server is backed up as Crash consistent, so no VSS aware backup that can truncate the SQL log files.

    /* Server Support Specialist */

    Thursday, January 23, 2020 11:28 AM
  • The comments about clustered indexes is irrelevant, so you can disregard that.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, January 23, 2020 11:30 AM
    Moderator
  • Many thanks Chetan for the explanation.

    It is very helpful.


    /* Server Support Specialist */

    Thursday, January 23, 2020 10:41 PM
  • The comments about clustered indexes is irrelevant, so you can disregard that.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    All good now,

    Thanks Tibor.


    /* Server Support Specialist */

    Thursday, January 23, 2020 10:41 PM