locked
Unexpected Growth of MDF file RRS feed

  • Question

  • Hi,

      How to find the reason and How to overcome unexpected growth of MDF file.User DB is 120GB,but today morning its reached 360GB suddenly.

     What might be the reason and how to get back normal.

    Thanks

    Selva


    Thanks - SelvaKumarSubramaniam.Please MARK AS ANSWER, if my answer is useful to U.

    Monday, March 17, 2014 4:44 AM

Answers

  • Hello Selva,

    You you may have a scheduled index rebuild/reorg job? Then it's quite normal that the database files grows, because additional space is required to rearrange the index pages.

    With

    EXEC sp_spaceused

    you can check the used / free space in the database to see if really used data size has been increased or only the free/unused space.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, March 17, 2014 7:50 AM
  • Hello Selva,

    You you may have a scheduled index rebuild/reorg job? Then it's quite normal that the database files grows, because additional space is required to rearrange the index pages.

    Olaf,

    Does rebuilding indexes causes data file to grow massively( consider this scenario) .I guess log file can grow but after index rebuild data file size change from 120 G to 360G is highly unlikely.Any thoughts :).I guess culprit here can be autogrowth setting


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, March 17, 2014 9:17 AM
  • Hi,

    1. Did you checked the autogrowth parameter?

    2. check what is the actual data size in the database

    -- This example summarizes space used in the current database
    EXEC sp_spaceused;

    give us the results please :-)


    [Personal Site] [Blog] [Facebook]signature

    Monday, March 17, 2014 12:37 PM
  • Hi,

    Is it possible if someone has changed the size in chance?

    Please check the disk space usage and reserved space as pituach suggested.

    Base on my test, I was able to decrease the size of a database file. And the value of the file size should be larger than the actual size of the file.

    According to ALTER DATABASE File and Filegroup Options, when specified with MODIFY FILE, size is the new size for the file, and must be larger than the current file size.

    Reference:

    Decreasing the size of a database file through the Management Studio GUI?

    http://blogs.msdn.com/b/ialonso/archive/2007/12/14/changing-the-size-of-a-database-file-through-the-management-studio-gui.aspx

    Thanks.


    Tracy Cai
    TechNet Community Support

    Tuesday, March 18, 2014 8:27 AM

All replies

  • Hi, 

    Are you sure that you mean the MDF file an not the LDF file?

    the MDF by default is the data file. It include the actual data. that is not mean that you actually have 360 GB of pure data, since the file include free space and some headers and so on. But jumping from 120 to 360 GB can be done by altering and re-sizing the file or auto re-sizeing when the SQL need more space for the data. In the second case the file is growth according to the autogrowth parameter. Check the autogrowth parameter, maybe it is 240GB and therefor the data jump by this size (I guess this is not the issue, as this is very uncommon to set the parameter to 200%). In the other hand, more common is jumping of the log file which include action log, if there is a big set of operations like insert then the log file might auto growth. Again check the autogrowth parameter of the log file.

    * Please inform us if you where talking about the log file or the data file if you need more information :-)


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Monday, March 17, 2014 5:50 AM
    Monday, March 17, 2014 5:46 AM
  • Unexpected growth in MDF not in LDF.NO changes in Table size also.No bulk insert also.

    Thanks - SelvaKumarSubramaniam.Please MARK AS ANSWER, if my answer is useful to U.

    Monday, March 17, 2014 6:08 AM
  • >>>How to find the reason and How to overcome unexpected growth of MDF file.User DB is 120G

    Do not use a percentage growth instead  use MB growing.... Also preallocate  more space to the MDF/LDF .... to prevent frequently growing... as well.


    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

    Monday, March 17, 2014 6:29 AM
    Answerer
  • Hello Selva,

    You you may have a scheduled index rebuild/reorg job? Then it's quite normal that the database files grows, because additional space is required to rearrange the index pages.

    With

    EXEC sp_spaceused

    you can check the used / free space in the database to see if really used data size has been increased or only the free/unused space.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, March 17, 2014 7:50 AM
  • Hello Selva,

    You you may have a scheduled index rebuild/reorg job? Then it's quite normal that the database files grows, because additional space is required to rearrange the index pages.

    Olaf,

    Does rebuilding indexes causes data file to grow massively( consider this scenario) .I guess log file can grow but after index rebuild data file size change from 120 G to 360G is highly unlikely.Any thoughts :).I guess culprit here can be autogrowth setting


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, March 17, 2014 9:17 AM
  • I agree witj olaf and Shanky,Intial size of the database is changed suddenly.How ?

    Thanks - SelvaKumarSubramaniam.Please MARK AS ANSWER, if my answer is useful to U.

    Monday, March 17, 2014 11:52 AM
  • Hi,

    1. Did you checked the autogrowth parameter?

    2. check what is the actual data size in the database

    -- This example summarizes space used in the current database
    EXEC sp_spaceused;

    give us the results please :-)


    [Personal Site] [Blog] [Facebook]signature

    Monday, March 17, 2014 12:37 PM
  • Hi,

    Is it possible if someone has changed the size in chance?

    Please check the disk space usage and reserved space as pituach suggested.

    Base on my test, I was able to decrease the size of a database file. And the value of the file size should be larger than the actual size of the file.

    According to ALTER DATABASE File and Filegroup Options, when specified with MODIFY FILE, size is the new size for the file, and must be larger than the current file size.

    Reference:

    Decreasing the size of a database file through the Management Studio GUI?

    http://blogs.msdn.com/b/ialonso/archive/2007/12/14/changing-the-size-of-a-database-file-through-the-management-studio-gui.aspx

    Thanks.


    Tracy Cai
    TechNet Community Support

    Tuesday, March 18, 2014 8:27 AM