Sep 27, 2017

If SQL backup job is taking long time, how will you troubleshoot it?

Ans: I would check all the components involved while taking backup means Disk IO on source (where database files resides) and destination, CPU utilization and the N/W utilization as well if the backup destination is on network location. Additionally, what else is running on server at same time which may impact the backup? E.g. rebuild index, bulk copy or insert, log backup (perhaps; need to verify), latency to complete checkpoint process and ghost cleanup task (need to verify). I will check the following to monitor relevant components involved in backup process:

Disk IO: Average disk queue length, Average sec/read, Average Disk sec/write (Object: Logical Disk)

CPU: Processor Time (Object: Process), Processor Queue Length (Object: System)

Network: Bytes Total/sec, Bytes Sent/sec, Bytes Received/sec, Current Bandwidth (Object: Network Interface)

No comments:

Post a Comment