Il ripristino o il ripristino può avere esito negativo o richiedere molto tempo se la notifica delle query viene usata in un database

Questo articolo illustra come risolvere il problema in cui il ripristino o il ripristino potrebbe avere esito negativo o richiedere molto tempo se viene usata la notifica delle query in un database.

Versione originale del prodotto: SQL Server
Numero KB originale: 2483090

Sintomi

È possibile notare uno o più dei sintomi seguenti con un database configurato per le sottoscrizioni di notifica delle query:

• Sintomo 1: il ripristino del database dal relativo backup potrebbe non riuscire con il messaggio di errore 1205 se NEW_BROKER opzione viene specificata durante l'operazione di ripristino. Inoltre, i file di dump verranno generati nella cartella Errorlog di SQL Server.

• Sintomo 2: il ripristino del database dal relativo backup ha esito negativo e il database diventa offline. Inoltre, i messaggi seguenti vengono registrati nel log degli errori di SQL Server:

  <Errore datetime> spid61: 9768, gravità: 16, stato: 1.
  <Datetime> spid61 Un utente di database associato alla conversazione sicura è stato eliminato prima che le credenziali fossero state scambiate con l'endpoint lontano. Non utilizzare DROP USER durante la creazione di conversazioni.
  <Datetime> spid61 Non è stato possibile verificare la presenza di notifiche di query in sospeso nel database "5" a causa dell'errore seguente durante l'apertura del database: 'Un utente di database associato alla conversazione sicura è stato eliminato prima che le credenziali siano state scambiate con l'endpoint lontano. Non utilizzare DROP USER durante la creazione di conversazioni. Operazione di pulizia delle sottoscrizioni di notifica delle query non riuscita. Per informazioni dettagliate, vedere gli errori precedenti.
  <Errore datetime> spid61: 9001, gravità: 16, stato: 5.
  <Datetime> spid61 Il log per il database 'Test' non è disponibile. Controllare il registro eventi per i messaggi di errore correlati. Risolvere eventuali errori e riavviare il database.
  <Errore datetime> spid61: 3314, gravità: 21, stato: 4.
  <Datetime> spid61 Durante l'annullamento di un'operazione registrata nel database 'Test', si è verificato un errore in corrispondenza dell'ID record del log (1835:7401:137). L'errore specifico viene in genere registrato in precedenza come errore nel registro eventi di Windows. Ripristinare il database o il file da un backup oppure correggere il database.

  Note

  È possibile che si verifichi il problema durante la fase di ripristino del database. Il ripristino viene eseguito anche in un database quando il database viene portato online, il server viene riavviato e così via.

• Sintomo 3: Il ripristino del database dal relativo backup può richiedere molto tempo e i messaggi simili ai seguenti vengono registrati nel log degli errori di SQL Server:

  Impossibile inviare un messaggio nella finestra di dialogo '{ ID finestra di dialogo }.'. Il recapito non è riuscito per la notifica '?<qn:QueryNotification xmlns:qn="https://schemas.microsoft.com/SQL/Notifications/QueryNotification" id="2881" type="change" source="database" info="restart" database_id="7" sid="0x010500000000000515000000FA48F22A6990BA52422C73DFF9030000"><qn:Message>4a4c696b-645c-40fd-bfef-4f2bc7c599b4; eb99973e-3cc9-4c7e-b4b9-47d8cf590c43</qn:Message></qn:QueryNotification>' a causa dell'errore seguente in Service Broker: 'The conversation handle "<Conversation Handler>" is not found.'.

  Note

  È possibile che si verifichi il problema durante la fase di ripristino del database. Il ripristino viene eseguito anche in un database quando il database viene portato online, il server viene riavviato e così via.

Causa

Causa del sintomo 1: quando si specifica NEW_BROKER opzione durante l'operazione di ripristino, SQL Server tenta di troncare tutte le tabelle correlate a Service Broker. Il troncamento richiede SCH_M blocco sull'oggetto troncato. La transazione principale contiene quindi un blocco SCH_M in sysdesend. Quando un database viene ripristinato o ripristinato, per impostazione predefinita SQL Server tenta di attivare tutte le notifiche di query in sospeso, che richiede l'inserimento di righe(messaggi) nella tabella sysdesend. Questa operazione richiede un blocco SCH_S sulla tabella. Tuttavia, questa operazione si verifica in una transazione diversa e il tentativo di acquisire SCH_S blocco viene bloccato dal blocco SCH_M mantenuto dalla prima transazione. Di conseguenza, il thread che esegue il ripristino è ora bloccato su una risorsa proprietaria, situazione nota come self-deadlock. Il deadlock viene rilevato dal monitoraggio del deadlock e il thread viene terminato, terminando così l'operazione di ripristino.

Per altre informazioni sui blocchi, vedere Modalità di blocco. Gli altri sintomi descritti nella sezione Sintomi sono causati da problemi noti documentati negli articoli di correzione indicati nella sezione Risoluzione di seguito.

Risoluzione

Soluzione alternativa per sintomo 1: è possibile risolvere il problema abilitando il flag di traccia a livello di sessione 9109 prima di tentare l'operazione di ripristino. Di seguito è riportato uno script di esempio:

dbcc traceon (9109)
go
RESTORE DATABASE [Test] 
FROM DISK = N'C:\TestBackup.bak' WITH FILE = 1, 
MOVE N'test_Data' TO N'C:\test.mdf', 
MOVE N'test_Log' TO N'C:\test_1.ldf', 
NOUNLOAD, 
STATS = 1, 
NEW_BROKER
go
dbcc traceoff (9109)
go

È anche possibile evitare completamente il problema non specificando l'opzione NEW_BROKER nell'operazione di ripristino e usare ALTER DATABASE invece con NEW_BROKER opzione dopo il ripristino del database.

Per altre informazioni, vedere DBCC TRACEON - Flag di traccia (Transact-SQL).