February 15, 2016 at 6:35 am
Hi,
I am getting continuous blocking.
We are using SQL Server 2008r2 standard edition.
We have a native backup job scheduled daily. Everyday it is running fine.
On weekend I monitor it is continuously blocked by other backup.
The command= backup database dbname to virtual device='.....'
The command= backup database dbname to disk='\\...'
I killed the native backup session. Blocking is gone but when I look for the PID of the other backup job from sys.processes. It has 3 kpids with that hostprocess for that spid.
But from the task manager I can't able to see that PID which matches the hostprocess in the task manager.
After some time when I tried to run backup again the blocking started again. But this time the SPID id changed but it is blocked by same hostprocess
How can I able to run the native backup and how to identify which 3rd party tool is taking that backup?
February 15, 2016 at 7:05 am
History of backups is in msdb.dbo.backupset, backupmediaset and backupmediafamily.
Try looking in there and see what's the application name that takes those backups.
-- Gianluca Sartori
February 15, 2016 at 8:45 am
Thank you.
I check those tables.
select distinct software_name from backupmediaset = Microsoft SQL Server.
When I check the SQL Server error logs
I found sequence of error msg logged different times
BACKUP failed to complete the command BACKUP DATABASE . Check the backup application log for detailed messages.
BackupIoRequest::ReportIoError: write failure on backup device '557074ea-a58b-4f90-a499-2d9eebabac89'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.)
BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device '557074ea-a58b-4f90-a499-2d9eebabac89'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).
So I am suspecting other backup didn't run successful thats why I am not able to see different software_name.
I am wonder why I can't able to see the PID of that host proess from Task manager event though it is showing it is still active from SSMS
February 15, 2016 at 9:09 am
Now it started the new hostprocess.
From Resource Monitor I can able to see that PID and the image is showing SQLBackup.exe.
For the same PID, I can see one Image but there are 2 Address from Network tab under resource monitor.
But I can't able to see that PID from the task manager.
What will happen if I kill that backup SPID from SSMS?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply