Post reply

HUge Backup

  • October 22, 2007 at 4:45 am

    #176408

    SQL 2000.

    My DB is 700 meg the scheduled backup creates files of 30 gig any ideas why this might be?

    Thanks,

    www.sql-library.com[/url]

  • October 22, 2007 at 5:28 am

    #744073

    Are you trying to do full database backup or differential backup?

  • October 22, 2007 at 5:39 am

    #744081

    full

    also when i backup the dn manually rather then with the maint plan the db size is normal?? Only the maint plan causes it to be massive

    www.sql-library.com[/url]

  • October 22, 2007 at 2:14 pm

    #744241

    I'm sorry that I got your question wrongly. I thought your database size is 700GB and your backupfile size is 30GB.

    What's is the option that you selected when you perform the backup using the maintenance plan? Do you select the option 'Remove files older than......'?

  • October 22, 2007 at 3:32 pm

    #744256

    Did you include LOG file in those 700 MB?

    _____________
    Code for TallyGenerator

  • October 22, 2007 at 5:15 pm

    #744272

    its a complete backup and the db recovery mode is simple. there isnt must activity in the db either so the log files are small.

    It was set to remove backups older than 14 days

    www.sql-library.com[/url]

  • October 22, 2007 at 7:52 pm

    #744291

    Every time you create the maintenance plan, it will generate a SQL job. Can you ensure the SQL job that is generated by your maintenance plan is having the following parameter (under the management panel -> SQL Server Agent -> Jobs):

    EXECUTE master.dbo.xp_sqlmaint N'........... -DelBkUps 14DAYS....'

    Just want to make sure that the options you have set are intake.

    If the above criteria are met, you have 2 options here:

    1) Turn on your profiler and run the job

    2) Delete the maintenance plan, ensure the SQL job is being removed, re-create the maintenance plan and run the job.

  • October 22, 2007 at 9:40 pm

    #744309

    Also check to make sure you're not appending backups to an existing backup... you could very well have a file that contains multiple backups, check the contents of the backup file.

  • October 22, 2007 at 11:33 pm

    #744322

    Probably this would be appending the backup to same file.:D

    binu john

  • October 23, 2007 at 4:15 am

    #744406

    What are the steps included in the maintenance plan. Are you doing the reindexing just before the full backup. Does the database size increases after the backup or it reamins at 700megs only.

  • October 23, 2007 at 4:37 am

    #744419

    where is that set? Think thats whats happening but cant find the place to toggle that on and off.

    I want a separate file for each backup.

    Cheers,

    www.sql-library.com[/url]

  • October 23, 2007 at 5:06 am

    #744428

    Hi,

    I got confuse 😉

    Let me know if I got your question right:

    You perform backup for database A (db size = 700MB) via maintenance plan and the backup file (A_db_yyyymmddhhmn.BAK file) size is 30GB.

    if the maintenance plan is scheduled daily every 7pm, the backup file will be having the following naming convention: A_db_200710231900.BAK

    You can read my next comments if I get your question right.

    What I understand from the maintenance plan is:

    It always generates new backup file and by nature, it will not append.

    Refer to my previous comments where you may check if the options are intake on your SQL job. You may use SQL profiler to investigate further or simply delete and recreate your maintenance plan.

  • February 8, 2008 at 5:42 am

    #776354

    Maybe you have set the WITH NOINIT option ... this appends every backup to the file, which means your backup file grows by the size of your database, every time you backup.

    just change to INIT and backup again. Then check the file size again ...

Viewing 13 posts - 1 through 12 (of 12 total)

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