question

shridhar avatar image
shridhar asked

SQL stop all process while backup

Hi I am using SQL server 2012 web edition having 150 GB Data when I start Backup from management studio then my all other (like insert ,updates) operation which get automatically stops
sqlsql-serverbackupbackup failed
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tim avatar image
Tim answered
Hi Shridhar, SQL Server backups don't cause blocking like what you have described above. Backups are resource intensive for disk as the pages are having to be read from disk and written back out to hopefully a different disk. Its possible that latency from the backup running is what is causing your issues. Have you done any troubleshooting to look at file statistics, wait statistics, waiting tasks, or other metrics?
5 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image Tim commented ·
What are the waiting task, if Backup is listed, what it is waiting on? Anything else. You would typically see things waiting, but what are the duration. Please provide as much detail about your environment. # of cores, memory, disk, where you are backing up to (URL, network share, backup device, local disk??) Has this always been happening or just recently started, more details are better.
1 Like 1 ·
shridhar avatar image shridhar commented ·
Yes I did when I start backup that time some process show into waiting task
0 Likes 0 ·
shridhar avatar image shridhar commented ·
waiting task are insert into table select * from tables
0 Likes 0 ·
Tim avatar image Tim commented ·
And what else is happening on the server? Any other metrics you care to share? memory usage, cpu usage, file statistics, , , , , there is so much more information you can share, otherwise none of us here are going to be able to help you with you providing so little details.
0 Likes 0 ·
shridhar avatar image shridhar commented ·
actually I have tracking application which continuously insert data into table CPU usage go near 60 % but buffer latch automatically goes at the same time all insert task go to wait then database I/O goes high near 40MB/sec all operation get suspended
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.