SQL SERVER – 2016 IF EXISTS Functions on SQL Azure Databases and More

The experimentations of working with the next release of SQL Server is always fun and when I wrote the blog SQL Server – 2016 – T-SQL Enhancement “Drop if Exists” clause, lesser did I know such capabilities are already existing with Azure SQL DB. When I was presenting at one of the local usergroups, I told most of the capabilities come to Cloud first, get test and the very reason that it scales without problem on cloud gives one the confidence that it would work in our enterprise. One of the attendees asked, has all features first introduced to cloud only?

That got me curious and I started to hunt to find is the rollout always to cloud first. I thought let me experiment first and see. I took my DIE (DROP IF EXISTS) post to see if I can run it on SQLAzureDB to know if it worked seamlessly. First to my connection to the latest database I created.

 SQL SERVER   2016 IF EXISTS Functions on SQL Azure Databases and More

As I was running the CTP2.0 of SQL Server 2016, I wanted to see what version was running on cloud already. It was a surprise that the production was already running the latest bits. It was a pleasant surprise and I know by the time you read this article and try out – maybe Microsoft would have upgraded to the latest bits too. So is the scale and agility that cloud brings to the table.

Now coming back to the core, I was now getting a feeling that my DIE (DROP IF EXISTS) code will work now perfectly. I went ahead with the following code and it executed just fine.

DROP TABLE IF EXISTS my_test
GO
DROP PROCEDURE IF EXISTS my_test_procedure
GO

Now, it was time to search for something interesting that I can do on cloud that was not available on-premise SQL Server. So I went to the documentation to search for something interesting. I found a new function DATEDIFF_BIG() that was interesting. Currently the DATETIME functions that we use with SQL Server cannot give the high precision values. Let me take a typical value of – number of nanoseconds in a year?

SELECT DATEDIFF_BIG(nanosecond, '2015-1-1 00:00:00.0000000', '2016-1-1 00:00:00.0000000')
GO

This returns “31536000000000000” which is not available with SQL Server 2016 on-premise version currently. It was a great learning to see the rate at which innovations happen on cloud. If you run this on an on-premise SQL Server – you will get the following error today.

Msg 195, Level 15, State 10, Line 1
‘DATEDIFF_BIG’ is not a recognized built-in function name.

I am curious to know, how many times have you wanted the higher level of precision when working with DATEDIFF functions? What are some of your usecases for the same? I would surely like to learn some from you. Do let me know via the comments.

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

Interview Question of the Week #056 – How to fix Installation Failure – Rule “Setup account privileges” Failed in SQL Server

Sometimes in the interviews I observe that users are able to answer all the theoretical questions correct but when it is about doing practical they fail. A while ago, when I was helping a large organization with interview, suddenly their DBA reported that one of their server has installation error. We found this as a great opportunity to test a new candidate and gave him problem to solve. He was successfully able to solve the problem.

Question: How will you fix the installation failure error setup account privileges error in SQL Server?

setup rule 01 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

Answer:

When I clicked on “failed” hyperlink, here is the message.

setup rule 02 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

Here is the text of the message.

—————————
Rule Check Result
—————————
Rule “Setup account privileges” failed.
The account that is running SQL Server Setup does not have one or all of the following rights: the right to back up files and directories, the right to manage auditing and the security log and the right to debug programs. To continue, use an account with both of these rights. For more information, see http://msdn.microsoft.com/en-us/library/ms813696.aspx, http://msdn.microsoft.com/en-us/library/ms813959.aspx and http://msdn.microsoft.com/en-us/library/ms813847.aspx.
—————————
OK
—————————

I always look at setup logs to see the exact issue. I opened “SystemConfigurationCheck_Report.htm” and below is the failure.

setup rule 03 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

Then I looked into “Detail.txt” and searched for “HasSecurityBackupAndDebugPrivilegesCheck” as shown in above screenshot. Failure is listed below.

(09) 2016-01-28 19:58:11 Slp: Initializing rule      : Setup account privileges
(09) 2016-01-28 19:58:11 Slp: Rule is will be executed  : True
(09) 2016-01-28 19:58:11 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.FacetPrivilegeCheck
(09) 2016-01-28 19:58:11 Slp: Rule ‘HasSecurityBackupAndDebugPrivilegesCheck’ Result: Running process has SeSecurity privilege, has SeBackup privilege and does not have SeDebug privilege.
(09) 2016-01-28 19:58:11 Slp: Evaluating rule        : HasSecurityBackupAndDebugPrivilegesCheck
(09) 2016-01-28 19:58:11 Slp: Rule running on machine: PINALVM1
(09) 2016-01-28 19:58:11 Slp: Rule evaluation done   : Failed

As highlighted above, the account which was running setup was missing second one.

SeSecurity: Manage auditing and the security log

SeDebug: Debug Programs

SeBackup: Back up files and directories

To see the “Local Security Policy”, we can go to Start > Run > SecPol.msc or open it from Administrative Tools.

setup rule 04 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

“Debug programs” doesn’t have any user. By default, “Administrators” group is part of it which was removed by me during some hardening. Once I added default account, I have to restart the computer to get forward from that error.

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

SQL SERVER – Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’

I do apply patches to my SQL instances as and when they are released by Microsoft. This is important because I always feel keeping the bits uptodate is essential because I don’t want to get infected as I travel quite a bit. Since I play a lot with my SQL Server, there are more chances that things are going to break sooner than your production server. Here is one such incident.

I was trying to apply patch to my SQL instance and it went fine. But then I was NOT able to start SQL Server service. Here are the messages from SQL ERRORLOG.

2016-01-24 06:14:40.63 spid7s      Error: 537, Severity: 16, State: 3.
2016-01-24 06:14:40.63 spid7s      Invalid length parameter passed to the LEFT or SUBSTRING function.
2016-01-24 06:14:40.63 spid7s      Error: 912, Severity: 21, State: 2.
2016-01-24 06:14:40.63 spid7s      Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 537, state 3, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2016-01-24 06:14:40.64 spid7s      Error: 3417, Severity: 21, State: 3.
2016-01-24 06:14:40.64 spid7s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2016-01-24 06:14:40.64 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

I knew that I need to use trace flag 902 to bypass script upgrade mode and fix something. It was challenging to find what to fix. So I started looking for ‘sqlagent100_msdb_upgrade.sql’ which was found under “Install” folder in “C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL”. I started SQL using trace flag 902 as below

NET START MSSQL$SQL2014 /T902

Refer: SQL SERVER – 2005 – Start Stop Restart SQL Server from Command Prompt

Then I was able to connect to SQL Server because the problem script didn’t run due to trace flag. I ran the script manually and found below piece of code failing.

DECLARE @dataDirName NVARCHAR(520)
SELECT @dataDirName = SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM MASTER.sys.master_files
WHERE (name = N'master')

script upgrade 01 SQL SERVER    Script level upgrade for database master failed because upgrade step sqlagent100 msdb upgrade.sql

Now, we need to figure out what’s wrong and how to fix it. So, I modified query to get details.

SELECT  DB_NAME(database_id) 'DB name', name 'logical name', physical_name
FROM MASTER.sys.master_files
WHERE (name = N'master')

script upgrade 02 SQL SERVER    Script level upgrade for database master failed because upgrade step sqlagent100 msdb upgrade.sql

Now we can see a problem. The problem is because we have two rows and master.mdf doesn’t exist in second row. It is failing because a test database had filename set correctly but it’s logical name was set as master.

script upgrade 03 SQL SERVER    Script level upgrade for database master failed because upgrade step sqlagent100 msdb upgrade.sql

To fix the problem I corrected the logical name of the user database which had been incorrectly set as master. Once this was done then, I stopped SQL and started it normally (without trace flag 902) and it was able to start successfully.

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

SQL SERVER – 2016 Error Reasons: Msg 10778, Level 16 Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported

When I was writing the blog SQL SERVER 2016 – InMemory OLTP support for Foreign Key, it felt like a great addition to SQL Server. I felt there must be something that I might be missing as the obvious. I was not wrong in that feeling too. One of my blog readers pinged to say they were getting a strange error that Foreign Key was not supported. This is how I figured out what was going wrong.

Pinal: Hi buddy.

Reader: Thanks for the ping.

Pinal: Not an issue. It is my pleasure.

Reader: As you know, based on your last week blog. I was experimenting something.

Pinal: Go on, anything related to this blog is something I am all ears.

Reader: I am getting an error.

Pinal: Which Blog are we talking?

Reader: It is the OLTP support for Foreign keys.

Pinal: Oh that one. That was a simple blog. What is the error?

Reader: It say it is not supported.

Pinal: Are you sure you are on a SQL Server 2016 version when you are trying that? It wouldn’t work on a SQL Server 2014 edition.

Reader: I am on the latest SQL Server 2016 version. But I was experimenting and got this error.

Pinal: What sort of experiment? What error are we talking?

Reader: The error states: “Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported.”

Pinal: No wonder. It is quite possible. Now I get it. You didn’t run the script as-is but changed one of them to Disk based tables.

The reader had changed the blog SQL SERVER 2016 – InMemory OLTP support for Foreign Key, and edited the portion of creation of table. It was as below:

CREATE TABLE Products
(
ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,
ProductName VARCHAR(25)
);
GO
CREATE TABLE ProductSales
(
SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY NONCLUSTERED HASH (SalesID) WITH (BUCKET_COUNT = 10000),
ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),
SalesPerson VARCHAR(25)
)
WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

As you can see the Products table was made as normal Disk based table while the transactions table was made as InMemory OLTP table. I am sure this is the strategy for most of the users wherein they will be creating normal tables for their masters and using the InMemory capability for the transactions. At the moment we will receive the error:

Msg 10778, Level 16, State 0, Line 3
Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint or index. See previous errors.

I am sure the SQL Server product team is looking into this and maybe in subsequent versions there will be a provision for the same. But how important are these capabilities in your opinion? Do you want relationship management from InMemory and Disk based tables? Do let me know via the comments.

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

SQL SERVER – Fixing Annoying Network Binding Order Error – Notes from the Field #112

ryanadams SQL SERVER   Fixing Annoying Network Binding Order Error   Notes from the Field #112[Note from Pinal]: In this episode of the Notes from the Field series database expert Ryan Adams explains a very critical error user receive when binding network error. Ryan is one guy who spends more time with real world issues with SQL Server than anything else. He has mastered the art of resolving complex errors and document them so easily that one can’t find anywhere else. In this blog post Ryan addresses a very interesting error related to binding network error. Read the experience of  Ryan in her own words.


One of the more common errors I see when setting up a Windows cluster (usually in preparation for a SQL Server Failover Cluster Instance or a SQL Server Availability Group) is regarding an incorrect network binding order.  You will see this presented as a warning in the Cluster Validation Report.  There are actually two different errors that I have seen with the first being very common and the second being more rare.

Error 1

Rule “Network binding order” generated a warning.

The domain network is not the first bound network.  This will cause domain operations to run slowly and can cause timeouts that result in failures.  Use the Windows network advanced configuration to change the binding order.

What this error means is that the network card used to connect to your domain network is not at the top of the network binding order.  The fix for this is usually pretty easy because we just need to go into Control Panel…Network and Internet…Network Connections and make the change.  Once we get there we need to identify which NIC card is connected to the domain network and that can be seen in the “Network Category” column shown in the screen shot below.  You’ll notice that I have labeled my connections Public and Private and they both show “Domain Network”.  If you are configuring a multi-subnet cluster you will see the exact same thing, but if your cluster is on a single subnet the Private network connection will show “Public Network”.  So in a single subnet it’s the one labeled “Domain Network” that you are targeting and in a multi-subnet cluster it’s your Public connection you are targeting.

112 1 SQL SERVER   Fixing Annoying Network Binding Order Error   Notes from the Field #112

In order to change the network binding order we need to go into the advanced settings.  Starting in Windows 2008 this option is hidden.  If you don’t see it hit ALT on your keyboard and the Advanced option pointed out in the previous screen shot will appear.  We need to select that and then go to Advanced Settings.  You will now be presented with the Advanced Settings box shown below.  In the screen shot you will see that my Public network is the second in the binding order and we need to move it to the top by selecting it and hitting the up arrow.  Click OK and go run Cluster validation again to see if it is resolved.

112 2 SQL SERVER   Fixing Annoying Network Binding Order Error   Notes from the Field #112

There is a chance that either your domain network was already at the top of the binding order, or you ran Cluster Validation again and it failed with the same error.  If that is the case then you either have a ghost network card (can be caused by NIC changes or driver changes) or the Microsoft Failover Cluster Virtual Adapter is bound before the domain network.  These adapters are not show in the GUI, but can be found in the registry and other places like the ipconfig /all command.

Unfortunately the network binding order in the registry uses GUIDs instead of friendly names, so we’ll have to do some translating to find and move the domain network to the top.  The first thing we will do is go figure out what the GUID of the domain network NIC is by running the following command from a command prompt.

WMIC Nicconfig Get Description, SettingID

You’ll remember that I renamed my NIC cards to be called Public and Private, but that’s the friendly name and not what will be returned from WMIC.  WMIC returns what is in the “Device Name” column from the very first screen shot above.  In my case it is called “Intel(R) PRO/1000 MT Desktop Adapter”.  You can see this pointed out in the screen shot below where we can see the output of WMIC in the command window.  Note that the GUID starts with A7.

112 3 SQL SERVER   Fixing Annoying Network Binding Order Error   Notes from the Field #112

Now we just need to open Regedit and head to HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Linkage\Bind.  That’s right, we’re getting down and dirty now so make sure you know what you are doing and above all else backup your registry before you make any changes.  Mistakes in the registry can be costly and destroy a system so proceed with caution because from here on out the responsibility lies solely with you…not me.  In the screen shot above you can see that my Public Domain Network is next to the last in the list and we need it to be at the top.  As an aside, I have also pointed out where the Microsoft Failover Cluster Virtual Adapter is located since I see this listed above the Public network from time to time.

The fix here is to cut the GUID for the Public Domain Network that starts with A7 and paste it at the top of the list.  Now we can go run Cluster Validation and life should be good unless you get the second error we’ll talk about now.

Error 2

Note that the error message is the same error you got above.  However, it’s a completely different issue.  So let’s say you verified the above and that the domain network is the first in the list, but the error persists.  Go Look in the following file and search for “IsDomainInCorrectBindOrder” to find the warning in the log file.

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\"Newest Log File Folder By Date"\Detail.txt

Here is a sample of the section you are looking for.

Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.NetworkBindingFacet
NetworkBindingFacet: Looking up network binding order.
NetworkBindingFacet: Network: ‘Production Team’ Device: ‘\Device\{0BF4D354-E6E9-480C-91CF-DC598282C4C1}’ Domain: ‘LITWARE.COM’ Adapter Id: ‘{0BF4D354-E6E9-480C-91CF-DC598282C4C1}’
NetworkBindingFacet: Network: ‘Local Area Connection’ Device: ‘\Device\{4DB91193-72F1-4713-A938-EB73F27CFEC8}’ Domain: ” Adapter Id: ‘{4DB91193-72F1-4713-A938-EB73F27CFEC8}’
NetworkBindingFacet: Network: ‘Heart Beat’ Device: ‘\Device\{5AC63784-8088-40F7-93C8-37F9CD03D445}’ Domain: ” Adapter Id: ‘{5AC63784-8088-40F7-93C8-37F9CD03D445}’
NetworkBindingFacet: Network: ‘BackUp Network’ Device: ‘\Device\{52AEDCB0-9E8E-4243-9D5D-ED86E602DF23}’ Domain: ” Adapter Id: ‘{52AEDCB0-9E8E-4243-9D5D-ED86E602DF23}’
IsDomainInCorrectBindOrder: The top network interface ‘Production Team’ is bound to domain ‘LITWARE.COM’ and the current domain is ‘CONTOSO.COM’.
Evaluating rule : IsDomainNetworkTopOfBindings
Rule running on machine: Server1
Rule evaluation done : Warning
Rule evaluation message: The domain network is not the first bound network. This will cause domain operations to run slowly and can cause timeouts that result in failures. Use the Windows network advanced configuration to change the binding order.
Send result to channel: RulesEngineNotificationChannel

