Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We run our weekly backup job at 9pm on Fridays and we are experiencing a couple of issues with regard to disk space (which gets perilously low at times) and performance. We're looking at streamlining/optimising what happens and would appreciate your comments.

Specifically:

  1. The backup process is taking about 4hrs to update statistics during the backup. Can we safely disable this process to save time?

  2. We're running low on disk space very regularly and wondering if we should re-jig the process. Currently it creates the backup and then deletes the previous backup and this is what is hogging disk space. Can we safely delete the previous one first and then do the backup?

Any other comments or observations would be very welcome EDIT: Total size of SQL files on the server is around 35GB. One db is about 25GB in size while the other six share make up the other 10 or so GB.

share|improve this question
1  
How big are the database and backup and whats the daily/weekly growth rate? –  Mark Storey-Smith Oct 3 '11 at 9:48
    
Backup files are around 3-4GB in size. Growth is minimal. –  5arx Oct 3 '11 at 10:53
1  
A full backup is only 3-4GB in size but update stats takes 4hrs? Something not quite right here. How large is the database on disk? –  Mark Storey-Smith Oct 3 '11 at 11:45
    
We have multiple databases totalling about 35GB (for the MDF files). One of them has an MDF file of approx. 25GB in size with the others having MDFs around 3-4GB in size. The big one is odd because the backup file and the MDF file are roughly the same size –  5arx Oct 3 '11 at 15:00
add comment

5 Answers

(1) Yes, I usually have the backup process by itself. I would not be doing much of anything during my backup time if I can. You might have it take the backup and then do the update on stats. As it sounds it seems you are running two jobs (1 for backup, 1 for update stats) at the same time?

(2) Do you copy the backup to tape or other disk storage? If so then I usually clean up the files before I create new backups locally. If not, then if I am scraping for storage space I would consider zipping the backup file prior to the new one being created. (That is if you cannot enable compression on the backups, as @Simon suggested, this would save some space as well.)

share|improve this answer
add comment

I can only answer question 2 here. I recommend you take a look at compressed backups.

share|improve this answer
add comment

1) I don't see a direct relation between the task of making a backup and the task of updating statistics. So you can split them without any problem. I'd see the updating statistics part more related to a job that would defrag/rebuild the indexes.

2) Even if it's for short time, you don't want to be without a backup. So you'll want to remove the last backup only if you already have it saved somewhere else.

Side note here: if you're doing backups on the same storage box where you have the database, then the backups will not be safe when you'll have a hardware issue with the storage box. So you need to ensure you have enough space for backups somewhere else, not on the same machine.

Side note 2: as specified already by Simon, invest time/money in compressed backups if you have space issues. You can see many ideas in this question: The smallest backup possible … with SQL Server.

share|improve this answer
add comment

Your update stats task should not be taking 4 hours for a 3-4 GB database. More than likely you have have some I/O issues or you have a heavily fragmented database that is creating I/O issues. Run a defrag or index rebuild on the database and see if that improves the performance. If not then fire up perfmon and check where your performance bottleneck is.

share|improve this answer
add comment

I wouldn't recommend deleting your sole backup before taking a new one. It wouldn't be the first time a backup failed or the instance went down, and having a gap in time with no chance of recovery is ill advised.

That's not the solution to your problem. Figuring out how to have more space to house both would be the right way to go about that.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.