SQL Server Performance

process takes no of days in killed/rollback stage

Discussion in 'Performance Tuning for DBAs' started by DKG, Jun 5, 2008.

  1. DKG New Member

    Hi All,
    The database backup job used to hang regularly on one of my servers, when i kill it takes long long time(no of days) and need restarts of SQL service every time to flush it - till the time I m not able to backup my database. it has become a major problem now. There is enough free disk space on the server and once i tried manual backup through EM it worked absolutely fine. Don’t know what’s wrong with the server.
    Backup script is using 'xp_sqlmaint' and server edition is SQL 2000 Ent Edition with SP3, while the other server with same configuration is working perfectely fine.Any quick response would be highly appreciated.
  2. MohammedU New Member

    If there any blocking going on? Check the following thread too...
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=945054&SiteID=1
    Generally, I don't advise or use 'xp_sqlmaint', try using BACKUP COMMAND instead of maintenace plan...
    Are you backing locally or to the network...if you are backing up locally then try and see if hangs...to isolate the problem from network...
  3. DKG New Member

    Thanks for your quick response, blocking could be the possible cause but I have already checked there is no blocking on the server. And im taking the backup on locking hard drive - which is having enough free space and other sys dbs backup are successfully happen on same disk using sqlmaint.
  4. satya Moderator

    SQL 2000 with SP3, is very old version and SP4 for SQL 2000 might have few fixes in this regard, try to test it and apply to resolve the issue.
    If not have a thorough checkup on the hardware where it is the culprit for causing such an issues.

Share This Page