Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Warwick
Starting Member
7 Posts |
 Posted - 2007-07-04 : 19:48:16
|
| I have a DB in SQL2K5 sp2 which we have a couple of FTC's created.scenario is as followsDB1 is a prod DB which has 3 FTC created. This DB needs to be backed up so it can be restored as a QA DB for pre prod roll-out. The QA DB is restored in the same instance as prod. Now with SQL2k5 incoporating the backup of FTC's into the database backup this caused a small problem. The restore has to perfrom the with move and this carries across to the FTC files. However atm the backup is being undertaken at the file level and not copying the FT files. The restore process is using the with Move, once the QA DB is up again i drop the FTC from the QA DB.1 particular FTC did have a nightly Incremental load happening but this has been stopped for the moment due to these issues.The QA restore only happens once a wk. When this occurres the restore process sits and hangs (thus making the qa DB unavailable). Using sp_who2 and the activity monitor i have been able to see that the spid for the restore is waiting and has a wait type of 'MSSEARCH'. (From what i have read this is waiting for some action in the MS Search service to complete) Due to this search wait type when the scheduled backup of DB1 starts it is then hung with a wait type of 'MSSEARCH'. this then leads to increasing connections which leads to blocking and timeouts of the app. The Restore spid is not able to killed.The backup spid is not able to killed.The MS Search Service is not able to be restarted.The only way i have been able to resolve this is by bouncing the server. Now as this is a prod server this is not suitable.I have tried that just prior to the full backup starting to stop the MS Search Service but as the FTC's are included into the backup the service obviously needs to be running and the backup process started the service again.I am at a loss as to what could be causing the FT Service to get itself into a state where it is sitting waiting like this.Advice help on places to start and look so as to be able to stop and fix this would be appreciated.thx in advance for you knowledge on this |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-04 : 21:18:34
|
| Don't do backup and restore at same time in your case. Put qa db on qa machine if possible. |
 |
|
|
Warwick
Starting Member
7 Posts |
Posted - 2007-07-04 : 22:00:49
|
| RMiao,The backups and the restore are not done at the same time. Also i am not able to put the QA on a different server. Even though i have said this would be a much better solution i am restrained by the hardware i have at my disposal.The full backup is occuring 2 hrs prior to the restore process starts. The restore process sat for nearly 14 hrs waiting and so it needed to be killed and thus also was not able to be achieved and had to lead to the bounce of the server.thx |
|
|
|
|
|
|
Full Text Search