SQL SERVER – CREATE TABLE Failed Because Column ‘C1024′ in Table ‘myTable’ Exceeds the Maximum of 1024 Columns

Sometimes the best way to learn is by taking some of the capabilities to the extreme. This blog post is one of those explorations which I would like to share. A very long time ago I wrote a blog on this feature which was introduced in SQL Server 2008 called “Sparse Columns”. You can read below blogs to read about the fundamentals of SPARSE columns:

One of the thing which people have misunderstood is the number of sparse column we can have with a table. As per documentation we can go beyond 1024 column limit by using sparse columns. One of my blog reader has done some test and said that the statement is not true. Here is the script which we was using:

CREATE TABLE SomeTable (Id INT SPARSE NULL)
DECLARE @SN INT
SET
@SN = 1
WHILE (@SN <= 1030)
BEGIN
DECLARE
@CLN VARCHAR(100)
SET @CLN = 'C' + CONVERT(VARCHAR(100), @SN)
DECLARE @Query VARCHAR(MAX)
SET @Query = 'ALTER TABLE SomeTable ADD ' + @CLN + ' nvarchar(100) SPARSE'
SET @SN = @SN + 1
EXEC (@query)
END
GO

Above script is having a logic where it would create a table called SomeTable as below

CREATE TABLE SomeTable (Id INT SPARSE NULL)

And then it would keep adding columns using dynamic SQL

ALTER TABLE SomeTable ADD C1 NVARCHAR(100) SPARSE
ALTER TABLE SomeTable ADD C2 NVARCHAR(100) SPARSE
..
..
ALTER TABLE SomeTable ADD C1023 NVARCHAR(100) SPARSE
ALTER TABLE SomeTable ADD C1024 NVARCHAR(100) SPARSE

It fails with below error

Msg 1702, Level 16, State 2, Line 1
CREATE TABLE failed because column ‘C1024′ in table ‘SomeTable’ exceeds the maximum of 1024 columns.

As per https://msdn.microsoft.com/en-us/library/ms143432.aspx (Maximum Capacity Specifications for SQL Server) we should not have received the error because our table is a wide table.

Columns per non-wide table 1024
Columns per wide table 30,000

Here is the answer from Qingsong Yao (Microsoft)

In order to create more than 1024 columns, you have to have a columnset column defined in the table. Once the table has columnset defined, the select * will hide all sparse columns, and only see the column set. Meanwhile, people can still select individual sparse columns in their query.

So, the test done by my blog reader was having little problem. He forgot to define columnset column. Here is the sample of columnset with sparse column.

AllDetails XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

Let me give you a quick example.

SET NOCOUNT ON
GO
USE tempdb
GO
CREATE TABLE MySparseTable (
i INT
,Detail XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
,Height DECIMAL(10, 2) Sparse
,Width DECIMAL(10, 2) Sparse
,Length DECIMAL(10, 2) Sparse
,Color VARCHAR(10) Sparse
,Volume DECIMAL(10, 2) Sparse
)
GO
INSERT INTO MySparseTable (i,Height,Width)
VALUES (1,11.9,12.0);
INSERT INTO MySparseTable (i,Height,Length)
VALUES (2,2.8,9.3);
INSERT INTO MySparseTable (i,Color)
VALUES (3,'Red')
GO
SELECT *
FROM MySparseTable
GO
SELECT i
,Height
,Width
,Color
FROM [MySparseTable]
GO
/* Clean up time
drop table MySparseTable
go
*/

Here is the output.

Sparse 01 SQL SERVER   CREATE TABLE Failed Because Column C1024 in Table myTable Exceeds the Maximum of 1024 Columns

Have you ever use this feature in your environments? What has been your experience in using and what scenarios have you been using these capabilities? Let me know via your comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #021 – Difference Between Index Seek and Index Scan (Table Scan)

Question: What is the difference between Index Seek and Index Scan?

Answer: Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan  in the execution plan.

Here are few other related articles on this subject which you may find useful:

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Server – Knowing Deprecated or Discontinued Features Using Extended Events

This blog is in continuation to what I wrote couple of weeks back – SQL Server – Knowing the Use of Deprecated or Discontinued Features. The concept of using a deprecated feature is not by intensions by developers but is an accident for many. When I wrote that blog post, lesser did I know people are going to ask me for more. One of my reader mentioned using SQL Server Profiler to identify the same. Though this is a valid answer, I wanted to keep Profiler away for this and introduce him to a new method of using Extended Events for the same requirements.