The issue here is that the server is joined to the LITWARE.COM domain, but the current domain is that of the currently logged in user which happens to be CONTOSO.COM.  Another way to say this is that the server is joined to the LITWARE.COM domain, but you logged with a user account from the CONTOSO.COM domain to create the cluster.  From a domain perspective these are completely different domains that have been trusted and it’s possible that they are in different forests too, but again they are trusted.  Technically this configuration is correct as the public/domain joined network is indeed at the top of the list.  You have two choices here.  You can safely ignore this warning or you can log out and back in with a user in the LITWARE.COM domain.

If you are looking for more information around Clustering, AlwaysOn Failover Clusters, or AlwaysOn Availability Groups you can visit my blog at http://www.ryanjadams.com/category/sql-server/alwayson-ag/ for more articles.  Setting up clusters to support SQL Server is complicated, but can yield great benefits if done correctly.  If you’re setting up a new cluster or interested in having us take a look at your current systems we would be happy to work with you.  You can find more information on the services we provide and contact us on our website http://www.linchpinpeople.com/.

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)

SQL SERVER – Add failover cluster node fails with “number of cluster nodes supported for edition”

A lot of my blog readers contact me via comments and I try my best to reply to all of them. Sometimes issue is so interesting that I ask them to contact via email. This blog is result of such an interaction.

Asif (name anonymized) was trying to Add third node to SQL Server failover cluster instance (FCI). He was able to add second node without a problem. But when he was trying to add the third node, there seemed to be an error. FCI related errors and troubleshooting are sometimes complex and I try to keep such interactions on face-to-face troubleshooting only. Here I possibly felt there was something fundamentally wrong in the understanding. He also told that this is enterprise edition media. So, I asked him to share the screenshot and setup logs:

AddNode 01 SQL SERVER   Add failover cluster node fails with number of cluster nodes supported for edition

Here is the snip from the setup logs.

(14) 2016-01-10 15:44:24 Slp: Executing rules engine…
(14) 2016-01-10 15:44:24 Slp: Start rule execution, total number of rules loaded: 18
(14) 2016-01-10 15:44:24 Slp: Initializing rule      : Number of cluster nodes supported for edition
(14) 2016-01-10 15:44:24 Slp: Rule is will be executed  : True
(14) 2016-01-10 15:44:24 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.NumberOfNodesFacet
(14) 2016-01-10 15:44:24 Slp: Rule ‘Cluster_NumberOfNodes’ edition Standard allows 2 cluster nodes.
(14) 2016-01-10 15:44:24 Slp: Rule ‘Cluster_NumberOfNodes’ detected 2 cluster nodes.
(14) 2016-01-10 15:44:24 Slp: Evaluating rule        : Cluster_NumberOfNodes
(14) 2016-01-10 15:44:24 Slp: Rule running on machine: SQLNode03
(14) 2016-01-10 15:44:24 Slp: Rule evaluation done   : Failed
(14) 2016-01-10 15:44:24 Slp: Rule evaluation message: This SQL Server edition does not support the installed number of cluster nodes. To continue, remove nodes and then complete cluster installation.
(14) 2016-01-10 15:44:24 Slp: Send result to channel : RulesEngineNotificationChannel

I saw something familiar, where it said “Standard” – now that looked basic validation has failed. I have asked to run below query in SQL Server Management Studio

SELECT SERVERPROPERTY('Edition')

OR share the ERRORLOG

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

