SQL Server Performance

Msql_xp Wait Type in SQL Server

Discussion in 'ALL SQL SERVER QUESTIONS' started by Hrishi_nk, Jan 19, 2012.

  1. Hrishi_nk New Member

    Recently I encountered this wait type on our production servers.The command column in sys.processes was KILLED/ROLLBACK.And this process was blocking other processes on my SQL server. As it was rollbacked there was no point in killing that process.I read on many forums and I
    found that restarting SQL server is only the solution.And this process was a linked server query.
    Is there any solution other than restarting SQL server during production hours?
  2. davidfarr Member

    As you may know; restarting Sql Server does not 'cancel' a rollback. At best, it can suspend the rollback, which will resume again when the service starts until it completes the rollback.
    Depending on your version of SQL Server the database being rolled back may even be offline when the SQL service starts until all transactions complete the rollback.

    Something that I have experienced with large rollback processes, although I cannot confirm this as technical fact, is that the other user processes that are being blocked seem to still contend with the rollback process, slowing it down or possibly even deadlocking it. I have found that killing the blocked processes, and temporarily disabling the logins of those users, seems to speed up the rollback process. This is also why restarting the service can sometimes assist the rollback.
    It is my opinion that Microsoft has some unaddressed issues regarding SQL server rollbacks. I have once experienced a rollback process on a 100GB database that remained 'stuck' for 12 hours with no progress. I had to stop the service, delete the physical database files, start the service, and restore from backups to point-in-time before the transaction started. I was fortunate that I could afford to do this.

Share This Page