Il ripristino o il ripristino potrebbero non riuscire o richiedere molto tempo se viene usata la notifica di query in un database

Questo articolo consente di risolvere il problema in cui il ripristino o il ripristino potrebbero non riuscire o richiedere molto tempo se la notifica di query viene usata 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 backup potrebbe non riuscire con un messaggio di errore 1205 se durante l'operazione di ripristino viene specificata NEW_BROKER opzione. Inoltre, i file di dump verranno generati nella cartella Errorlog del SQL Server.

• Sintomo 2: il ripristino del database dal backup non riesce e il database passa offline. Inoltre, i messaggi seguenti vengono registrati nel log degli errori SQL Server:

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

  Nota

  È 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 backup potrebbe richiedere molto tempo e i messaggi simili ai seguenti vengono registrati SQL Server log degli errori:

  Il recapito della notifica della query SPID data/ora non è stato possibile inviare un messaggio nella finestra di dialogo '{ ID finestra di dialogo }.'. 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.'.

  Nota

  È 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 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 su sysdesend. Quando un database viene ripristinato o ripristinato, per impostazione predefinita SQL Server tenta di generare tutte le notifiche di query in sospeso, che richiede l'inserimento di righe(messaggi) nella tabella sysdesend. Questa operazione richiede un blocco SCH_S nella tabella. Questa operazione si verifica tuttavia 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 in una risorsa di cui è proprietario, situazione nota come deadlock automatico. Il deadlock viene rilevato dal monitoraggio deadlock e il thread viene terminato, terminando così l'operazione di ripristino.

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

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

Nota

Dopo aver ripristinato o ripristinato completamente il database, è consigliabile verificare che vengano attivate le notifiche di query. Il modo più semplice per ottenere questo risultato consiste nel modificare lo stato del database in Sola lettura e modificarlo nuovamente in Lettura-scrittura. Altri modi per verificarlo includono lo scollegamento e il ricollegamento del database, il riavvio SQL Server e così via.

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

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