Wednesday, November 27, 2013

SQL Server 2008 R2 Installation Error: The MOF compiler could not connect with the WMI server

SQL Server 2008 R2 Installation Error: The MOF compiler could not connect with the WMI server

The following error has occurred while Installing SQL Server 2008 R2
2013-11-27 02:08:11 Slp: Error: Action "Microsoft.SqlServer.Configuration.MsiExtension.SetFeatureInstallStateAction" threw an exception during execution.
2013-11-27 02:08:11 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: MsiGetProductInfo failed to retrieve ProductVersion for package with Product Code = '{A2122A9C-A699-4365-ADF8-68FEAC125D61}'. Error code: 1608. ---> System.InvalidOperationException: MsiGetProductInfo failed to retrieve ProductVersion for package with Product Code = '{A2122A9C-A699-4365-ADF8-68FEAC125D61}'. Error code: 1608.
2013-11-27 02:08:11 Slp:    at Microsoft.SqlServer.Configuration.MsiExtension.SetFeatureInstallStateAction.FillInstallPropertyByProductCode(PackageInstallProperty pkg, String prodCode)
2013-11-27 02:08:11 Slp:    at Microsoft.SqlServer.Configuration.MsiExtension.SetFeatureInstallStateAction.FillInstallProperty(PackageInstallProperty pkg)
2013-11-27 02:08:11 Slp:    at Microsoft.SqlServer.Configuration.MsiExtension.SetFeatureInstallStateAction.ExecuteAction(String actionId)
2013-11-27 02:08:11 Slp:    at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
2013-11-27 02:08:11 Slp:    at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun)
2013-11-27 02:08:11 Slp:    --- End of inner exception stack trace ---

The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start.


Workaround / Solution
If this error occurred while installing SQL Server 2008 R2, try below steps.

Step 1: Open command prompt with administrator privileges.
Step 2: Run below command to stop the WMI services.
net stop winmgmt
Step 3: Rename folder windows\System32\Wbem\Repository to other one, for backup purposes, for Instance OLD_Repository.
Step 4: Run below command to start the WMI services.
net start winmgmt

Tuesday, October 22, 2013

Property Owner is not available for SQL Server Database error

Property Owner is not available for Database

Error Details:

Cannot show requested dialog.
Additional information:
  Cannot show requested dialog.(SqlMgmt)
    Property Owner is not available for Database'[      ]'. This property may not exist for this
    object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)


During one of Knowledge Transfer I tried to open the database properties but it was throwing error like below, so I had ran sp_helpdb command to check the database properties and noticed that the owner column was set to UNKNOWN as below. It seems the actual database owner left the company and dropped his login, that's the reason I got below errror.




Solution:
To fix this property owner of sql server database, we need to run below command on the server.

USE DATABASE_NAME
GO
sp_changedbowner 'Domain/User_Nmae'
GO

If your sql server database owner property is not located, then you won’t be able to launch the graphical interface of the database property page whether it's for, database properties, or database-mirroring, or log-shipping or or any other database property window.

Friday, September 13, 2013

Microsoft Compete with SAP HANA - Download SQL Server 2014 CTP 1


Microsoft SQL Server 2014 brings to market new in-memory capabilities built into the core database, including in-memory OLTP, which complements our existing in-memory data warehousing and BI capabilities for the most comprehensive in-memory database solution in the market.  SQL Server 2014 also provides new cloud capabilities to simplify cloud adoption for your SQL databases and help you unlock new hybrid scenarios.

  • Microsoft SQL Server 2014 Community Technology Preview 1 (CTP1) installation limitations:
  • Microsoft SQL Server 2014 CTP1 is pre-release software and should be installed only on a clean machine. It does not support upgrade from or side-by-side installations with any previous version of SQL Server, including SQL Server 2012. In addition it does not work with or support side by side installations with any client redistributables of SQL Server such as feature pack components, Visual Studio 2012 or earlier versions, SQL Server Data Tools, Data Tier Application Framework, etc.
  • To administer Microsoft SQL Server 2014 CTP1 use only SQL Server Management Studio that ships as a part of Microsoft SQL Server 2014 CTP1.
  • The Microsoft SQL Server 2014 CTP1 release is NOT supported by Microsoft Customer Services and Support (CSS).
  • The Microsoft SQL Server 2014 CTP1 release is only available in the X64 architecture.
  • The Microsoft SQL Server 2014 CTP1 release is a NON-PRODUCTION release and should not be installed and used in production environments.
  • The Microsoft SQL Server 2014 CTP1 release does NOT have an associated Microsoft SQL Server 2014 CTP1 Feature Pack.
  • The Microsoft SQL Server 2014 CTP1 release on Windows Azure VM is only intended for functional development and testing, and not for performance testing.
  • The Microsoft SQL Server 2014 CTP1 release contains branding and versioning from older Microsoft SQL Server releases.
