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:

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

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.

How big are the database and backup and whats the daily/weekly growth rate?
–
Mark Storey-SmithOct 3 '11 at 9:48

Backup files are around 3-4GB in size. Growth is minimal.
–
5arxOct 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-SmithOct 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
–
5arxOct 3 '11 at 15:00

5 Answers
5

(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.)

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.

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.

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.