Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Backup never ends -- how to kill backup, kill/stop does not work

Status
Not open for further replies.

katekis

MIS
Feb 12, 2009
66
US
My Database backup never ends. I was able to previously backup the database in less then a minute. Now my backup runs forever and never ends.

It has a wait Type of "MSSEARCH"

I stopped the backup.

I then Killed the backup (kill spid).

The Backup still shows in the Activity Monitor. The only way I found to get it stop is to reboot the server. This is currently not an option.

Does anyone have other ideas on how to kill this Backup.

Once it is stopped what can I do to figure out what is wrong with the database and why it will not backup.

It has been many days since I have been able to successfully run a backup on the database.
 
Note, you don't have to reboot to kill the process only restart the SQL services. It is usually a quick process if you can afford the minute of downtime. Adding WITH STATUSONLY to your kill command can show you what its status is and might give some insight into what it is doing. What version and service pack are you running?
 
I assume that you have or had a full text catalog set up. Did you recently delete one or disable the full text service?
 
The SQL Full Text service is running. I restarted this service and the Backup finally STOPPED. THANKS YOU!

Do I dare try to run backup again? I am cauious because I do not want to have the backup stuck again, can not easily restart SQL Server, production server.

All other backups on the server are running successfully, it is just one database I am having a problem with.
 
If the database you are having problems with has any full text catalogs, try rebuilding them.

Were there any changes to the full text catalogs right before you started having problems with the backups?

Also, run backups for your other databases one at a time. That way you at least have a good backup.
 
Yes you can run the backups again. If the backup hangs again, just restart the full text service and the backup will begin doing the actual backup.

This is a known bug which hasn't been fixed yet.

Setup a job which starts 5 minutes after the backup kicks off. Have it check for backups being blocked, and if there are have it restart the full text service.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top