March 19, 2014 at 11:01 am
I have an Network Administrator running a 3rd Party Backup Utility on an instance of SQL Server.
On one instance the 3rd party Backups are hanging. Everything is fine on the other instance.
I did not setup that Server and it had been in operation for years before I started as the DBA. All I did were setup native SQL server Backups, maintenance tasks, etc.
Yesterday I killed some process and the backup of Reporting Services Database is stuck in a KILLED/ROLLBACK Status. Reporting Services is not being used on that machine.
The backups from last night was running for nearly 12 hours before I rolled the dice and killed the process that was backing up the Reporting Services TempDB.
I tried to convince the network type that the backups were hanging but he does not believe me.
I showed him a screenshot of sp_who3 of all the backups that were in progress but he I could not convince him otherwise
This problem is not going to go away. It will happen every day.
My concern is that tomorrow or some day in the future I kill the process that is hung backing up a mission critical database it will get stuck in a Killed/Rollback state and I will end up with databases in Recovery mode. The Recovery fails and I'm stuck with an old backup. What actions can I take to prevent this from happening?
Any ideas would be greatly appreciated.
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 19, 2014 at 3:35 pm
Check for any resource contention issues on the server that's causing this. Usually this occurs due to poor I\O. Try to find on what are the backups waiting for ? You may need to tune the server well to avoid this issue.
--
SQLBuddy
March 19, 2014 at 3:56 pm
sqlbuddy123 (3/19/2014)
Check for any resource contention issues on the server that's causing this. Usually this occurs due to poor I\O. Try to find on what are the backups waiting for ? You may need to tune the server well to avoid this issue.--
SQLBuddy
The problem is not with SQL Server.
About a month ago I was called into a problem with a DB Server that printed checks.
Same issue. 3rd party backups were hanging and blocking.
I cleared the blocking and I told the Support person to wait until the morning, I did not do anything for I wanted to see what would happen the next morning.
One of the Network Support Types overheard the conversation and the problem stopped.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 19, 2014 at 4:46 pm
Welsh Corgi (3/19/2014)
I have an Network Administrator running a 3rd Party Backup Utility on an instance of SQL Server.On one instance the 3rd party Backups are hanging. Everything is fine on the other instance.
I did not setup that Server and it had been in operation for years before I started as the DBA. All I did were setup native SQL server Backups, maintenance tasks, etc.
Yesterday I killed some process and the backup of Reporting Services Database is stuck in a KILLED/ROLLBACK Status. Reporting Services is not being used on that machine.
The backups from last night was running for nearly 12 hours before I rolled the dice and killed the process that was backing up the Reporting Services TempDB.
I tried to convince the network type that the backups were hanging but he does not believe me.
I showed him a screenshot of sp_who3 of all the backups that were in progress but he I could not convince him otherwise
This problem is not going to go away. It will happen every day.
My concern is that tomorrow or some day in the future I kill the process that is hung backing up a mission critical database it will get stuck in a Killed/Rollback state and I will end up with databases in Recovery mode. The Recovery fails and I'm stuck with an old backup. What actions can I take to prevent this from happening?
Any ideas would be greatly appreciated.
Thank you.
You need to find the process in Task Manager in Windows and stop that process if you want to fix this without having to bounce the whole server. And just restarting the service isn't going to make the actual problem go away even if it makes SQL Server look like the problem went away. And don't just end the process... make sure you end the "Process Tree". Right click on the offending process and select "End Process Tree" because the hung process may have children.
That may be what that person who overheard the conversation did.
Chose carefully.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 19, 2014 at 4:56 pm
True, that could be a resource contention issue, the resource being network. May be the backups are stored on a remote server and a slow network could cause this.
Backups don't cause blocking but they would definitely freeze database I\O which looks like blocking. Check the error logs and you would find some entries regarding this.
Also monitor the Network counters to find any N\W, NIC issues ..
--
SQLBuddy
March 19, 2014 at 5:18 pm
Jeff Moden (3/19/2014)
Welsh Corgi (3/19/2014)
I have an Network Administrator running a 3rd Party Backup Utility on an instance of SQL Server.On one instance the 3rd party Backups are hanging. Everything is fine on the other instance.
I did not setup that Server and it had been in operation for years before I started as the DBA. All I did were setup native SQL server Backups, maintenance tasks, etc.
Yesterday I killed some process and the backup of Reporting Services Database is stuck in a KILLED/ROLLBACK Status. Reporting Services is not being used on that machine.
The backups from last night was running for nearly 12 hours before I rolled the dice and killed the process that was backing up the Reporting Services TempDB.
I tried to convince the network type that the backups were hanging but he does not believe me.
I showed him a screenshot of sp_who3 of all the backups that were in progress but he I could not convince him otherwise
This problem is not going to go away. It will happen every day.
My concern is that tomorrow or some day in the future I kill the process that is hung backing up a mission critical database it will get stuck in a Killed/Rollback state and I will end up with databases in Recovery mode. The Recovery fails and I'm stuck with an old backup. What actions can I take to prevent this from happening?
Any ideas would be greatly appreciated.
Thank you.
You need to find the process in Task Manager in Windows and stop that process if you want to fix this without having to bounce the whole server. And just restarting the service isn't going to make the actual problem go away even if it makes SQL Server look like the problem went away. And don't just end the process... make sure you end the "Process Tree". Right click on the offending process and select "End Process Tree" because the hung process may have children.
That may be what that person who overheard the conversation did.
Chose carefully.
Ok, thank you very much! 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 20, 2014 at 3:23 am
Thanks Jeff.
Fortunately the Backups did not hang last night.
I had this problem about a month ago on another server.
I was out on vacation but when I returned I discovered that the backup jobs were hanging.
In my absence they were just restarting the SQL Server Service without knowing was wrong.
Thank again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 20, 2014 at 4:35 am
You could contact the backup software company. Perhaps they have "retry" settings that will help if there is contention or network problems.
March 20, 2014 at 5:22 am
homebrew01 (3/20/2014)
You could contact the backup software company. Perhaps they have "retry" settings that will help if there is contention or network problems.
That's why I like homegrown solutions. It really doesn't take much to write a proc to do backups and, if something goes wrong, you can tweek the proc. Native restores aren't difficult to do, either.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2014 at 8:34 am
homebrew01 (3/20/2014)
You could contact the backup software company. Perhaps they have "retry" settings that will help if there is contention or network problems.
I wish I could but I'm the DBA and the Infrastructure Manager uses software that causes me grief.
I perform my own backups but the 3rd party software , on occasion causes the backup to hang
The problem went away. I check it at 4:00 AM est.
Thanks to you and everyone that replied.
Regards.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply