Interview Question of the Week #022 – How to Get Started with Big Data?

Big data is one of the most popular subject in recent time and everybody wants to get started on this subject. During recent interviews there are plenty of the questions with related to Big Data. Here is the most popular question which I receive on this subject.

Question: How to get started with Big Data?

Answer: Earlier last year I wrote timeless series on the subject Big Data. Here is the link to the entire series.

 bigdataimages Interview Question of the Week #022   How to Get Started with Big Data?

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

SQL SERVER – Error Msg 511 using SQL Server Sparse columns

When I wrote the previous post around SQL SERVER – CREATE TABLE Failed Because Column ‘C1024′ in Table ‘myTable’ Exceeds the Maximum of 1024 Columns, I thought it was a simple concept. Lesser did I know that there can be something wrong. One of my blog reader said they were getting some sort of an error when adding a sparse column to a table. I was surprised to why one should get an error? I asked for further details because this was strange.

The mail stated they are getting:

Msg 511, Level 16, State 1, Line 6
Cannot create a row of size 10019 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.

Just like a strange error gets me going. I started to dig into why are they were getting this error. This lead to an interesting learning for me with sparse columns.

Using sparse columns reduces the maximum size of a row from 8060 bytes to 8018 bytes because of the additional overhead used when storing non-NULL values. However, for most of the normal scenarios this should not be a concern, since by definition, most of the sparse columns in a row will contain NULL values. Therefore, size reduction obtained by not storing any data for NULL values in a sparse column easily compensates for this fixed overhead. This still doesn’t tell us why we are getting the above error. Let us understand the basics.

When you convert a nonsparse column to a sparse column, the sparse column will consume more space for nonnull values. Therefore, when the row size is near 4,009 bytes, the operation can fail. The reason is when we modify an existing table to add our space column, the data pages need to have enough space to make a copy of the original row, make changes and then delete the original row. The error is because of insufficient space on the data pages. Let us do a simple script to get the error:

--Create a column of size 6000 (note this is NCHAR)
CREATE TABLE sparse_tbl (c1 NCHAR(3000))
INSERT INTO sparse_tbl VALUES (REPLICATE ('z',100))
--Modify the column to sparse
ALTER TABLE sparse_tbl ALTER COLUMN c1 ADD SPARSE

msg511 sparse 01 SQL SERVER   Error Msg 511 using SQL Server Sparse columns

A similar error is encountered if you go ahead and remove a sparse column from an existing table with data where the rows have more than 4009 bytes. In case of a new table, this is not encountered in general because there is no data to work with or move.

So the next question was, how can I alter or add or remove sparse columns which have data more than 4009 bytes then? In this case:

  • Create a new table for the operation
  • Insert into new table using the old table
  • Delete the old table and rename the new table

If we follow a process to do the same, there is nothing difficult. I have always felt there are ways to work with data and work around things. I am hope in future versions Microsoft might change this behavior but this is the current workaround. Hope you have never encountered this error before in your code. Let me know your thoughts.

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

SQL SERVER – Using 20 Logical Processors Based on SQL Server Licensing

It is second to human nature to utilize every bit of juice for what we pay. You buy something and you would like to get the best out of it right? If you start thinking from an organizations point of view, they think they should buy something which satisfies their needs. That is the only reason why organizations do capacity planning for their applications. Hardware resources are not cheap and it is difficult to over budget on them. Below is a real world query that had come to me.

One of my friend contacted me and told that he is using task manager and seeing that out of 64 processor only 20 processors are shown as utilized for a SQL Server machine. Whenever someone contacts me for any issue, I always want to know their environments details and ERRORLOG gives me a fair idea about it. If you don’t know where ERRORLOG is then you may want to read my earlier blog.

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

So, he sent me an ERRORLOG and I found below message over there.

SQL Server detected 8 sockets with 8 cores per socket and 8 logical processors per socket, 64 total logical processors; using 20 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

This explains the behavior which he is seeing but he also wanted to fix it. I went ahead and looked in the ERRORLOG’a again and found below on the top.

2015-05-13 11:00:54.72 Server      Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

This is an enterprise edition of SQL so I was expecting it to use all processors and full power of the machine. I searched licensing guide and found that from SQL Server 2012 onwards, there are two enterprise licenses – Core Based and SERVER/CAL Based. As per my research, CAL based license limits SQL Enterprise to use only 20 physical processors. If hyper-threading is enabled then it would be 40 logical processors.

We have identified that this behavior was correct as per license they have purchased. Since this was their most critical server, they have purchased core based license. They came back to me and asked – “How can we change the installed SQL Server to use Core Based license?”

Answer was simple – perform the edition upgrade using new media Here are the steps:

  • Launch the setup.exe from your setup media or network location.
  • Select Maintenance on the left pane of the setup dialog.
  • Select Edition Upgrade on the right pane.
  • After the standard pre-requisite check you will get a Product Key Page. You need to enter your new product key.

Once complete, the Errorlog showed below.

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

If you are using SQL 2012 onwards and having big machine, I would like you to check the ERROLROG and check if you are running into same issue. Else you can also run

SELECT SERVERPROPERTY('Edition')

Edition 01 SQL SERVER   Using 20 Logical Processors Based on SQL Server Licensing

This is mostly observed when there is an upgrade from earlier version of SQL Server because you might not be aware of that. Check and comment if this blog has helped you.

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

SQL SERVER – What are T-SQL Window Functions? – Notes from the Field #082

Kathi SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082[Note from Pinal]: In this episode of the Notes from the Field series database expert Kathi Kellenberger explains about easy and amazing functions of SQL Server. Kathi is an amazing instructor, she was the SQL author I have read in my early career. The reason, I love SQL Server because her writing has instigated love for this technology in me. Today she brings a relatively interesting topic for database experts. Read the experience of  Kathi in her own words.


The first set of T-SQL window functions have been available for 10 years, but many SQL Server professionals are not quite sure what window functions are. I am frequently asked by people I meet at conferences and customer sites “What are window functions?” I always answer with another question, “Do you use ROW_NUMBER?” Almost every time, the answer is “Yes. I didn’t know that was a window function.”

T-SQL window functions are different than other T-SQL functions because they operate over a set, or window, of rows. Notice that I am not capitalizing “window.” They have nothing to do with the Windows operating system. They are part of the ANSI SQL 2003 standard.

T-SQL window functions give you the ability to perform a calculation over the rows that are the result of the FROM, WHERE, GROUP BY and HAVING clauses. You can do things like include a column from a different row, calculate running totals, and perform summary calculations without losing details or grouping.

My favorite T-SQL window function is called LAG. LAG, introduced with SQL Server 2012, lets you bring in a column from a previous row. This is useful for calculating year over year performance, for example.

Here is the syntax of the LAG function:

LAG(<expression>[,offset][,default]) OVER([PARTITION BY <expression>] ORDER BY <expression>)

You must provide the column name that you wish to bring forward, and you must provide an ORDER BY expression. The ORDER BY is used to determine just which row is the previous row. By default, the value returned is from the previous row. By specifying an offset, you can go back any number of rows. You can also specify a default value to replace any NULLs that are returned.

Run the following script to create and populate a table for testing LAG.

CREATE TABLE #Sales (
SalesYear INT NOT NULL,
SalesQtr INT NOT NULL,
SalesAmt MONEY
CONSTRAINT PK_Sales PRIMARY KEY(SalesYear, SalesQtr));
INSERT INTO #Sales( SalesYear, SalesQtr, SalesAmt )
VALUES (2009,1,1400),(2009,2,2200),(2009,3,2800),(2009,4,1000),
(
2010,1,1200),(2010,2,2300),(2010,3,2850),(2010,4,900),
(
2011,1,1550),(2011,2,2600),(2011,3,2900),(2011,4,1100),
(
2012,1,1500),(2012,2,2500),(2012,3,2800),(2012,4,1000),
(
2013,1,1300),(2013,2,2800),(2013,3,2750),(2013,4,900),
(
2014,1,1550),(2014,2,2700),(2014,3,2700),(2014,4,1200);

The following query uses the LAG function with an offset value of four to return the sales from the same quarter of the previous year.

SELECT SalesYear, SalesQtr, SalesAmt,
LAG(SalesAmt,4) OVER(ORDER BY SalesYear, SalesQtr) AS PrevYearQtrSales
FROM #Sales;

notes 82 1 SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082

Figure 1: The partial results of using LAG

