Link to home
Start Free TrialLog in
Avatar of ZekeLA
ZekeLAFlag for United States of America

asked on

SQL Server 2000 Backup File Size is Excessive

When applying changes to our web site's database, I make a backup first in case I need to roll back the changes. The database is at about 5.5 Gb in size. Until now, my manual backups have always created a backup file sized about 75 Mb. There is also a daily job which creates the backup around midnight each day and that backup file is also about 75 Mb.

But tonight, for some reason, the manual backup I generated came out to about 5 Gb in size. This took that drive to less than 5% free disk space which is how I found out about the problem.

As far as I know I created the manual backup the same as always. I verify upon completion, overwrite existing media, and save to a new disk file. I'm wondering if I did append instead of overwrite or forgot to remove the existing filename when entering the new one. Even so, I would only expect a doubling of the file size, not the full database sizing.

Can someone explain or guess at what I did or what could have happened? Thanks.
SOLUTION
Avatar of SNilsson
SNilsson
Flag of Sweden image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
small note of advice, actually backup strategy should be something like take differential backup, transaction backup and full backup. you should run transaction backup every 5-20 minutes, differential backup every 1 hour and full backup should be at every 24 hours. Since your database is small, these time frames are pretty good for you. SNilsson is right, this is because of log files and you can first backup your log file and than try getting full backup.


Avatar of chapmandew
More frequent trans log backups are necessary.  It is preemptive for RiteshShah to suggest a timeframe, because one cannot  be established at this point.  But, the more often you do trans log backups, the better.  The differentials aren't really going to help in terms of backup size, it will just make it easier to recover to a point in time.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ZekeLA

ASKER

I looked further into this and it was the log file that had grown excessive. Apparently, SQL Agent stopped running about 10 days ago. Our daily backup job is run via SQL Agent which is the cause of the problem.

RiteshShah and all, if I want to implement a more frequent backup strategy, it looks like I would have to track multiple backup files so I could do succesive sequential restores in the event of a failure. Can any of you explain further or provide any links to backup strategies that would help me to configure things better?

Thanks.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
There is always trusty old MS, here is for the full backup:
http://msdn.microsoft.com/en-us/library/ms190217.aspx
The script I gave you, has to be set in jobs with proper login credential, it will generate, transaction log backup, differencial back and full backup. it will delete 24 hours old backup as well. When I was using sql server 2000, I have started using that script, very trusted and give good performance. set low traffic hours for full backup.
Avatar of ZekeLA

ASKER

Thank you all. I'll be implementing a more comprehensive backup strategy once I fix the immediate issue.