I'm having to disable nightly SQL backups. They're peaking CPU activity and blocking access to end users.
Is SQL backup a blocking or non-blocking operation? Can it be setup to run on a schedule without affecting end-users?
Sam
Backups don't block any objects on the database.
Are you backing up to local disks, or something more "remote"? I find that backing up to remote seems to cause more "effort" than to local disks, but that may be subjective judgement on my part 
FWIW we backup FULL on Sunday Nights and DIFF on weekdays. The DIFF runs more quickly ... although it has more to do, and takes longer, as the week progresses.
1 Like
Backup pegs the CPU, creates a backup size of 522,388Kb. It's a 4Gb 64 bitOS, 4 Xeon processor, running on a RAID 10 SSD. Lasts about an hour.
Eh, maybe I'm just tired, but how can it run more quickly but take longer?
bitsmed...You're tired. The DIFF runs more quickly [than the FULL]....and takes longer [than the previous DIFF] as the week progresses.
ahmeds08...We have received errors that we can't perform certain disk related administrative activities at the same time as the backups. You are correct that the front-end queries would not be blocked by a backup. The only impact might be a slow down since resources are in contention.
SamC... Are you performing any other administrative tasks, such as re-indexing or file shrinking, during the time of the blockage?
1 Like
Thank you Stephen - I'll go to bed right away 
You're not that tired ... I could have explained what I meant better ...
There is something seriously wrong with your system. I backup a 140GB database to remote NAS storage in about 45 minutes. If what you posted is correct, you're only backing up about a 1/2GB. As you know, an hour is an insane amount of time for such a thing. And backups should NEVER peg the CPU.
What are you using to do the backups with? Native SQL Server backups or something else? Do you have compression turned on or not? Which version, edition, and SP are you running at for SQL Server? And are you backing up to disk or directly to tape?
I apologize for my mis-diagnosis. We're doing remote monitoring and found that this past Monday through Thursday, there was a server blackout lasting two hours beginning at 2AM, exactly when the SQL backup was scheduled. I moved the schedule up to 11PM and since then, no blackouts have occurred.
The backup itself takes no time at all after further testing.
While this is good news, the bad news is I don't know what was causing the server unavailability and have no idea when it might happen again. I've setup text messaging via an external server monitoring service to check it when it happens. It's hard to fix a problem when it isn't broken.
Sam
1 Like
Step 1 would be to look for jobs that light off at 2AM. Index rebuilds and backups do form a somewhat contentious bit of code that can make it look like the server is "blacked out".
I think LOG BACKUP blocks FULL BACKUP? So if, say, index rebuild was running, huge Log Files, Log Backup running ... then FULL Backup would be delayed? Possibly a log time?
Log backups and full backups don't block each other - this was true a long time ago in SQL Server 2000 but has not been an issue since 2005.
What can happen is if you have large rebuilds/reorganizes - and the full backup is running at the same time - then the log backup cannot mark the VLF's as available until the full backup completes. This could cause the logs to grow even if you are backing up the logs every minute.
1 Like
Agreed. My point was, the system gets busy. If it doesn't have enough horsepower, it could cause significant delays that would make the system look "blacked out".