How to reduce the space /shrink SQL Server 2008 R2 Data file (MDF) and Log file (LDF)

  • Dear Guru's,

    I have read that using the DBCC ShrinkFile is not the best practice but our data and log files keep growing.

    Its in Full recovery mode and we do daily full backup and 15 minute transaction log files backup using the maintenance plan but still its not reducing 
    Below are the sizes of Log and Data files currently in one of the databases.

    Log file ( FileSize in MB 31189.94     -- UsedSpace MB ( 19.75 ) and FreeSpaceMB (31170.19)
    Data file ( FileSize in MB 64286.13    -- UsedSpace MB ( 32373.06    ) and FreeSpaceMB (31913.06)

    Their is lot of bulk load kind of activity going on daily where it inserts and deletes lot of data from these tables. I tried to add the disk space but its just eating up whole disk.

    What's the best way to restore this free space to OS and what are the other options and good practices

    Thanks
    Sri

  • srik18 - Sunday, August 20, 2017 6:24 PM

    Dear Guru's,

    I have read that using the DBCC ShrinkFile is not the best practice but our data and log files keep growing.

    Its in Full recovery mode and we do daily full backup and 15 minute transaction log files backup using the maintenance plan but still its not reducing 
    Below are the sizes of Log and Data files currently in one of the databases.

    Log file ( FileSize in MB 31189.94     -- UsedSpace MB ( 19.75 ) and FreeSpaceMB (31170.19)
    Data file ( FileSize in MB 64286.13    -- UsedSpace MB ( 32373.06    ) and FreeSpaceMB (31913.06)

    Their is lot of bulk load kind of activity going on daily where it inserts and deletes lot of data from these tables. I tried to add the disk space but its just eating up whole disk.

    What's the best way to restore this free space to OS and what are the other options and good practices

    Thanks
    Sri

    It's ok to shrink the log file IF it normally doesn't need so much room.  If you don't know how much room it actually needs, shrink it to 0 and then immediately regrow it to something smaller than the original.  Make sure that the growth is in mega-bytes rather than %.  For the size of your database, I'd recommend, as a bit of a swag, to regrow it to 2,000 MB (2GB) with a growth of 500MB or 1000MB and see how things play out.

    You say you "tried to add the disk space but its just eating up whole disk."  How big a disk did you try adding?  If it ate the face off an additional disk, I'd say that you have some code somewhere that has an accidental many-to-many join in it, which is also known as a Cartesian Product and, yes, it will eat whatever it can, although TempDB is normally the victim there.

    Soooooo.... before you shrink the log file, Google for code and the necessary alert to monitor what is causing your log file to grow.

    Shifting gears to the MDF file, are you also using maintenance plans to do index rebuilds/reorganizes?  Either way, what is the size of your largest table including all the indexes?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • srik18 - Sunday, August 20, 2017 6:24 PM

    Dear Guru's,

    I have read that using the DBCC ShrinkFile is not the best practice but our data and log files keep growing.

    Its in Full recovery mode and we do daily full backup and 15 minute transaction log files backup using the maintenance plan but still its not reducing 
    Below are the sizes of Log and Data files currently in one of the databases.

    Log file ( FileSize in MB 31189.94     -- UsedSpace MB ( 19.75 ) and FreeSpaceMB (31170.19)
    Data file ( FileSize in MB 64286.13    -- UsedSpace MB ( 32373.06    ) and FreeSpaceMB (31913.06)

    Their is lot of bulk load kind of activity going on daily where it inserts and deletes lot of data from these tables. I tried to add the disk space but its just eating up whole disk.

    What's the best way to restore this free space to OS and what are the other options and good practices

    Thanks
    Sri

    Try Testing trace flag 610.

    @JayMunnangi

  • JakDBA - Sunday, August 20, 2017 10:00 PM

    srik18 - Sunday, August 20, 2017 6:24 PM

    Dear Guru's,

    I have read that using the DBCC ShrinkFile is not the best practice but our data and log files keep growing.

    Its in Full recovery mode and we do daily full backup and 15 minute transaction log files backup using the maintenance plan but still its not reducing 
    Below are the sizes of Log and Data files currently in one of the databases.

    Log file ( FileSize in MB 31189.94     -- UsedSpace MB ( 19.75 ) and FreeSpaceMB (31170.19)
    Data file ( FileSize in MB 64286.13    -- UsedSpace MB ( 32373.06    ) and FreeSpaceMB (31913.06)

    Their is lot of bulk load kind of activity going on daily where it inserts and deletes lot of data from these tables. I tried to add the disk space but its just eating up whole disk.

    What's the best way to restore this free space to OS and what are the other options and good practices

    Thanks
    Sri

    Try Testing trace flag 610.

    What you really mean is to try "Minimal Logging" for bulk loads.  That's a great idea to prevent the expansion of the log file in the future but the op wants to know how to reduce the current size of the log file, which I explained, and how to reduce the size of the MDF file without the massive fragmentation that occurs during such a shrink which is followed by massive regrowth when rebuilding the indexes, all of which are relatively incredibly slow.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • JakDBA - Sunday, August 20, 2017 10:00 PM

    Try Testing trace flag 610.

    Trace flag 610 is useless unless he also switches to bulk-logged or simple recovery modes, with their different effects on ability to recover in a disaster.

    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
  • srik18 - Sunday, August 20, 2017 6:24 PM

    Log file ( FileSize in MB 31189.94     -- UsedSpace MB ( 19.75 ) and FreeSpaceMB (31170.19)
    Data file ( FileSize in MB 64286.13    -- UsedSpace MB ( 32373.06    ) and FreeSpaceMB (31913.06)

    Their is lot of bulk load kind of activity going on daily where it inserts and deletes lot of data from these tables. I tried to add the disk space but its just eating up whole disk.

    During those loads, how much free space does the log file have?
    How often are you doing log backups?

    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
  • Jeff Moden - Sunday, August 20, 2017 9:34 PM

    srik18 - Sunday, August 20, 2017 6:24 PM

    Dear Guru's,

    I have read that using the DBCC ShrinkFile is not the best practice but our data and log files keep growing.

    Its in Full recovery mode and we do daily full backup and 15 minute transaction log files backup using the maintenance plan but still its not reducing 
    Below are the sizes of Log and Data files currently in one of the databases.

    Log file ( FileSize in MB 31189.94     -- UsedSpace MB ( 19.75 ) and FreeSpaceMB (31170.19)
    Data file ( FileSize in MB 64286.13    -- UsedSpace MB ( 32373.06    ) and FreeSpaceMB (31913.06)

    Their is lot of bulk load kind of activity going on daily where it inserts and deletes lot of data from these tables. I tried to add the disk space but its just eating up whole disk.

    What's the best way to restore this free space to OS and what are the other options and good practices

    Thanks
    Sri

    It's ok to shrink the log file IF it normally doesn't need so much room.  If you don't know how much room it actually needs, shrink it to 0 and then immediately regrow it to something smaller than the original.  Make sure that the growth is in mega-bytes rather than %.  For the size of your database, I'd recommend, as a bit of a swag, to regrow it to 2,000 MB (2GB) with a growth of 500MB or 1000MB and see how things play out.

    You say you "tried to add the disk space but its just eating up whole disk."  How big a disk did you try adding?  If it ate the face off an additional disk, I'd say that you have some code somewhere that has an accidental many-to-many join in it, which is also known as a Cartesian Product and, yes, it will eat whatever it can, although TempDB is normally the victim there.

    Soooooo.... before you shrink the log file, Google for code and the necessary alert to monitor what is causing your log file to grow.

    Shifting gears to the MDF file, are you also using maintenance plans to do index rebuilds/reorganizes?  Either way, what is the size of your largest table including all the indexes?

    Jeff,
    Yes the settings are in MB for log file its set at 500 MB and for the data 1024 MB . I do monitor the disk place and get the alerts. For D Drive so far added 100 GB and E its at 200 GB. I dont see much growth of TempDB. I am in process of upgrading the DB's to 2014 i hope it gets better. I use Ola's maintenance scripts. Please find below the numbers. Also i cant change the recovery models 

    ## Aug 20 2017 7:22AM ## D:7GB;E:102GB
    ## Aug 19 2017 7:22AM ## D:23GB;E:112GB
    ## Aug 18 2017 7:22AM ## D:23GB;E:113GB

    RowCounts    TotalSpaceMB    UsedSpaceMB    UnusedSpaceMB
    3,911,943    26726    22363    4363
    2,300,072    3077    3073    3
    2,064,830    1008    1005    2
    923,513    572    571    0
    3,772,469    428    428    0
    1,187,280    330    330    0
    2,300,206    176    175    1
    59,267    28    27    0

    Thanks
    Sri

  • GilaMonster - Monday, August 21, 2017 12:37 AM

    srik18 - Sunday, August 20, 2017 6:24 PM

    Log file ( FileSize in MB 31189.94     -- UsedSpace MB ( 19.75 ) and FreeSpaceMB (31170.19)
    Data file ( FileSize in MB 64286.13    -- UsedSpace MB ( 32373.06    ) and FreeSpaceMB (31913.06)

    Their is lot of bulk load kind of activity going on daily where it inserts and deletes lot of data from these tables. I tried to add the disk space but its just eating up whole disk.

    During those loads, how much free space does the log file have?
    How often are you doing log backups?

    Geff,

    Logs are being backed up every 15 minutes. I need to check the % free when bulk load is happening. This is being done by 3rd party application which i dont have control

    Thanks
    Sri

  • You also need to verify that the log is in status to be shrunk, like so:

    SELECT log_reuse_wait_desc, *
    FROM sys.databases
    WHERE name = '<your_db_name>'

    If the result is 'NOTHING', then carry on as before, the issue is as already stated.  But if it's something else, you'll need to look into that reason.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • srik18 - Monday, August 21, 2017 5:58 AM

    Jeff Moden - Sunday, August 20, 2017 9:34 PM

    srik18 - Sunday, August 20, 2017 6:24 PM

    Dear Guru's,

    I have read that using the DBCC ShrinkFile is not the best practice but our data and log files keep growing.

    Its in Full recovery mode and we do daily full backup and 15 minute transaction log files backup using the maintenance plan but still its not reducing 
    Below are the sizes of Log and Data files currently in one of the databases.

    Log file ( FileSize in MB 31189.94     -- UsedSpace MB ( 19.75 ) and FreeSpaceMB (31170.19)
    Data file ( FileSize in MB 64286.13    -- UsedSpace MB ( 32373.06    ) and FreeSpaceMB (31913.06)

    Their is lot of bulk load kind of activity going on daily where it inserts and deletes lot of data from these tables. I tried to add the disk space but its just eating up whole disk.

    What's the best way to restore this free space to OS and what are the other options and good practices

    Thanks
    Sri

    It's ok to shrink the log file IF it normally doesn't need so much room.  If you don't know how much room it actually needs, shrink it to 0 and then immediately regrow it to something smaller than the original.  Make sure that the growth is in mega-bytes rather than %.  For the size of your database, I'd recommend, as a bit of a swag, to regrow it to 2,000 MB (2GB) with a growth of 500MB or 1000MB and see how things play out.

    You say you "tried to add the disk space but its just eating up whole disk."  How big a disk did you try adding?  If it ate the face off an additional disk, I'd say that you have some code somewhere that has an accidental many-to-many join in it, which is also known as a Cartesian Product and, yes, it will eat whatever it can, although TempDB is normally the victim there.

    Soooooo.... before you shrink the log file, Google for code and the necessary alert to monitor what is causing your log file to grow.

    Shifting gears to the MDF file, are you also using maintenance plans to do index rebuilds/reorganizes?  Either way, what is the size of your largest table including all the indexes?

    Jeff,
    Yes the settings are in MB for log file its set at 500 MB and for the data 1024 MB . I do monitor the disk place and get the alerts. For D Drive so far added 100 GB and E its at 200 GB. I dont see much growth of TempDB. I am in process of upgrading the DB's to 2014 i hope it gets better. I use Ola's maintenance scripts. Please find below the numbers. Also i cant change the recovery models 

    ## Aug 20 2017 7:22AM ## D:7GB;E:102GB
    ## Aug 19 2017 7:22AM ## D:23GB;E:112GB
    ## Aug 18 2017 7:22AM ## D:23GB;E:113GB

    RowCounts    TotalSpaceMB    UsedSpaceMB    UnusedSpaceMB
    3,911,943    26726    22363    4363
    2,300,072    3077    3073    3
    2,064,830    1008    1005    2
    923,513    572    571    0
    3,772,469    428    428    0
    1,187,280    330    330    0
    2,300,206    176    175    1
    59,267    28    27    0

    Thanks
    Sri

    Thanks for taking the time, Sri. 

    Your largest table may explain the size of the log file if the Clustered Index is being rebuilt or reorganized on a regular basis.

    On that note, I'll tell you that I've not defragged any indexes on any of my production boxes since the 17 of January, 2016 (more than a year and a half ago) and performance got markedly better in the first three months and hasn't gotten any worse since then.  You should try the same experiment yourself once we get the current problems cleaned up a bit.  The key is to rebuild stats that need it.

    Getting back to your problem, you monitor your disks and get the alerts but have you done like I suggested and figured out what it is that is causing the growth?  That's the first step that I'd take.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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