Archive

Archive for the ‘General Administration’ Category

SQL Server service fails to start after Service Pack installed

June 23, 2011 2 comments

Yesterday hit the situation when SQL Server service wouldn’t start after installation of GDR 1617 for SQL Server 2008 R2 (RTM). In case you wonder about what the heck GDR is (I did!), here and here.

One of the error messages in Windows Application Log pointing to the master database corruption, suggesting its rebuild.

It’s one of those classic cases when error message gives you solution hints, which are totally wrong. Well, following the path of uninstalling the service pack, rebuilding the master database with its subsequent restore from backup will do it, but… The real problem cause is totally different from what is suggested by this error message, so is the right solution – it’s all about changing couple of registry keys values.

Note: The same situation is possible to occur after ANY service pack installation, not just this specific GDR one.

It happens because during the upgrade process, a temporary database is created,

then a script runs against msdb database, you would think – according to the script’s file name: sqlagent100_msdb_upgrade.sql

and, as a result, the final error says master database is corrupted and needs to be rebuilt. Very logically consistent, I know. And this, once again, can happen after ANY service pack installation because this is the “shared” behavior. I’d rather say A FEATURE.

Don’t fall into this trap of uninstalling the SP, rebuilding the master db and then restoring it from the latest backup.

Instead, just check two registry keys values and change then accordingly:

That does it. Now you can start the SQL Server service, no need to change any startup parameters. It will start as usual.

The problem’s solved.

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

June 14, 2011 3 comments

Just came across this peculiar SSMS error while trying to view a Job’s steps

TITLE: Microsoft SQL Server Management Studio

——————————

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

——————————

ADDITIONAL INFORMATION:

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

——————————

BUTTONS:

OK

——————————

And found a simple solution here: http://connect.microsoft.com/SQLServer/feedback/details/569732/creating-an-instance-of-the-com-component-with-clsid

It only takes to register dts.dll with regsvr32 utility:

 

This, however, works in a random way – sometimes it does, sometimes it doesn’t. When it doesn’t, the only solution as far as I understand is to restart SSMS. Not comfortable, especially if you need to open several server nodes or multiple categories inside a single node. Let’s hope they will fix it in the next SQL Server version.

Enabling FILESTREAM

June 9, 2011 Leave a comment

When it comes to enabling FILESTREAM feature on a new instance of SQL Server 2008 (FILESTREAMs is new SS2K8 feature), you might come across this commands, which are supposed to do the job:

EXEC sp_configure filestream_access_level, 1

RECONFIGURE

or

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

but they don’t – FILESTREAM remains disabled. Some posts claim it happens in Express Edition only, – nope. Enterprise Edition as well.

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

 
 
There is “the GUI way” too, with SSMS:

 

But neither of this will work

 SELECT SERVERPROPERTY ('FilestreamConfiguredLevel'),SERVERPROPERTY ('FilestreamEffectiveLevel');

 will still return zeroes.

What needs to be done is this:

Start -> All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager

then right click on SQL Server service -> Properties

Go to FILESTREAM tab and check “Enable FILESTREAM for Transact-SQL access” and “Enable FILESTREAM for I/O streaming access”

Then restart the service

This will do it. Now if you run the check again,

 
SELECT SERVERPROPERTY ('FilestreamConfiguredLevel'),SERVERPROPERTY ('FilestreamEffectiveLevel');

it shows that FILESTREAM has been enabled.

 
After this, FILESTREAM filegroup can be created and datafile assigned to it:
 
ALTER DATABASE AdventureWorks2008R2
ADD FILEGROUP FILESTREAM_FILEGRP
CONTAINS FILESTREAM
GO

–Add a file for storing database photos to FILEGROUP
ALTER DATABASE AdventureWorks2008R2
ADD FILE
(
    
NAME= ‘FILESTREAM_01’,
    
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\filestream_01.ndf’
)
TO FILEGROUP FILESTREAM_FILEGRP
GO

 
 
Thanks for the final hint to Stuart’s Tech Punch

Changing your default settings for any new database is just compulsory

June 2, 2011 2 comments

What a statement, eh? All over a sudden and out of the blue. Never bothered to alter any default settings for a to-be-created database myself and haven’t heard others doing that on a large scale. However, after hitting this article by Glenn Berry, (which references couple of other related posts One | Two by Michelle Ufford and Kimberly Tripp) I realized this is serious. And I’m very serial about this! (“South Park” is sometimes retarded and disgusting, especially that first episode of 15th season, but in 9 out of 10 – it’s brilliant!)

In short, to summarize what the guys already said: excessive number of transaction log file extents, – Virtual Log Files, will lead to lengthy restore/recovery times. As simple as that. Besides, you want to save on execution time and server workload when having multiple Transaction Log File extents following one another during “heavy” DML ops. In addition to that, having multiple small VLFs in Transaction Log File will make it unnecessary big in size, which eventually will require its truncate and shrink. Too much hassle for such a silly reason as not-really-suitable default settings, which are very easy to change.

Hence, right after the new installation of SQL Server, go and change the model db datafile and transaction log file settings from default to… whatever you feel like after reading those two articles, but the main point is – CHANGE IT!

GUI-way:

Script-way:

USE [master];
ALTER DATABASE [MyNewDB] MODIFY FILE ( NAME = N'MyNewDB_Log', FILEGROWTH = 65536KB );

Export / Import Database Diagrams

May 12, 2011 2 comments

One of the developers in my IT dept asked how to export Database Diagrams. I never thought of it before, neither done or was asked to do it. So, Google as usual. Found several posts with the similar question – how do you export database diagrams in SQL Server 2000, 2005 and 2008. In SQL 2000, the diagram data was stored in a bunch of related tables, so the script which created the same tables on the “source” side and populated them using the right order of INSERT-AS-SELECTs did the job.

In SQL Server 2K5/2K8, a database diagram data is stored in a single user database table – sysdiagrams as binary(max) string.

However, it’s not that straight forward process. If you simply “copy” sysdiagrams table from “source” to “destination”, after you try to modify it, all the tables will be removed from this newly imported diagram.

This kind of behaviour is claimed by Microsoft to be designated – to preserve and follow the schema names notation.

First, the same table has to be created in “destination db”. That, actually, is taken care of SSMS. When you click at Database Diagrams, the following message pops-up: “This database does not have one or more of the support objects required to use database diagramming. Do you wish to create them?” – “Yes”, and the sisdiagrams table is created. The one and the only one – unlike SQL Server 2000, which, apparently had those multiple tables to overcome the limitation of binary(max) datatype. Second, Database Publishing Wizard has to be installed and ran with -schemaonly option to produce DDL file, and finally, the file has to be run in “destination” database.

Here are the detailed steps:

1. Download and Install MS Database Publishing Wizard to the default location (C:\Program Files\Microsoft SQL Server\90\Tools\Publishing)

 2. Script the “source” database: 

“C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz” script -d “C:\xyz.sql” -schemaonly

(NOTE: use -schemaonly option in order to produce DDL file only, – leaving out all the source db data)

 3. Create a blank, “DESTINATION” database

4. Go to Database Diagrams node under the new, click “Yes” to create the necessary objects (actually it’s the only one table called sysdiagrams)

5. use <DESTINATION>

go

6. Run c:\xyz.sql script

7. Finally, copy the “source” diagram data, – this time it will not through any message upon modification attempt:

USE <DESTINATION DB>;

SET IDENTITY_INSERT sysdiagrams ON

INSERT INTO sysdiagrams (name, principal_id, diagram_id, version, definition);

SELECT name, principal_id, diagram_id, version, definition
FROM ecometry..sysdiagrams;

SET IDENTITY_INSERT sysdiagrams OFF