With every new release of SQL Server, the investments that are going to Extended Events is amazing. So I think to bring the same in a step-by-step for folks who want to use the same for identifying Deprecated features. Let us work through the basic steps in setting this up next:

  1. Open the SQL Server Management Studio.
  2. Connect to the server next.
  3. Click the plus sign next to Management then click the plus sign next to Extended Events.
  4. Click on Sessions then right click on New Session Wizard
    deprecated xEvent 01 SQL Server   Knowing Deprecated or Discontinued Features Using Extended Events
  5. At this point the New Session Wizard screen should have launched. Once the New Session Wizard has launched, click Next.
  6. Type “Deprecated and Discontinued” in the text box after session name and click Next.
  7. Choose the radial button  Do not use a template then click Next
  8. In the Event library text box type ‘deprec’ and choose the deprecation announcement and deprecation final support events and then click the > arrow in the middle of the page to move them over as Selected events.
    deprecated xEvent 02 SQL Server   Knowing Deprecated or Discontinued Features Using Extended Events
  9. In the Capture Global Events section check the following:
  • client_app_name
  • client_connection_id
  • database_name
  • nt_username
  • sql_text
  • username
  1. Then click Next
  2. On the Set Session Event Filters screen click Next. We are not filtering this Event Session.
  3. Check the top check box next to Save data to a file for later analysis Make the Max File Size 5 MB with Rollover files as 20.
  4. Click Next.
  5. On the next screen click Finish on the Summary
  6. On the next screen click Close after the Event Session is successfully created.

The same can be scripted out using the script button in the wizard before you hit the finish button. The script for the same steps would look like:

CREATE EVENT SESSION [Deprecated and Discontinued] ON SERVER
ADD EVENT sqlserver.deprecation_announcement(
ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,
sqlserver.database_name,sqlserver.nt_usernamesqlserver.sql_text,
sqlserver.username)),
ADD EVENT sqlserver.deprecation_final_support(
ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,
sqlserver.database_name,sqlserver.nt_usernamesqlserver.sql_text,
sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'Deprecated and Discontinued',max_file_size=(5),max_rollover_files=(20))
WITH (STARTUP_STATE=OFF)
GO

If you want to use the same. Go ahead and start the session for Extended Events and start the collection process.

Extended Events are cool in a lot of ways. As part of my learning adventures, Extended Events are slowly getting into mainstream. Having said that, I have tons to learn from you. Do let me know how you use Extended Events in your environments and let me know via your comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Only Formatted Files on Which the Cluster Resource of the Server has a Dependency can be Used

Getting little help and learning from each other always helps. When I don’t get to know of something and I need to explore something, I try to check with my SQL friends who can guide me in the right direction. This blog is a classic example of me trying to learn something and help the community. Recently I got an email from my blog reader as below:

<EMAIL>

Hi Pinal,
My Name is Aysha and I have been a regular follower of you blog. Just to let you know that I am a hard-core developer, but also an accidental DBA. So please pardon me if this is a really simple issue for you. Also, I don’t have much knowledge about cluster. Here is the problem: I needed to create a new database on the SQL Server 2012 two node failover cluster to host database for new application under development. By following few internet articles, I added a new drive (M and N) into the SQL Group in the cluster, but I am unable to create database on the newly added drive.

Here is the command I was trying

CREATE DATABASE [TestDatabase]
ON PRIMARY
(NAME = N'TestDatabase', FILENAME = N'M:\SQLDATA\TestDatabase.mdf'  )
LOG ON
(NAME = N'TestDatabase_log', FILENAME = N'N:\SQLLOG\TestDatabase_log.ldf')

But here is the error I am receiving

Msg 5184, Level 16, State 2, Line 1
Cannot use file ‘M:\SQLDATA\Testdatabase.mdf’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Do you think I did something wrong? I have restarted SQL multiple times, but still same error. Can you please help?

Thanks
Aysha

<EMAIL>

Here is my reply.

<Reply>

Hi Aysha.

Thanks for contacting me and giving me all the details which I always expect from anyone who contacts me. Since you are new to cluster, you need to read a little more about the dependencies of cluster resource. Dependency decides the order in which they can come online. The child resource must come online before an attempt is made to bring parent resource online in a cluster.

  • The IP Address and the Disk Resource do not depend on anything.
  • SQL Network Name depends on the IP Address.
  • SQL Server depends on the Network Name and a Disk Resource.
  • SQL Agent depends on SQL Server.

Coming back to problem which you are facing (i.e. below error). Here is a breakup of error

Only formatted files on which the cluster resource of the server has a dependency can be used.
Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

See that, dependency terms in error message?