To calculate year-over-year sales, you subtract the previous year’s sales from the current sales and divide by the previous sales. To make the calculation a bit simpler, I’ll add the query to a CTE and perform the calculation in the outer query.

WITH Sales AS (
SELECT SalesYear, SalesQtr, SalesAmt,
LAG(SalesAmt,4) OVER(ORDER BY SalesYear, SalesQtr) AS PrevYearSales
FROM #Sales)
SELECT SalesYear, SalesQtr, SalesAmt, Sales.PrevYearSales,
FORMAT((SalesAmt - PrevYearSales)/PrevYearSales,'P') AS YOY
FROM Sales;

notes 82 2 SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082

Figure 2: Using LAG for year-over-year calculation

notes 82 3 SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082The LAG function is really easy to use, and it performs great. If you would like to learn more about LAG and all of the other T-SQL window functions, be sure to check out my new book: Expert T-SQL Window Functions in SQL Server.

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

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

Using Power of Web to Query Databases / Sources With Skyvia

Web technologies are moving at a pace that we can hardly keep up in pace. More and more of investments are going on in the space of web that even database developers like me find it interesting. In the same context, recently I was exploring on Skyvia (SQL SERVER – Integrate Your Data with Skyvia – Cloud ETL Solution) from ETL to how one might query and was surprised to see how some of these can be made useful.

This blog stems from a simple idea that was in my mind for a long time and I was struggling for a suitable solution till I saw what was available here. Think that you are a DBA and are on a vacation to a far-away land. You didn’t take your laptop along because you wanted to really enjoy your vacation times. Everything looks normal till you get an SMS from your management that something is wrong on one of your servers and they need 10-15 mins of your time to quickly resolve. Now with no laptop and all the cool things that you would have normally installed on your machine, how can you help your management with a few query support? Is it even possible? Assume you get to an internet kiosk and you want to do some amount of troubleshooting. How will you do? Well, there are a number of solutions.

One of the many available including the use of Skyvia to do query on your servers once you have done the simple step setup. In this example, I have used an SQL Server 2014 version available on a Cloud provider so that we can see what is going wrong.

Let us start by connecting to Skyvia and setting up our connections.

 Using Power of Web to Query Databases / Sources With Skyvia

In my example, I am going to use the “SQL Server” source and will give all the credentials. Once done and tested, we must see this in our connections tab.

 Using Power of Web to Query Databases / Sources With Skyvia

Once the connection setting seem to be in place, we are all set to run our queries on the backend. We can then head to the Query tab as shown below:

 Using Power of Web to Query Databases / Sources With Skyvia

At this stage, I just linked the Query window to my connection that we created in our previous step. This started to list our all the tables that were available on my server. This was really a cool moment because I was able to work on my database in less than 5 mins via the web.

 Using Power of Web to Query Databases / Sources With Skyvia

Obviously based on the permissions that I used to connect, the tables and objects are displayed. If there are 100’s of tables to work, then we can surely use the search to find the objects.

The next stage or table is our awesome query window. It is here that we can type in any SQL-92 query that needs to be fired against our database. Since I was wanting to know what was happening in my SQL Server system and what operators were functioning of few long running query, I used a DMV from SQL Server 2014 to check if it works. For my surprise, I was not disappointed.

 Using Power of Web to Query Databases / Sources With Skyvia

This is the query result output based on the query that I was running in the backend.

SELECT
node_id,physical_operator_name, SUM(row_count) row_count, SUM(estimate_row_count) AS estimate_row_count,
CAST(SUM(row_count)*100 AS FLOAT)/SUM(estimate_row_count)
FROM sys.dm_exec_query_profiles
GROUP BY node_id,physical_operator_name
ORDER BY node_id;

To know more about sys.dm_exec_query_profiles you can read from MSDN. I also talk about this DMV in my course at Pluralsight – SQL Server 2014 Administration New Features. The course is exhaustive in understanding some of these new capabilities of SQL 2014.

Coming back to our query, based on the output I was able to find out what was the query that was running behind the scene. As you can see this is a Dummy query without any filters or restriction on the columns selected. Hence we are seeing a number of Clustered Index Scan in our query output via the web.

SELECT      *
FROM DimProduct
INNER JOIN DimProductSubcategory
ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
INNER JOIN FactInternetSales
ON DimProduct.ProductKey = FactInternetSales.ProductKey

Once the query was identified, you can start looking at what is firing up the query and take corrective actions. Any normal SQL syntax can be fired like this via the web. It is simple and quite powerful to say the least.

The results from Skyvia is powerful that you can also use them to export to CSV format for later offline consumption. As part of simple testing, I also fired few DDL statements and was surprised to have almost everything I do via SQL Server Management Script window here via the web, anytime and anywhere I like.

And another thing I’d like to mention is that Skyvia Query is not just for SQL Server and other databases. Skyvia Query supports SQL for cloud CRMs, such as Salesforce, Dynamics CRM, etc, and allows using SELECT, INSERT, UPDATE and  DELETE statements against them. This enables admins, knowing SQL, work with cloud data in the same way as with SQL Server data.

Currently Skyvia is free for use. You can find more information on how to use their online SQL editor on the following page: Online SQL Query Editor for Cloud and Relational Data.

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

SQL SERVER – Identify Disk Latency on SQL Server Box Using PowerShell

Couple of week back, I was playing around with using Powershell for some simple activities. The fall out of that was the ability to search the SQL Server Error Logs. Though we can search for anything, I just showed you a case of usage. In this blog post, I will talk about one of the most sought after request for searching what the disk latencies are.

Disks are one of the most notorious when it comes to bring the server performance down. It is sometimes quite difficult to identify the problems. A lot of times, the first point of search would be using resource monitor. But in this blog, I thought let us try to automate this collection process using a PowerShell script. This ultimately shows how powerful this scripting language is and how one can use it to collect and process few Performance Counter values.

param (
    [string]$ServerName = "localhost",
    [int]$SampleFrequencySeconds = 10,
    [int]$CollectionDurationSeconds = 120
)

# do a check to ensure that collection duration is greater than
# or equal to the frequency rate
if ($CollectionDurationSeconds -lt $SampleFrequencySeconds) {
    Write-Error "CollectionDurationSeconds cannot be less than SampleFrequencySeconds"
    exit
}

# loop through all of the drives, sampling them
$DrivesOutput = for ($i = 0; $i -lt [int]($CollectionDurationSeconds / $SampleFrequencySeconds); $i++) {
    Get-Counter -ComputerName $ServerName -Counter "\LogicalDisk(*)\avg. disk sec/transfer" |
        Select-Object -ExpandProperty CounterSamples |
        Where-Object {$_.InstanceName -ne "_total"} |
        Select-Object InstanceName,
            @{Name = "Type"; Expression = {"LOGICAL"}},
            CookedValue

    Get-Counter -ComputerName $ServerName -Counter "\PhysicalDisk(*)\avg. disk sec/transfer" |
        Select-Object -ExpandProperty CounterSamples |
        Where-Object {$_.InstanceName -ne "_total"} |
        Select-Object InstanceName,
            @{Name = "Type"; Expression = {"PHYSICAL"}},
            CookedValue

    # Sleep for the specified frequency before continuing in the loop
    Start-Sleep -Seconds $SampleFrequencySeconds
}

# Group by the drive and Calculate the average for each drive we have
# round to the nearest [ms]
$DrivesOutput |
    Group-Object InstanceName, Type |
    Select-Object @{Name = "InstanceName"; Expression = {$_.Group.InstanceName[0]}},
        @{Name = "Type"; Expression = {$_.Group.Type[0]}},
        @{Name = "DiskLatencyMs"; Expression = {
            [int](($_.Group.CookedValue | Measure-Object -Average).Average * 1000)}
        } |
    Sort-Object InstanceName

As part of the script we have gone ahead and started our collection of counters once every 10 seconds. In this script, I have used 2 mins interval for the collection process. We have collected two counters – “\LogicalDisk(*)\avg. disk sec/transfer” and “\PhysicalDisk(*)\avg. disk sec/transfer”.

PowerShell DiskLatency 01 SQL SERVER   Identify Disk Latency on SQL Server Box Using PowerShell

I have gone ahead and shown a typical output from my PC. I have two physical drives and 3 logical drives. And we can see the disk latencies in (milliseconds).

I am sure the output would vary in your environments. Do let me know if such scripts are useful and you would want me to write more about them. I am slowly starting to love this scripting language that I plan to explore the various ways people use this powerful features.

Do let me know via comments some of the ways you have used PowerShell in your environments and which of those are even used in Production?

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

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)