So that all those of you whose fascination on this new technology, get your hands dirty through using the CTP version of 2014 which can be readily available for download at here.

Saturday, May 11, 2013

Unable to open physical file - Operating system error 5: 5(error not found) Microsoft SQL Server: Error 5120

Error:  Unable to open physical file - Operating system error 5: 5(error not found) Microsoft SQL Server: Error 5120

Reason of getting this Error: During attaching the database usually got this kind of error.

Work Around:
First we need to find out the service account which sql server runs under, here is the couple options for finding service account.


Option 1: Open SQL Server Configuration manager from start menu, and select sql server services, now you can see service account name on "Log On As" option below.



Saturday, March 30, 2013

Database 'distribution' does not exist.

When I try to configure replication, I get the error "Database 'distribution' does not exist. Make sure that the name is entered correctly."

I’m not able to use the distribution database because it doesn't exist, and I even can't create the distribution database because it already exists.

USE master
Go
Alter Database distribution Set Offline;
GO
Drop Database distribution;


Above script help you to remove distributor and then recreate them.
Could not locate entry in sysdatabases for database 'distribution'. No entry found with that name. Make sure that the name is entered correctly, does not exist in the database, sql server 2012, sql server 2008 R2, sql server 2005, sql server 2000, Msg 922, Level 14, State 1, Line 1, Database 'distribution' is being recovered. Waiting until recovery is finished. Msg 3148, Level 16, State 3, Line 1, This RESTORE statement is invalid in the current context. The 'Recover Data Only' option is only defined for secondary filegroups when the database is in an online state. When the database is in an offline state filegroups cannot be specified. Msg 3013, Level 16, State 1, Line 1, RESTORE DATABASE is terminating abnormally. distribution database cann't be dropped. Dropped Distribution database. Distribution database exists. unable to disable publishing and distribution‎ . Determining size of Distribution database‎ . How to clean Distribution database. Database 'distribution' cannot be opened due to inaccessible files or insufficient memory or disk space. Large Distribution Database. Drop Distribution DB. Publications. Subscriptions. jobs. agents.

Deprecated Feature - PIN and UNPIN a Table From Memory

How to PIN a Table into Memory?
The result will be that pinned Table pages will not be flushed out from memory, whatever comes in, will stay in memory. If the table is used frequently, the result is improved performance.

use AdventureWorks
GO
DECLARE @db_id int, @tbl_id int
SET @db_id = DB_ID('AdventureWorks')
SET @tbl_id = OBJECT_ID('AdventureWorks.Person.Address')
DBCC PINTABLE (@db_id, @tbl_id)


How to UNPIN a Table from Memory?
Very frequently used tables should be pinned. Performance improvement should be monitored. If no gain/improvement, pinning should be discontinued or else it will overhead to the memory

use AdventureWorks
GO
DECLARE @db_id int, @tbl_id int
SET @db_id = DB_ID('AdventureWorks')
SET @tbl_id = OBJECT_ID('AdventureWorks.Person.Address')
DBCC UNPINTABLE (@db_id, @tbl_id)




NOTE: This feature has been deprecated from 2005 onward, DBCC PINTABLE does nothing anymore.
Query Performance Tuning, SQL Server Query Performance Tuning, sql server query performance tuning tips, DBCC PINTABLE, SQL Server 2005 "Pin" data in Memory, Is there a way to force a table into memory, Keeping data available in the SQL Server data cache with PINTABLE, SQL Server Pin Table into Memory, sql server pin table in memory, dbcc buffer, dbcc dropcleanbuffers, SQL Server, Pinning Tables, DBCC PINTABLE UNPINTABLE, Store a table in a memory, Useful SQL Server DBCC Commands,

Saturday, March 2, 2013

Differences Between SQL Server Lower Version Vs Higher Version Installer Files

I found some Intresting differences between lower version SQL Server with Higher version SQL Server while installing sql server 2012, see below.

No 1. Installation center was not there till SQL Server 2005, from 2008 on-words this installation Center was introduced and you know how easy to install or add new feature with this tool. 

No 2. Till 2008 R2 we can see IA64 platform file on installer but from 2012 on-words it was not supporting, you can't find this file anymore, see below picture.



No 3. Till SQL Server 2008 R2 the Business Intelligence was not included with installer, but from 2012 on-words both engine and business Intelligence tool is coming together.

Difference Between Sql server Editions. Difference between SQL Server 2012 SP1 Express. Differences Between SQL Server Compact and SQL Server. Difference between sql server 2008 and sql server 2012. ifference between SQL Server Compact edition and SQL Server Express edition. difference between sql server 2005 and 2008 pdf. difference between sql server 2008 and 2012. difference between sql server standard and enterprise edition. difference between sql server and oracle. difference between sql server 2008 r2 and 2012. difference between sql server 2000 and 2005. difference between sql server 2008 and sql server 2008 r2. difference between sql server and mysql.