12

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.

immutabl
  • 809
  • 2
  • 7
  • 16

5 Answers5

8

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

Community
  • 1
7

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

Simon Hughes
  • 618
  • 1
  • 6
  • 12
6

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.

Community
  • 1
Marian
  • 15,741
  • 2
  • 62
  • 75
6

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.

Jason Cumberland
  • 1,051
  • 6
  • 10
4

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.

Thomas Stringer
  • 42,434
  • 9
  • 120
  • 155