Here is what we saw in ERRORLOG

2016-01-18 14:55:48.430 Server       Microsoft SQL Server 2014 – 12.0.2548.0 (X64)
Jun  8 2015 11:08:03
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 10240: )

As I was guessing earlier, it is a Standard edition which allows only 2 nodes in FCI. So, I asked him to contact to the application team and get media for enterprise edition. He upgraded Standard to Enterprise and then he was able to add node.

If we closely look at setup logs, its clearly mentioned there. Have you ever encountered any such simple errors in SQL Setup and logs have helped you?

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

SQL SERVER – Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

Some errors when encountered take most of us for a spin. In this category the error related to “Timeout” surely falls. If you are a web developer and receive the same there are a hundred combinations why this can possibly happen. The web results can sometimes lead us in completely opposite direction because we have not analyzed the root cause for this. I sincerely urge everyone when working with generic errors, look for specific symptoms and then use the trick of eliminating one after the other before a final solution can be arrived.

This is one of the most common error which you would hear from web developers. Here is a typical error raised shown on website.

timeout 01 SQL SERVER   Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

[SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +83
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.ExecuteReader() +12
System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) +332

It’s very clear message that the timeout duration specified for the operation is completed but actual work didn’t finish. The error can come because of two kind of timeouts:

  1. Query Timeout: The default value for web application or .NET application is generally 30 seconds. If any request is in progress and it couldn’t complete within the timeout period, we would again see an error.
  1. Connection Timeout: The default value of connection timeout is generally 15 seconds. Within this time if connection can’t be made, we would see the error.

Next obvious question is – what should we do if we see such error?

Query timeout: The reasons a command/query runs longer than expected is commonly due to blocking or the need for query/index tuning or both.  A quick way to check for blocking to to run sp_who2 while the query is running.  The BlkBy column will show the SPID of the blocking connection if the query is blocked.  For slow running query, you may want to check the execution plan to verify that the statement is touching the rows which are needed.  For example, if your intent is to Select a single row but you see a scan operator, that is a strong indication that you need to perform index or query tuning or need to update statistics.

Here is the over-simplified repro of query timeout done via SQL Server Management Studio (SSMS). The default value of query timeout in SSMS in 0 (which is infinite) Tools > Options in menu would open below window where the value can be set, if needed.

timeout 02 SQL SERVER   Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

For demo purpose, I have changed it to 10 seconds. In one query window we would run below command

USE tempdb
GO
CREATE TABLE foo (i INT)
BEGIN TRAN
INSERT INTO
foo VALUES (1)
GO

In second query window, we can run below command

SELECT * FROM tempdb..foo

and we can see below after 10 seconds.

timeout 02 1 SQL SERVER   Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

It is important to note the error number. It is not an SQL Server error but error by client. The query waited for 10 seconds because we caused blocking. If we don’t set timeout value in SSMS, they query would run forever because default timeout is zero.

Connection timeout: This I can’t reproduce by management studio query easily. On a slow network, you can easily reproduce it by reducing the connection timeout to a lower value. In SSMS. You need to click on Options on login page as shown below:

timeout 03 SQL SERVER   Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

and change the value in Connection Properties tab.

timeout 04 SQL SERVER   Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

On the same screen we can also set query timeout (shown as execution time-out)

If you are seeing same errors in your web application, then you need to check configuration file (normally known as web.config file). The same parameters (query and connection timeout) can be set in connection string of the application as well.

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

SQL SERVER – Why is My Query Switching to Row Processing and Not Doing Batch Processing?

Some of the learnings can lead to another exploration when someone does stump you with something you didn’t know. To understand the basics of batch mode and row mode, read the blog SQL SERVER – ColumnStore Index – Batch Mode vs Row Mode where I have explained the concepts.

This blog got inspired by the fact when one of the readers after reading this blog came back to me and asked, how do I know that the query that I am working has aborted from Batch processing and reverted to row processing. At a glance, I felt this was a trivial question and didn’t think much. But the question kept haunting me for a while. I incidentally, was at a Microsoft conference and got to ask this question with bunch of experts as a side conversation.

