Help with Backup Best Practices
ghines
Posts: 7
We had a situation this morning that caused quite a bit of consternation among management. A huge file import was being done, and disk I/O skyrocketed, causing our web site to slow down to a crawl. The first inclination of the tech on duty was to kill the backup, so they did so using the Kill Process from Activity Monitor. The backup job then went into Rollback. About an hour later, they killed the huge import (via the same method), and once the import finished rolling back, the backup jobs finally finished also. So we're looking for a couple of suggestions on how to handle this situation.
1) Any suggestions on how to best run backups when there is a possibility of a huge file being imported? Will the backup take significantly longer if this happens? We have noticed the import does take much longer during a backup.
2) Is there a graceful way to kill the RedGate backup process? Should certain things be tried first?
3) How can we check if a backup process is in place before starting the import process? If we could stick some code in a procedure (or check in an SSIS package) to delay the import, that would probably be ideal.
Thanks for any help.
1) Any suggestions on how to best run backups when there is a possibility of a huge file being imported? Will the backup take significantly longer if this happens? We have noticed the import does take much longer during a backup.
2) Is there a graceful way to kill the RedGate backup process? Should certain things be tried first?
3) How can we check if a backup process is in place before starting the import process? If we could stick some code in a procedure (or check in an SSIS package) to delay the import, that would probably be ideal.
Thanks for any help.
Comments
Both your SQL Backup job and your file import will be battling it out for system resources, especially Disk I/O and CPU. Because of this the performance of your SQL Server and these processes will be greatly reduced.
It doesn't seem like running both processes in your environment simultaneously is very feasible so I would advise scheduling the tasks so they do not coincide.
If you really need to kill a SQL Backup job, the fastest way it to stop/start the SQL Backup Agent service on your SQL Server machine.
You can check if SQL Backup is currently performing a job or about too by opening the SQL Backup GUI and examining the timeline.
This should display all your scheduled jobs so you can plan a window for your file import.
If you do not use the SQL Backup GUI you can run the following to check for currently running backups :
exec master..sqbstatus
I hope this helps.
Redgate Foundry