So, all you need to do is add dependency of disk M and N which you have added post installation, to the SQL Server resource. SQL Server is preventing us from creating databases on drive which aren’t a dependency of the SQL Server resource. This is to make sure that the disk is online before the SQL is trying to access database file.

Here are the steps

  • Open Failover Cluster Manager from Start Menu OR you can also go to Start > Run > Cluadmin
  • Select the clustered application, i.e. the group which has SQL Server.
  • Right-click on your “SQL Server” resource then choose Properties.
  • Go to Dependencies tab.
  • Click the Insert button to add an additional row, then select the shared disk (M) that is going to contain your SQL databases. In next row N also to be added.
  • Ensure that all resources are required (there is an AND next to subsequent resources) then click OK.

That’s it. Depending on the version of SQL, you might need to take SQL Server resource offline and bring it back online by choosing from right click.

Hope this helps!

</Reply>

Later she responded back and informed me that she was able to fix the issue after following the steps.

Home work: Can you help me out with this – which SQL version does not need restart after modifying dependencies of the disk? If you already have a working SQL cluster, please test and let me know by commenting. This was a great learning for me too, and felt I would quiz you with some research.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Adding Reference Data to Master Data Services – Notes from the Field #081

[Note from Pinal]: This is a 81th episode of Notes from the Field series. Master Data Services is one of the most important, but very little explored feature of SQL Server. If you have been reading this blog, when it is about BI, I always take help from LinchPin People who are BI experts. I requested Reeves from LinchiPin people to help me answer this unique question.

In this episode of the Notes from the Field series database expert Reeves Smith explains why one should add referencing data to Master Data services to our enterprise. Read the experience of Reeves in his own words.


Reeves Smith SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

In a previous post, I talked about Master Data Services and how it can enhance your Enterprise Data Warehouse. Through the Master Data Services add-in for Excel, the business can access and manage reference data without much involvement with IT. This might sound like a bad idea, but the subject matter experts (SME) for the data often do not reside in IT. SMEs should have the ability to manage reference data, and with the Master Data Services add-in for Excel they can.

In this post, I’m going to outline the three steps needed to get your data governance people up and running after Master Data Services has been installed. The three are:

  • Create a model to store the reference data (completed on the MDS website)
  • Format the reference data in Excel
  • Create an Entity within the Master Data Services add-in for Excel

IT will only need to perform the first step of the process. The first step to manage reference data is creating a container for the reference data. This step must take place within the Master Data Services website.

Step 1 – Create a model to store the reference data

  1. Open the Master Data Service’s website and Select System Administration, as you see in Figure 1.

notes81 1 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

Figure 1 – Master Data Services System Administration

  1. After you select System Administration, the website will list three menus: Model View, Manage, and System. Select Manage and then select Models from the pop-out menu.
  2. Click on the green plus sign to create a new model.
  3. In the Add Model dialog, which you see in Figure 2, add a model name that would be relevant to the appropriate reference data. In this example I will use Demo. Unselect all of the check boxes under the model.

 

Figure 2 – Add Model

  1. Test your model by connecting to it from the Master Data Services add-in for Excel.
    1. Open a blank Excel workbook and select the Master Data Services tab.
    2. Click the Connect button to create a new connection to your Master Date Services Instance.
    3. In the Master Data Explorer, which you see in Figure 3, select your newly created model.

Figure 3 – Master Data Explorer

notes81 2 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

Step 2 – Format the reference data in Excel

  1. Create an Excel worksheet with the formatted reference data and provide a header for each of the columns.

Note: Master Data Services requires a Code column and suggests the use of a Name column. Both of these columns do not need to be directly called Name and Code but should have like columns within the reference data. The code column must contain unique values.

notes81 3 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

Step 3 – Create an Entity within the Master Data Services add-in for Excel

For this example, I will create a list of customers from the Adventure Works database to enable the discussion of enhancements to the data set in a later post. Your reference data should be less complex then the customers list.

  1. With an open connection to Master Data Services and the worksheet with your reference data, click the Create Entity button, as you see in Figure 4.

notes81 4 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

Figure 4 – Master Data Services tab with the Create Entity highlighted

  1. Click the red arrow to the right of the Range text box and select the range of your reference data. Be sure to include the column headers and select the My data has headers check box, as shown in Figure 5.
  2. Select the newly created model; in my case that would be Demo.
  3. Add and new entity name and select the appropriate Code and Name column.

Note: Master Data Services 2012 and forward enables the use of an identity values for the Code if you do not want to manage unique keys.

notes81 5 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

Figure 5 – Create Entity Dialog

  1. Click OK.

