Reg:Shrink Database not happen

  • vs.satheesh

    SSCrazy

    Points: 2213

    Dear All,

     

    In My production environment , every six month cycle we are replace the database server.whenever replace the database we are truncate all the unwanted transaction data. My problem is after truncate the data i try to shrink the database. But not reduce the MDF file size.i tried all the possibilities like

    1. Change the database recover mode in simple i tried , that time also not working
    2. Index rebuild all the tables
    3. mdf and ldf separately tried
    4. backup file got reduced but mdf file not reduced.

    please give solution for above this problem.

     

     

  • Andrey

    Old Hand

    Points: 366

    questions:

    1. Are there any LOB columns (text, image, varchar(max), etc) in the tables where you truncate (delete?) the data?
    2. do you see and how do you check that free space exists in the database?
    3. provide the output of the following:
       select @@version?

     

     

     

     

  • Erland Sommarskog

    SSC-Insane

    Points: 23720

    The solution is simple. Don't shrink. There is no point in shrinking if you are going to fill up the space again. Also, shrinking the data files introduces a ton of fragmentation.

    You can also read SQL Server MVP Tibor Karaszi's article on the topic:

    https://karaszi.com/why-you-want-to-be-restrictive-with-shrink-of-database-files.

     

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Andrey

    Old Hand

    Points: 366

    vs.satheesh wrote:

    please give solution for above this problem.    

    also, check this out:

    https://dba.stackexchange.com/questions/186871/how-to-free-the-unused-space-for-a-table

Viewing 4 posts - 1 through 4 (of 4 total)

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