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.
- The „owner“ will be the User performing the restore,
- Trustworthy will always be false,
- 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 ;
ALTER DATABASE msdb SET ENABLE_BROKER ;
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
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.
The next time anyone tells you that they have built a „Secure“ system, get them to read this article.
Source: BAE Systems Threat Research Blog: Two bytes to $951m
An unknown number of people with a lot of easily acquired knowledge, but not so common skills, developed and deployed a system of hacks to manipulate a „secure“ banking system to transfer almost a billion dollars to accounts unknown. A large amount of this money is now „accounted for“, whatever that means, while 81 Million Dollars are still missing. That looks like a pretty good return on investment if you’re not too worried about illegalities or you consider it just liberating the money from robber banks.
Of course, many will say „our security standards are better than those in Bangladesh“ but those people are just trying to reassure those who cannot, for whatever reason, understand the signals generated by this particular case.
An incredible number of systems rely on the key system-parts misused in this hack. I would not have believed it until I saw it, so many systems rely on reading and writing text files from some location, often a network share, and these files are easily manipulated if due attention is not paid to the security of those files.
Just last week I had an application development team who could not, or would not because of time constraints, understand why it was a bad idea to let a server inside the secure zone reach out to a server on the internet in order to automatically download some code. The number of ways in which their deployment scheme could be hacked into and misused was mind-boggling and yet, they considered it not only safe, but also perfectly normal. Someone is going to make a lot of money out of their overly relaxed attitude if they should draw the wrong kind of attention.
More than interesting take on the „we have to have full control of our DNS“ discussions found in many companies. There are of course many reasons why people inside the organisation will view moving DNS Services to some provider as wrong, such as job security but as more-and-more companies succumb to the „Cloud“ hype, the more relevant „Besides, as organizations continue to move their services to the cloud, why would you have the name resolution of those resources tied to some legacy, on-premise server?“ is going to become.
Quelle: Why You Shouldn’t Be Hosting Public DNS | Postmodern Security
A recent issue we faced was SQL Server blocking by a session that had a negative SPID value. In this tip the author (not myself) looks at how to kill sessions with one source of negative SPIDs.
Quelle: How to Kill a Blocking Negative SPID in SQL Server
» Database Corruption Worksheet Steve Stedman.
I was going to write that I’vebeen very lucky in not having to deal with any corrupt MS SQL Server Databases until now but, given the rock solid MS SQL Server, it’s not unusual to never see any corruption.
If I ever *do* have to recover an MS SQL Server Database, I’d be looking to start with the Worksheet from Steve Stedman and go on to trawling his and Brent Ozar’s and Paul Randall’s other stuff for methods.
So, I came out of a meeting this morning where there was a large amount of ITIL type talk about „Tickets“, „Incidents“, „Changes“, „Emergency Changes“, and whether an Incident was documentation enough to mean not having to also create a(n emergency) Change Request“.
I went to the Ceramic Department afterwards and there I heard that Günther Grass had died so I started to wonder whether the grim Reaper did his job driven by „incidents“ (someone died, go deal with it) or „Changes“ (someone’s time has come, go and change the state from living to dead.) This led me to wonder, if, just if, this was a „change request“ scenario, whether that would require someone/-thing to approve the change and whether with an appropriate Lobby the Change could be rejected. I was left with a profound feeling of, well, if there is an afterlife, they’d better have their ITIL stuff together, because otherwise I’m going to get some relevant, inadequately documented, „changes“ revoked. Maybe starting with John Lennon’s shooting.
„AlwaysOn“ and „High Availability Groups“ were among the features that sounded just *so* great when first announced. Unfortunately, as insanely great as they sound and can be, there’s a lot to think about and to watch out for. Here’s Brent Ozar’s entertaining (as always) look at „Real“ Life.
AlwaysOn Availability Groups Real-Life Lessons Learned (Video) – by Brent Ozar Unlimited®.
Disney’s $1 Billion Bet on a Magical Wristband | WIRED.
This is what technology should be doing, „removing the friction“ as they put it. This is also where Apple used to be, innovative, advanced and friction free.
In an earlier Job I worked on a lot of this kind of Technology, we just didn’t have the same level of financial backing… amongst other things…