backup job

programmer76programmer76 Posts: 12
edited July 31, 2007 4:55AM in SQL Backup Previous Versions
Hi,

i started a full database backup job for a very large Database around 500 GB. When I go to SQL management- JObs and If I refresh over there I still see the status of the job is still executing. It has been more than 24 hours since this job is running. But I don't see any changes in the .mdf file which i'm observing at the back up folder since I started this job. Size of .mdf file is constant now but job is running for few hours now. I gave option to create the back up using red gate sql backup without any compression and also create the backup in only one file. What confueses me is that SQL job status says still executing but there is no change going on in the .sqb back up file.

What may be the problem? Is there any limit on the size of .sqb file by any chance ? I see a .sqb file with size around 350 GB. In native sql I can check the content of the back up file with RESTORE FILELISTONLY FROM DISK command. Is there something in red gate which I can use to see if the backup created by redgate is good.Or is there any other way out?

I will appreciate an early reply gurus...



Thanks

Comments

  • To check the progress of the backup, execute the following command in Query Analyser or Management Studio:

    master..sqbstatus 1;

    In particular, note the "processed (bytes)" and "compressed (bytes)" column. If these change when you run the command again a minute later, then the backup is still performing.

    However, if the values are unchanged after 2-3 minutes, and the "processed (bytes)" value is not comparable to the size of the database, it suggests that something may have gone wrong, which will need further investigation.

    The size of the SQB file is a bad indicator of progress, since the value reported by Windows can remain unchanged, even though the file size is increasing. The above command will give you the exact progress of the backup, from the perspective of the SQL Backup service.

    Hope that helps,
    Jason
  • Hi Jason,

    I looked in to it deeper and the reason the backup file size is not increasing is that there was not enough space left on the disk where we were putting this back up.

    All the other backup jobs in sql server which started after this job has failed specifically giving message that there is not enough disk space to perform back up. But this job has not failed. Even right now it's status says executing even when there was no disk space.

    I also ran your command master..sqbstatus 1. "processed (bytes)" and "compressed (bytes)" column values remain unchanged.

    Now to take care of this issue I emptied more than enough space on the back up disk. But even after that still the job status remains executing but there is no change in the back up file size and also the processed and compressed column values remain unchanged.

    What does mean? I have a big 400 GB incomplete backup file but I guess it's not good?Is this job is unsuccessful even though we don't see any error message Or is there any other way to tackle this OR I have to start the back up again from scratch?

    Thanks
  • Hi,

    There is a known issue with backups sometimes stalling if the drive runs out of disk space... unfortunately the only solution is to restart the SQL Backup service (which will release any locks on the file) and restart the backup.

    Because the backup is incomplete, it will not be "good", so really it needs to be started from scratch.

    I'll pass this issue on to the developers to look into this again, since while nothing disasterous happens per-say, it's obviously far from ideal that no error or email notification is being issued.

    Thanks,
    Jason
Sign In or Register to comment.