notes81 6 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

At this point you should have a working model with a new entity that is fully managed within Master Data Services.

Summary

In this post, I have walked through the steps that are required to move reference data from an Excel workbook and create a model within Master Data Services. Under MDS you get all of the benefits of a managed master data management solution, like audibility and security.

What’s Next

In a coming post, we will walk through enhancing or reference data. One enhancement we will demonstrate, will include adding domain values within the Model. This helps the business users select the correct data elements and increase the data quality of your master data.

Hope this helps.

If you want to get started with BIML with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Puzzle with MONTH Function – Win USD 50 Amazon Gift Card

It has been a while since we ran the contest. I reached out to kind team of Embarcadero and they agreed to support the next contest. The contest has two steps and they are very simple. It took me a while to build contest, but it is a really fun one. I am very confident that once you try out the contest, you will love it.

Two Giveaways:

amazon gift cards SQL SERVER   Puzzle with MONTH Function   Win USD 50 Amazon Gift Card

(Global) USD 50 Amazon Gift Card to 1 Individual

(India) INR 2500 Flipkart Gift Card to 1 Individual

Contest 1: T-SQL

Run following T-SQL script and observe the answer:

SELECT MONTH(18/200), MONTH(200/18)

monthcontest SQL SERVER   Puzzle with MONTH Function   Win USD 50 Amazon Gift Card

When we look at the answer, it displays 1 as a answer to both of the expression. The question is why does above script return values as 1 instead of throwing an error or displaying any error?

Contest 2: Download and Install DBArtisan

This is the easy part of the contest. You just have to download and install DBArtisan. You can download DBArtisan from here.

How to Participate?

  • Leave an answer for contest 1 in the comment section of the blog.
  • Leave a comment with the same email address which you have used to download DBArtisan.
  • The contest is open till June 2nd, 2015 Midnight PST.
  • The winner will be announced on June 4nd, 2015.
  • There will be two winners 1) Global 2) India.
  • All the correct answer to the contest will be hidden till the winner is announced.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Antivirus Exclusions Best Practices With SQL Server

One of the classics as far as best practices is concerned is to exclude SQL Server files and folders on your antivirus programs running on the server. We all know this statements and often I see DBAs not adding any extra care to this recommendation. There are not many blogs that call them out explicitly.

Although antivirus software is a very important part of security planning, it is important to understand the effect it has on SQL Server files.  After rebooting a server, if the antivirus software locks a SQL Server file before SQL Server can gain access to that file, potential issues ranging from SQL Server not being able access those particular files to possible database corruption may occur. In a number of cases I have seen SQL Server refusing to start sometimes or the CPU / IO seems to be stalling sometimes. As you can see there are a number of these random errors one might get because of not implementing a simple best practice. Therefore, it is recommended that the following files be excluded from all antivirus scans:

  • SQL Server data files (typical extension is .mdf, .ndf, and .ldf)
  • SQL Server backup files (typical extension is .bak and .trn)
  • Full-text catalog files
  • Trace files (typical extension is .trc)
  • SQL Audit files
  • SQL query files (typical extension is .sql)
  • Directories holding Analysis Services data and partitions
  • The directory that holds Analysis Services temporary files used during processing
  • Analysis Services backup files

In addition to excluding SQL Server and Analysis Services files, it is recommended to exclude the following list of processes from antivirus scans:

  • SQLServr .exe
  • ReportingServicesService .exe
  • MSMDSrv .exe

For environments where SQL Server is clustered, exclude the C:\Windows\Cluster directory and the Quorum drive.

Though this list is not exhaustive, I am sure there might be a few more that I might have missed. This was a humble shot at this topic and I am sure as seasoned DBA’s you might have a little more to include. Do let me know under the comments section and I am sure this can be a great source of reference for others searching on this topic too.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Authority News – SQL Server 2014 Service Pack 1 Available for Download

Last month, Microsoft announced SQL Server 2014 Service Pack 1 and it had issues with SSIS catalog. Microsoft has once again released SQL Server 2014 Service Pack 1 earlier today and it is now available to download. I hope all the issue with earlier version has been taken care and the version is reliable to apply to production server. I strongly suggest that one should wait for a while before going ahead and applying the service pack. It is a good idea to wait and see if the version which we are installing is stable and there are not bugs. I always install, service packs on my development server before I install them on production servers.

Here are the links to download SQL Server 2014 Service pack 1

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #020 – What is the Difference Between DISTINCT and GROUP BY?

Question: What is the Difference Between DISTINCT and GROUP BY?

