mdf file growing

  • Hi,

    I am new in SQL Server. I have a database whose size is almost 25 GB, there log size 18 GB and mdf file is 6.30 GB, and everyday millions of records is coming and every hour data file size is increasing around 1 MB.

    Is this a normal , if not can anyone tell me the soluntion.

    Thanks in advance....

  • arooj, When you have created database so definitely you DB size is going to increase. you can check out the row count size of the tables or their sizes to get an idea on how frequently are these increasing.

    Based on your requirement, later on, you can plan some kind of purging mechanism, if required.

  • Thank yoiu very much for your prompt reply.....:-)

  • Check the recovery model of the database. If it is in FULL recovery you need to take frequently LOG-backups (every 15 - 30 minutes). If you don't do that the LOG file will continue to grow. By taking log-backups the allocated space within the logfile can be re-used.

    You could also change the recovery model to SIMPLE. Then log-backups aren't required but you can't restore to a point-in-time anymore.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • arooz, could you confirm if it is log file or data file in question here ?

  • arooj300 (5/15/2013)


    Hi,

    I am new in SQL Server. I have a database whose size is almost 25 GB, there log size 18 GB and mdf file is 6.30 GB, and everyday millions of records is coming and every hour data file size is increasing around 1 MB.

    Is this a normal , if not can anyone tell me the soluntion.

    Thanks in advance....

    There is nothing abnormal about Data or Log file growth given the circumstances you have described.

    That being said, you may want to take a close look at you Log File Backup Management Procedures as has been pointed out.

    You can always ask if you need help with that.

  • @sqlnaive:

    This does not need a purging mechanism!!! What is required here is a backup strategy that includes the transaction log. Simply purging by doing something like changing the Recovery Mode is a dangerous and potentially data-risky strategy.

    The fact that the database is so small and the TLog so large indicates from my side a) there is an awfully large amount of transactional activity or b) there is no mechanism in place (read backups) to help control the size of the TLogs.

    My advice therefore is to start there and build a backup plan to control the size of the .ldf.

    Regards,

    Kev

  • kevaburg (5/16/2013)

    arooj300I have a database whose size is almost 25 GB, there log size 18 GB and mdf file is 6.30 GB, and everyday millions of records is coming and every hour data file size is increasing around 1 MB


    @SQLNAIVE:

    This does not need a purging mechanism!!! What is required here is a backup strategy that includes the transaction log. Simply purging by doing something like changing the Recovery Mode is a dangerous and potentially data-risky strategy.

    The fact that the database is so small and the TLog so large indicates from my side a) there is an awfully large amount of transactional activity or b) there is no mechanism in place (read backups) to help control the size of the TLogs.

    My advice therefore is to start there and build a backup plan to control the size of the .ldf.

    Regards,

    Kev

    Kev - I know what you mean. I replied as per the situation described by arooj. As she clearly mentioned the data file size increasing and not log file. If it was a log file data size increase. I would have focused on the solution you are saying. And thats why I wanted to clear later from arooj if the question is about data or log file.

  • sqlnaive (5/17/2013)


    kevaburg (5/16/2013)

    arooj300I have a database whose size is almost 25 GB, there log size 18 GB and mdf file is 6.30 GB, and everyday millions of records is coming and every hour data file size is increasing around 1 MB


    @SQLNAIVE:

    This does not need a purging mechanism!!! What is required here is a backup strategy that includes the transaction log. Simply purging by doing something like changing the Recovery Mode is a dangerous and potentially data-risky strategy.

    The fact that the database is so small and the TLog so large indicates from my side a) there is an awfully large amount of transactional activity or b) there is no mechanism in place (read backups) to help control the size of the TLogs.

    My advice therefore is to start there and build a backup plan to control the size of the .ldf.

    Regards,

    Kev

    Kev - I know what you mean. I replied as per the situation described by arooj. As she clearly mentioned the data file size increasing and not log file. If it was a log file data size increase. I would have focused on the solution you are saying. And thats why I wanted to clear later from arooj if the question is about data or log file.

    This is where it gets a little complicated then. If the .mdf file is increasing in size then some form of transactional activity is taking place. If the TLog is not growing then I would suggest the TLog is not actually 18GB in size but rather much, much smaller. The 25GB would be representative of the maximum amount of space used since the last shrink. The actual data content could theoretically be only be in MB range.

    My recommendation: Backup the transaction log and then shrink it (the shrink being something only exceptional circumstances call for). Your .mdf file should continue to grow at the rate you have previously mentioned. TheTLog will (probably also grow) but then wil come to a point where it grows more slowly until it reaches its point of maximum growth. At this point you can observe the activity of the database and growth rates of both data files.

    If your database is running in SIMPLE mode then you could expect your transaction log to show some very dynamic behaviour but it will still remain relatively small according to how the data being brought in is being handled. If you are running in the FULL Recovery Mode then your TLog will continue to grow until the next log backup.

    I would probably suggest though that what you are witnessing in your database is normal behaviour.....

  • @Kev:

    Thanks for the reply..

    As I earlier mention that datafile is growing not log file, and the mdf file size was 6 GB but now size of mdf file is 8.88 GB but the log file size is remain same....

    Note: databse size is 28 GB. log backup is running as per the schedule time twice a day...

  • arooj300 (5/21/2013)


    @Kev:

    Thanks for the reply..

    As I earlier mention that datafile is growing not log file, and the mdf file size was 6 GB but now size of mdf file is 8.88 GB but the log file size is remain same....

    Note: databse size is 28 GB. log backup is running as per the schedule time twice a day...

    You are describing nothing more complicated than normal SQL Server behaviour. You .mdf is growing because data is being added, that much is obvious.

    Your transaction log will remain the same "size" as viewable by the operating system until SQL Server sizes it upward again because it needs the room. The amount of data actually inside that file is what matters. I can guarantee that although your log file appears to remain the same size that after each transaction log backup the actual data remaining inside the log file is reduced dramatically.

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

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