Archiv für den Monat: Oktober 2016

SQL Server

Server-level event notifications cannot be delivered

Schreibe einen Kommentar

This is the error message (or at least part of it) you will get after a restore of the msdb database has been carried out. The SQL Server Agent could not run.

Three things are changed (or at least look diffferent) after restoring a database.

  1. The „owner“ will be the User performing the restore,
  2. Trustworthy will always be false,
  3. Service Broker will always be disabled

(Caveat to point 1: the dbo alias is not changed, there you will find the original owner.)

It is point 3 which is important for the msdb. After restoring the msdb you have to re-enable service broker for the database:

USE master ;
 GO
 ALTER DATABASE msdb SET ENABLE_BROKER ;
 GO

Otherwise you will get (this time the full error message):

Server-level event notifications cannot be delivered. Either Service Broker is disabled in msdb, or msdb failed to start. Event notifications in other databases could be affected as well. Bring msdb online, or enable Service Broker

SQL Server

Can not start SQL Server, model db in RESTORING state

Schreibe einen Kommentar

Now that I have a few days free, it’s time to write-up some of the things I’ve noted to talk/write about over the last months.

 

One day, we had an instance reporting:

Error The database ‚model‘ is marked RESTORING and is in a state that does not allow recovery to be run. Error: 927, Severity: 14, State: 2. Database ‚model‘ cannot be opened. It is in the middle of a restore. Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

A colleague of mine was handling the case and discovered this error message once the monitoring had raised a problem with a (clustered) Instance not running.

Doctor Google gave my colleague:

Quelle: Can not start SQL Server, model db in RESTORING state

This article is interesting because one of the answers gives short, simple, and memorable descriptions of two Trace Flags:

The end of the story is that we did not use the methods in the article. Instead we renamed the „dead“ model database files and replaced them with copies of the installation template files.

The old, broken, model database files were kept for later analysis and I suspect they will remain there, untouched, until the instance is decommissioned.