Answer: A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If all you need is to remove duplicates, then use DISTINCT. If you are using sub-queries execution plan for that query varies so in that case you need to check the execution plan before making decision of which is faster.

USE AdventureWorks2014
GO
-- Example of DISTINCT:
SELECT DISTINCT JobTitle, Gender
FROM [HumanResources].[Employee]
GO

interview 20 1 Interview Question of the Week #020   What is the Difference Between DISTINCT and GROUP BY?

USE AdventureWorks2014
GO
-- Example of GROUP BY:
SELECT JobTitle, Gender
FROM [HumanResources].[Employee]
GROUP BY JobTitle, Gender
GO

interview 20 2 Interview Question of the Week #020   What is the Difference Between DISTINCT and GROUP BY?

USE AdventureWorks2014
GO
-- Example of GROUP BY with aggregate function:
SELECT JobTitle, Gender, COUNT(*) EmployeeCount
FROM [HumanResources].[Employee]
GROUP BY JobTitle, Gender
GO

interview 20 3 Interview Question of the Week #020   What is the Difference Between DISTINCT and GROUP BY?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Service Pack Error – The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory

As a good SQL Server DBA, I always keep updating my SQL Server on my all VMs with the latest service pack available. During my last installation of service pack 2 on SQL Server 2012, I got below error.

The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.

I remember that I have played with some registry keys and now I realized that they are important keys. I looked into the logs based on MSDN articles and found below in Summary.txt file.

Detailed results:
Feature: Database Engine Services
Status: Failed: see logs for details
Reason for failure: An error occurred during the setup process of the feature.
Next Step: Use the following information to resolve the error, and then try the setup process again.
Component name: SQL Server Database Engine Services Instance Features
Component error code: 0x851A0043
Error description: The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.

Then I looked in the Detail.txt and found below:

(01) 2015-05-13 09:19:09 SQLEngine: --SqlEngineSetupPrivate: <strong>Validating path:E:\MSSQL\Data </strong>
(01) 2015-05-13 09:19:09 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing Validation and scenario Validation.
(01) 2015-05-13 09:19:09 Slp: The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.
(01) 2015-05-13 09:19:09 Slp: The configuration failure category of current exception is ConfigurationValidationFailure
(01) 2015-05-13 09:19:09 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing Validation and scenario Validation.
(01) 2015-05-13 09:19:09 Slp: Microsoft.SqlServer.Configuration.SqlEngine.ValidationException: The User Data <strong>directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.</strong>
(01) 2015-05-13 09:19:09 Slp: at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPrivate.CheckIfDirectoryExistsGeneric(String sqlPath, SqlEngineErrorCodes invalidError)
(01) 2015-05-13 09:19:09 Slp: at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPrivate.ValidateDataRegistryKeys(EffectiveProperties properties)
(01) 2015-05-13 09:19:09 Slp: at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPrivate.Patch(ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcb)
(01) 2015-05-13 09:19:09 Slp: at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.ExecuteAction(String actionId)
(01) 2015-05-13 09:19:09 Slp: at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.Execute(String actionId, TextWriter errorStream)
(01) 2015-05-13 09:19:09 Slp: The following is an exception stack listing the exceptions in outermost to innermost order
(01) 2015-05-13 09:19:09 Slp: Inner exceptions are being indented
(01) 2015-05-13 09:19:09 Slp:
(01) 2015-05-13 09:19:09 Slp: Exception type: Microsoft.SqlServer.Configuration.SqlEngine.ValidationException
(01) 2015-05-13 09:19:09 Slp: Message:
(01) 2015-05-13 09:19:09 Slp: The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.

I search for DefaultData key in registry and found at below location

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer

RegistryData 01 SQL SERVER   Service Pack Error   The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory

As we can see, it is pointing to E:\MSSQL\Data and it was not valid. Later I played with various other keys and found below possible errors.

Invalid Values in Registry Error Message
DefaultData The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.
DefaultLog The User Log directory in the registry is not valid. Verify DefaultLog key under the instance hive points to a valid directory.
SQLDataRoot The Database Engine system data directory in the registry is not valid.

SQLDataRoot in locating under HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\Setup

The cause of all errors was invalid path, which I have messed up. Later I search on the Microsoft site and found that there is a connect item filed by someone

https://connect.microsoft.com/SQLServer/feedback/details/778275/sql-server-2012-sp1-installation-issue

Moral of the story is never playing directly with the registry unless you are sure what they are. I was able to fix the error by correcting the value in DefaultData registry key.

Have you encountered such errors and were able to look at the logs?

Reference: Pinal Dave (http://blog.sqlauthority.com)