Archiv der Kategorie: SQL Server

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.

SQL Server

» Database Corruption Worksheet: Steve Stedman

Schreibe einen Kommentar

» 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.

SQL Server

AlwaysOn Availability Groups Real-Life Lessons Learned (Video) – by Brent Ozar Unlimited®

Schreibe einen Kommentar

„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®.

Performance, SQL Server

SQL Server Trace Flag 272 : Geek City: Lost Identity

Schreibe einen Kommentar

I too used to belong to the crowd thanking Microsoft for the IDENTITY column type, I came from an MSACCESS background around about the end of SQL Server 7 (sic) and I was relieved to find I didn’t have to do any tedious „mucking around“ with identity value tables.

Of course, I also knew that there is no such thing as a free lunch and that SQL Server must be doing some housekeeping for me so it couldn’t be as performant as it would be with „raw“ data. But it was a heck of a lot faster than any T-SQL solution.

In SQL Server 2012 Microsoft, bless their cotton socks, „improved“ the IDENTITY column type and, naturally, in some minds broke it. Microsoft weren’t (all) born yesterday so they added a Trace Flag to enable you to get the old behaviour back. Nice. Here are some details:

Kalen Delaney : Geek City: Lost Identity.

Performance, SQL Server

SQL Server Trace Flag 8602

Schreibe einen Kommentar

This one is *great* even if you probably might never need it.

That having been said, I do come across some queries written by hot-shots who use hints in their queries…. and they often don’t maintain the queries when the table structure has changed making the hints counter-productive. How could they remember where they have used which hints and know which ones to fix? How will they find the time to do so? Being able to demonstrate that the hints are no longer correct without changing the source is a terrific way of avoiding the „you must have changed something else as well“ kind of reactions from  certain types of people.

SQL Server Trace Flag 8602.

SQL Server

What risks are there if we enable read committed snapshot in sql-server? – Database Administrators Stack Exchange

Schreibe einen Kommentar

Sometimes, you hear of something and you think „that’s just what I need!“ only to find that in the real world „things are a bit more complicated than you think“

READ_COMMITTED_SNAPSHOT

Is an example of this. At first sight I thought „why isn’t this the standard setting“ but this question-and-answer article might explain why…

via What risks are there if we enable read committed snapshot in sql-server? – Database Administrators Stack Exchange.