Resolve The Problem Of Starting MSSQL Services

This SQL Server update 1617 changes the schema of the master, msdb and other system databases used by SQL Server. It changes the version of SQL from 10.50.someversion.0 to 10.50.1617.0

~~If there is ANY corruption in those databases prior to the update being applied, the update fails and leaves the server in an UNUSABLE state.~~ The SQL Service starts, then stops.

When the SQL Server starts again, the SQL Service will start, then stop. The event viewer will show error 912:

Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 15281, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

UPDATED: 2011-06-23. My original solution (see below) was to remove the update, restore from backup which worked. Another reader of my Blog was able to find the real root cause of the error – the DefaultData and DefaultLog registry settings located under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer

If either of those paths do not exist, or they exist but in the registry the values end in a backslash, then the SQL service will not start. On one of my dev machines the path was there, but ended in a backslash and removing the backslash was all I needed to do to get it working again!

UPDATED: 2011-06-24: After learning about the path issue above, I went back and checked the Registry Paths on my other customer’s SQL Servers that I had fixed with the method described below. It turns out that on those customers the paths already were correct and valid, so while the registry paths issue could be one cause, sometimes the solution I originally outlined below is still required.

Here’s the original solution that worked for me also – but did not address the registry issue:

To solve the problem, here’s what I did. Your solution may be different:

1. Remove the update:

Since this update changes the schema of the master and msdb databases, you won’t be able to restore any backup you have of them unless you first remove the update. If you skip this step your restore operations will fail.

Programs and Features – choose the option to view installed updates. Sort the list by Install Date.

Select the GDR 1617 update and choose the uninstall button.

2. Put SQL into special recovery mode:

Open the SQL Server Configuration Manager, right-click on the instance in question, properties:

Add -T902;in front of the other startup parameters – DO NOT FORGET THE SEMICOLON! ;

3. Start the SQL Service

4. Connect using Management Studio, then open Query Window

5. Run SQL Command DBCC CheckDB() for each of these databases:

a. Master

b. MSDB

6. If any of these report consistency errors, then restore them from backup. Then run DBCC CheckDB() again.

a. If the files are still corrupt, then you may have to run DBCC CheckDB(msdb, allowdataloss option here syntax in checkdb results from previous step). (Only do this if you have no choice – i.e. because you don’t have a non-corrupt backup).

On my server, neither master nor msdb reported any corruption. I just had to restore the master database from backup.

To do that:

1. Go back into the service properties startup parameters and also add a -m;

So now it starts with:

-m;-T902;

Start SQL

Open management studio

Restore the master database:

Note: with master it won’t appear in the list – you have to type “master” yourself.

Turn on the option to Overwrite the existing database (WITH REPLACE).

When the master database is done restoring, the SQL Service will automatically stop. Since you’re doing this from the graphical user interface it will appear as if it failed. IT DID NOT FAIL.

Open the startup parameters again:

Remove the -m;-T902; parameters you added earlier.

Start the SQL Service.

If the SQL Service stays up you’re done. You’ve recovered from the issue.

SQL Server

June 17, 2011

8 Comments

FTP 550 Access is Denied error

I recently installed a new FTP Server on one of my servers and could connect without issue and download files, but whenever I went to “PUT” a file, I’d get a 550 Access is Denied error.

After an hour or so of digging around (first wasting time on file system permissions and local computer firewall rules) I realized that the issue was my ISA 2006 server publishing rule. My rule was configured using the “FTP Server” Traffic rule:

What I had forgotten is that by default, when you publish an FTP site using this rule, it automatically sets another property (which is NOT visible on any of the properties tabs) to mark the FTP site as read only.

To get to the hidden property you have to finish the rule, then right-click it, and choose the “Configure FTP” Filter option:

You then have to clear the checkbox from the “Read Only” property:

As soon as that’s done, apply the rule and the file uploads will start working.

ISA Server 2006

May 26, 2011

Adding Full Text Search to SQL 2008 Express

Recently I had a customer who had installed SQL 2008 Express, and then wanted to install the Full Text option, but when they went to the add features the feature was missing.

So we downloaded the SQL 2008 Express with Advanced Services installer. When you run that installer and try to add features to the existing installation:
it didn’t display the Full Text option, or Reporting Services option or anything else that is part of the Advanced tools.

If you re-run the setup and choose the option to create a new instance those features do show in the list of available options. So what gives?

It turns out that Microsoft’s installer considers the SQL Express (without the advanced tools) to be a completely different Edition than the SQL Express (with advanced tools). It’s like you had the Standard Edition installed and then ran the Enterprise edition setup and wondered why you couldn’t turn on the Enterprise features. The answer is because the edition you’re trying to maintain isn’t the same as the installer – so it only shows you those features that DO exist for that edition.

In my real-world example, I can’t add the full text option from the “with advanced services” version of SQL Express to the standard SQL Express I had installed.

The solution that doesn’t require un-installing and re-installing: On the main SQL installer screen choose the MAINTENANCE option, then choose the EDITION UPGRADE OPTION.

Here are the steps to follow broken down:

1. First – download the SQL 2008 Express with Advanced Services. If you don’t have it yet, just go to www.microsoft.com/downloads and do a search for SQL 2008 Express with Advanced Services – or visit this blog post and click the link you need: http://blogs.msdn.com/b/sqlexpress/archive/2009/06/15/installing-sql-server-2008-guidance.aspx

Once you’ve downloaded the file, you’ll notice it is much bigger than the regular SQL Express installer and has the letters “ADV” in the filename:

In this screen capture – notice that the filename is different and that the Size is VERY different – make sure you’re running the “ADV” file – the bigger one.

2. Choose the “Maintenance” option – not the “Installation” option, then choose “Edition Upgrade”:

Source

http://darrenmyher.com

کلمات کلیدی:

نوشته شده توسط Abteen 94/10/9:: 11:11 صبح | () نظر