The first answer I got was, did you check Extended events? I was pleasantly surprised because I didn’t think it that way. Since I come from the school of using Profiler for quite some time this was not a natural thought. As soon as heard that, I cranked my SSMS to check. Guess what – I got to an event expression_compile_stop_batch_processing which exactly did fit my requirement.

I went to read the description as: Occurs when an expression is not natively supported in batch processing mode and a wrapper of row-by-row evaluation is used.

cloumnstore abort row 01 SQL SERVER   Why is My Query Switching to Row Processing and Not Doing Batch Processing?

So this event is raised when the query aborts from batch processing to row mode. This I personally felt was a great learning and exploration for me when working with ColumnStore Indexes. Since this was a SQL Server 2014 instance, some of the functions that short circuit to row mode include:

  • UNION/UNION ALL, SELECT/COUNT DISTINCT, INNER JOIN, ORDER BY
  • addition, subtraction, and multiplication, and division
  • SUM, AVG, MIN, MAX, IS / IS NOT NULL, CASE WHEN /CASE WHEN NOT, IN  /NOT IN, HAVING, EXISTS / NOT EXISTS, CAST, CONVERT, CTE, ISNULL(), DATEADD, TOP, BETWEEN
  • < , >, <=, >=, <>, =
  • equal, not equal (strings)
  • and, or, in, not,

I know this may not be exhaustive and as the versions upgrade, some of these limitations are getting addressed. Hope you learnt something new today and feel free to let me know your thoughts via the comments below.

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

Interview Question of the Week #055 – How to Convert ASCII to DECIMAL or DECIMAL to ASCII?

Question: How do you convert ASCII to DECIMAL or DECIMAL to ASCII?

Answer: 

ASCII – Returns the ASCII code value of the leftmost character of a character expression.

CHAR – Fixed-length non-Unicode character data with length of n bytes.

Examples:
--Decimal to ASCII
SELECT CHAR(80)+CHAR(73)+CHAR(78)+CHAR(65)+CHAR(76) ASSQLAuthorityAuthor
GO

--ASCII to Decimal
SELECT ASCII('P') AS SQLAuthorityAuthor
UNION ALL
SELECT ASCII('I')
UNION ALL
SELECT ASCII('N')
UNION ALL
SELECT ASCII('A')
UNION ALL
SELECT ASCII('L')
GO

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

SQL SERVER – Script: Change Service Account Using WMI / SMO

Last week I wrote a post where my friend faced problem because he changed the service account of SQL Server from services. mass rather than SQL Server Configuration Manager.

SQL SERVER – Performance counter missing! DMV sys.dm_os_performance_counters is Empty

I am a strong advocate of using the tools which are designed for that purpose. When I told that same thing to my friend, he said “how can I manage 1000 SQL Servers like this? I need to automate things via script.” Here is script which I use to change the service account for ALL SQL instances on my machine. There would be other possible ways to do what I am trying to do, but I am trying to build a foundation of standard scripts related to SQL using SMO and these examples are using just WMI.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
$Server = "SQL16NodeB"
$UserName="SQLAuthority\SQLSvc"
$Password="Sysadmin@1234"

$SMO = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $Server
$Service = $SMO.Services | where {$_.type -like "SQLServer"}
Write-Host 'Properties before Change'
$Service | select name, ServiceAccount, DisplayName, StartMode  | Format-Table
$Service.SetServiceAccount($UserName, $Password)
Write-Host 'Properties after Change'
$Service | select name, ServiceAccount, DisplayName, StartMode | Format-Table

The variable are declare on the top of script. Here is the output on my machine.

wmi 01 SQL SERVER   Script: Change Service Account Using WMI / SMO

You would notice that script has picked only SQL Server account. My machine has two instances (default and named) and it has been changed for both. Notice that I have added below filter

where {$_.type -like "SQLServer"}

due to that I am getting all SQL Services not SQL Agent Services.

How do you change service account or password in your environment?

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