Issue with a backup on sql 2005

[kodaksmile]

This one is a bit odd - we have a sql 2005 database that also uses the fulltext search. The backups were running fine then the vendor performed an upgrade (unfortunately the dba's are not involved with this - part of the vendor's contract) and after the upgrade we have a database that will not backup. The backup begins and you can see the process in the activity monitor but, it shows a waittype with the description MSSEARCH. Any suggestions? The transaction log backups run fine it is just the full backup for this one database that just hangs forever (does not show as being blocked) My first thought is that it was unable to put all of the full text indexes in a "backup freeze" status since only 4 of the 7 full text catalogs show up in that status - the others show available. Any suggestions?

 

[mrdenny]

Create a new job on the server.

Have the first step look for a stalled backup. If there isn't one throw an error and retry a few times. If there is one continue on to the next step. On the next step stop the MSSEARCH service. On the next step pause for 40 seconds. On the next step start the MSSEARCH service.

Add a step to the backup job to start the other job before the backup starts.

I had the same problem.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[kodaksmile]

I did a manual test where I stopped the MSSEARCH service and then let the hung job finish and ran a backup job from scratch which worked fine. Any idea on why all of the sudden the full test is causing an is causing an issue with the backups? Just wondering since is that something is wrong with one of the full text indexes (wondering if I have more to fix than just a backup even though there are no complaints from the business)

 

[mrdenny]

I had the same problem for a while, and wasn't ever able to figure out what the cause was.

What it appeared to be was that the full text catalog was being updated very often, and because of this the MSSearch wouldn't release it's lock on the database which was causing the backup to not be able to start.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog