Archive

Archive for June, 2011

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.

Advertisements

Script for point-in-time recovery

June 15, 2011 Leave a comment

  

This is great script for a point-in-time recovery. Big ‘Thank You’ to Henning Frettem

(and Michael Valentine Jones, SQLTeam.com as, apparently, the original script author)

 

/*
Generate Point-in-time restore script, given only DBName and Point-in-time timestamp
Works only on SQL2008 databases!!

Script for generating restore script for full backup originally created by Michael
Valentine Jones, SQLTeam.com

The rest is created by Henning Frettem, http://www.thefirstsql.com
*/
SET NOCOUNT ON
GO

DECLARE
  
@DBName VARCHAR(200) = ‘MyDB’,
  
@PointInTime DATETIME = ‘2010-08-09 09:10:00’,
  
@Filename VARCHAR(200),
  
@tab VARCHAR(1) = CHAR(9),
  
@cr VARCHAR(2) = CHAR(13)+CHAR(10),
  
@Full_BackupStartDate DATETIME,
  
@Diff_BackupStartDate DATETIME,
  
@Log_BackupStartDate DATETIME,
  
@SQL NVARCHAR(MAX) =

BEGIN TRY
  
–> Performing some checks
  
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND TYPE = ‘D’ AND backup_start_date <= @PointInTime)
    
RAISERROR(N’No full backup exists prior to the specified PointInTime’, 16, 1)
  
ELSE IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND TYPE = ‘L’ AND backup_start_date > @PointInTime)
    
RAISERROR(N’No backup of the log exist after the specified PointInTime’, 16, 1)

  –> Getting the filename and the date of the last full backup prior to the PointInTime
  
SELECT TOP 1
    
@Filename = b.physical_device_name,
    
@Full_BackupStartDate = backup_start_date
  
FROM msdb.dbo.backupset a
    
INNER JOIN msdb.dbo.backupmediafamily b
      
ON a.media_set_id = b.media_set_id
  
WHERE a.database_name = @DBName
    
AND a.TYPE = ‘D’
    
AND a.backup_start_date <= @PointInTime
  
ORDER BY a.backup_start_date DESC

  –> Create temp-tables for file header information
  
DECLARE @header TABLE (
    
BackupName NVARCHAR(128),
    
BackupDescription NVARCHAR(255),
    
BackupType smallint,
    
ExpirationDate DATETIME,
    
Compressed bit,
    
Position smallint,
    
DeviceType tinyint,
    
UserName NVARCHAR(128),
    
ServerName NVARCHAR(128),
    
DatabaseName NVARCHAR(128),
    
DatabaseVersion INT,
    
DatabaseCreationDate DATETIME,
    
BackupSize numeric(20,0),
    
FirstLSN numeric(25,0),
    
LastLSN numeric(25,0),
    
CheckpointLSN numeric(25,0),
    
DatabaseBackupLSN numeric(25,0),
    
BackupStartDate DATETIME,
    
BackupFinishDate DATETIME,
    
SortOrder smallint,
    
CodePage smallint,
    
UnicodeLocaleId INT,
    
UnicodeComparisonStyle INT,
    
CompatibilityLevel tinyint,
    
SoftwareVendorId INT,
    
SoftwareVersionMajor INT,
    
SoftwareVersionMinor INT,
    
SoftwareVersionBuild INT,
    
MachineName NVARCHAR(128),
    
Flags  INT,
    
BindingID UNIQUEIDENTIFIER,
    
RecoveryForkID UNIQUEIDENTIFIER,
    
Collation NVARCHAR(128),
    
FamilyGUID UNIQUEIDENTIFIER,
    
HasBulkLoggedData bit,
    
IsSnapshot bit,
    
IsReadOnly bit,
    
IsSingleUser bit,
    
HasBackupChecksums bit,
    
IsDamaged bit,
    
BeginsLogChain bit,
    
HasIncompleteMetaData bit,
    
IsForceOffline bit,
    
IsCopyOnly bit,
    
FirstRecoveryForkID UNIQUEIDENTIFIER,
    
ForkPointLSN numeric(25,0) NULL,
    
RecoveryModel NVARCHAR(60),
    
DifferentialBaseLSN numeric(25,0) NULL,
    
DifferentialBaseGUID UNIQUEIDENTIFIER,
    
BackupTypeDescription NVARCHAR(60),
    
BackupSetGUID UNIQUEIDENTIFIER NULL,
    
CompressedBackupSize bigint,
    
Seq INT NOT NULL IDENTITY(1,1)
    )

  –> Create temp-table for db file information
  
DECLARE @filelist TABLE  (
    
LogicalName NVARCHAR(128),
    
PhysicalName NVARCHAR(260),
    
TYPE CHAR(1),
    
FileGroupName NVARCHAR(128),
    
Size numeric(20,0),
    
MaxSize numeric(20,0),
    
FileID bigint,
    
CreateLSN numeric(25,0),
    
DropLSN numeric(25,0) NULL,
    
UniqueID UNIQUEIDENTIFIER,
    
ReadOnlyLSN numeric(25,0) NULL,
    
ReadWriteLSN numeric(25,0) NULL,
    
BackupSizeInBytes bigint,
    
SourceBlockSize INT,
    
FileGroupID INT,
    
LogGroupGUID UNIQUEIDENTIFIER NULL,
    
DifferentialBaseLSN numeric(25,0) NULL,
    
DifferentialBaseGUID UNIQUEIDENTIFIER,
    
IsReadOnly bit,
    
IsPresent bit,
    
TDEThummbprint VARBINARY(32),
    
Seq INT NOT NULL IDENTITY(1,1)
    )

  –> Get header and filelist information from the backup file
  
INSERT INTO @header
  
EXEC (‘RESTORE HeaderOnly FROM DISK = ”’ + @Filename + ””)

  INSERT INTO @filelist
  
EXEC (‘RESTORE FilelistOnly FROM DISK = ”’ + @Filename + ””)

  –> Generate the full backup restore script
  
SELECT
    
@SQL = @SQL +
      
CASE
        
WHEN a.Seq = 1 THEN
          
@cr + ‘RESTORE DATABASE [‘ + c.DatabaseName + ‘]’ +
          
@cr + ‘FROM DISK =’ + @cr + @tab + ”” +
          
@Filename + ”” + @cr + ‘WITH’
        
ELSE
      
END
      
+ @cr + @tab + ‘MOVE ”’ + a.LogicalName + ”’ TO ”’ + a.PhysicalName + ”’,’
      
+
      
CASE
        
WHEN a.Seq = b.Seq THEN
          
@cr + @tab + ‘REPLACE, STATS = 5, NORECOVERY’
        
ELSE
      
END
  FROM
    
@filelist a
    
CROSS JOIN
      
(SELECT Seq = MAX(b1.Seq) FROM @filelist b1 ) b
    
CROSS JOIN
      
(SELECT DatabaseName = MAX(c1.DatabaseName) FROM @header c1) c
  
ORDER BY
    
a.Seq

  SELECT @SQL = @SQL + @cr + ‘GO’ + @cr + @cr

  –> Restore the last differential backup if it exists
  
SELECT TOP 1
    
@SQL = @SQL + ‘RESTORE DATABASE [‘ + @DBName + ‘] FROM DISK = ”’ + b.physical_device_name + ”’ WITH NORECOVERY’ + @cr + ‘GO’,
    
@Diff_BackupStartDate = a.backup_start_date
  
FROM msdb.dbo.backupset a
    
INNER JOIN msdb.dbo.backupmediafamily b
      
ON a.media_set_id = b.media_set_id
  
WHERE a.database_name = @DBName
    
AND a.TYPE = ‘I’
    
AND a.backup_start_date > @Full_BackupStartDate
    
AND a.backup_start_date < @PointInTime
  
ORDER BY a.backup_start_date DESC

  IF @Diff_BackupStartDate IS NULL
    
SET @Diff_BackupStartDate = @Full_BackupStartDate

  –> Generate all log restores except the last one
  
SELECT
    
@SQL = @SQL + ‘RESTORE LOG [‘ + @DBName + ‘] FROM DISK = ”’ + b.physical_device_name + ”’ WITH NORECOVERY’ + @cr + ‘GO’ + @cr,
    
@Log_BackupStartDate = a.backup_start_date
  
FROM msdb.dbo.backupset a
    
INNER JOIN msdb.dbo.backupmediafamily b
      
ON a.media_set_id = b.media_set_id
  
WHERE a.database_name = @DBName
    
AND a.TYPE = ‘L’
    
AND a.backup_start_date > @Diff_BackupStartDate
    
AND a.backup_start_date < @PointInTime
  
ORDER BY a.backup_start_date

  –> Generate last log restore script with the stopat command and recovery
  
SELECT TOP 1
    
@SQL = @SQL + ‘RESTORE LOG [‘ + @DBName + ‘] FROM DISK = ”’ + b.physical_device_name + ”’
      WITH RECOVERY, STOPAT = ”’
+ CONVERT(VARCHAR(20), @PointInTime, 120) + ”” + @cr + ‘GO’ + @cr
  
FROM msdb.dbo.backupset a
    
INNER JOIN msdb.dbo.backupmediafamily b
      
ON a.media_set_id = b.media_set_id
  
WHERE a.database_name = @DBName
    
AND a.TYPE = ‘L’
    
AND a.backup_start_date > @PointInTime
  
ORDER BY a.backup_start_date ASC

  PRINT @SQL

END TRY
BEGIN CATCH
  
PRINT ERROR_MESSAGE()
END CATCH

Categories: Backup and Recovery

Just another random stuff

June 15, 2011 Leave a comment

7 7-zips running simultaneously on four quad-core Intel Xeon E5440 system… Sweet!

Categories: Uncategorized

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

To switch or not to switch? And if to switch then what, a hardware platform or Database? And if to go with the second choice then should it be some “rare” one, to have more “fun”? Oh, no, rather not…

June 6, 2011 6 comments

Continuing the series of interesting points of view on RDBMS-related topics.

Argument about Oracle announcing desupport of HP-UX (Itanium) by Fernando Nunes

And my comment on that:

–===================================================================–

Interesting point. Can’t agree on the conclusion for, mainly, two reasons, though.

For one, hardware upgrades are not that “big of a deal” – it has to be done every once in a few years anyway, so there’s nothing wrong with switching from HP-UX on Itanium to Unix(Solaris)/Linux on whatever other platform; there are plenty.

For two, – resources. “Sad but true” – there is a kvazillion resources which come up for SQL Server and Oracle from just googling practically anything in regards to either of these RDBMSs, and… almost none for Informix. It seems like the product’s been completely abandoned or, otherwise, left behind very profoundly. Well, apparently it is quite a story to get  free  ODBC Informix drivers for Windows 7 64-bit – there are simply none out there. There are some third-party ones, but that’s another story. You would think there must be any RDBMS-provider ones. Or if there still are, then why is it such an arduous task to find any?

Too bad ’cause even from reading bits and pieces of info I managed to find about Informix, it seems like some really good db, with great performance among other advantages.

As both Oracle and later SQL Server DBA – never came across Informix before in 10 years of my experience anywhere, i.e. lots of Oracle, lots of SQL Server, quite a few MySQLs, some DB2 here but… no Informix. So, if the popularity and widespread is not there, who would want to switch to Informix rather than switch just to other hardware, which will have to be upgraded (read changed) anyway? On my strong opinion, Informix, as for now goes exclusively as upgrade for any existing projects, any IBM products db backend or any new projects which are run by people who come from Informix side. Can’t really think of anyone planning to migrate to Informix all over a sudden, especially from such main players as Oracle and SQL Server.

There is also a third reason. It’s very much subjective yet seems pretty accurate as soon as many agree on that: IBM support is probably the worst and most definitely one of the worst out there. Dealing with IBM is totally pain in the neck. Even judging by my personal experience with Cognos – before IBM bought it, Cognos Support was awesome, now it’s as bad as it can get. IBM for many and many years has been consistently proving itself as highly bureaucratic, awkward and sluggish company. Ending up with IBM being the major, and kind of a single capable resource out there to address any Informix issues, is the factor to reject the very thought of going with Informix, no matter how great RDBMS it is.

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 );