Post reply

backup file size

  • May 23, 2007 at 5:34 am

    #172519

    Hi.

    In a scheduled back up of SQL Server, we're finding the size of database file differing in a day. let's say, backup happens 3 times a day, the first 2 backup file size is smaller by 30-40% than the one happening later in the evening.What could be the reason ?

    Bharat

     

  • May 24, 2007 at 8:59 am

    #708681

    You'll need to provide additional information to get an informed answer.  What type of backups are you doing?  Full backups, differential, log?

    If you are doing full backups then the only thing I can think of is the database might be being shrunk by a job every night and as the day progresses the transaction log fills up causing the database to grow significantly in size.

    If you are doing differential backups then it's natural for the backup files to grow in size as with differential backups SQL Server backs up anything that has changed since the last full backup.

    If you are going log backups then again, the growth in backup file size might be due to the activity occuring in the database throughout the day.

     

    My guesses are based on the assumption that you have little or no database activity in the morning and as the day progresses that activity increases.

  • May 24, 2007 at 9:24 am

    #708691

    Thanks for the reply.

    I'm doing a full backup. There are no jobs to shrink the database in the evening. Infact, the server will be shut down around 7 PM every evening after the last backup.

    Interesting thing is let's say on 24th May afternoon, the full backup file size is 60 MB. On 24th May evening, the size will be 100 MB. Again on 25th may day time the file size is 65-70 MB. This is what i'm unable to understand.

    Is there any other factor you think of , please share.

    regards

    Bharat.

     

     

     

  • May 24, 2007 at 2:46 pm

    #708797

    Sure...

    Is the database set to "Auto Shrink", if so is there a process which deletes records in the evenings?

    It's even possible there's no process which deletes records but rather a long running transaction which is filling the log file up during the day.  Once that transaction commits and the log is free to be truncated the backup job itself will allow removal of those log entries so again if the database is set to "Auto Shrink" that would be an explaination.

  • May 24, 2007 at 7:59 pm

    #708829

    Thank you. Will keep bothering sometime again with queries...

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

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