DBA Utilities

Leave a comment Posted on by latheeshnk DBA Utilities, Errors and Exceptions

SQL Server Configuration Manager – The remote procedure call failed. [0x800706be]

Problem Statement:

Recently, I encountered an issue as below with SQL Server Configuration Manager which is a very common issue for most of us. Most of the time, this type of error is related to not having enough permission to see the SQL Server Configuration Manager. However, the case was different for me as I am the administrator for my system.

Observations:

In my laptop, I have multiple SQL versions like 2008 R2, 2014, 2016 and 2017.For some reason, SQL Server Configuration Manager (by default it was pointing to oldest one, nothing but 2008 R2 version) was not able to correctly get the information for all the installed versions. This was causing an issue.

To resolve the issue,

1. we can identify the file location as “C:\windows\syswow64\” OR “C:\Windows\System32\”.
The SQL Server Configuration manager file name should be similar to SQLServerManager**.msc where ** denotes the version of the SQL Server.

2. Open the latest version of SQL Server configuration manager file. This will work as expected.

However, the above is not a complete solution while we open the configuration manager from the start window. This is because, the default configuration manager will still be pointing to the older version of the SQL Server. To change this behavior, we need to do the following steps.

1. Open the “SQL Server Configuration” in start and right click on the icon.


2. Right Click on “SQL Server Configuration Manager” and change the Target file to the latest mmc file.(You should have administrator privilege to do so)


3. Close the Window

From then, you will be successfully able to see the SQL Server Configuration manager that fetch the data as expected.

Advertisements
1 Comment Posted on by latheeshnk DBA Utilities, Linked Server

How to identify NetBIOS Name of a computer?

Its not a SQL Server related, but I found it difficult to identify a way to get the NetBIOS name of a computer, hence sharing the same.

Recently, while troubleshooting an issue with MSDTC, I had to use DTCPing tool. I do not cover the DTCPing tool here, but will share more about it in another post. DTCPing tool was expecting NetBIOS name instead of IP address.

So, here are few options to identify the NetBIOS Name of a computer. 


Using T-SQL

Select serverproperty('ComputerNamePhysicalNetBIOS')
(Or)
exec master..xp_regread 'HKEY_LOCAL_Machine', 'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\','ComputerName'

Using Windows

nbtstat -n  (-n   -- Lists local NetBIOS names.)

Thats it for now, but if you think there is any other method, post it in the comment section.

Leave a comment Posted on by latheeshnk DBA Utilities

A quick look at SQL Server Logs

Logs are always important for anyone who works with any application. SQL Server has its own Logs that can be accessed to get the required information. Often, people call it as SQL Server Error logs. However, I personally would like to refer as SQL Server logs instead of Error Logs. The reason is that it does not only report the errors or exceptions, but also other important information.

What are the different types of logs in SQL Server?

Each one represents the logs for different purposes.
1. SQL Server – Logs SQL Server specific information
2. SQL Server Agent – Logs SQL Server Agent information
3. Database Mail – Logs Database Mail related information
4. Windows NT – Logs System related information

How do we see these log information?

There are multiple ways to get the log information as below.

using TSQL command using XP_READERRORLOG

XP_ReadErrorlog is an extended procedure available in SQL server. Please note that this is an undocumented procedure.

Parameter details:

There is also another method using sp_readerrorlog. However, if you take the definition of the procedure, it uses XP_readerrorlog internally.

Using Object Explorer

Management provides two options for us to view the logs as below.
1. Management -> SQL Server Logs

2. SQL Server Agent -> Error Logs

How to find the location of SQL Server log file?

The logs are not storing in SQL Server anywhere, but in the disk. When you access the logs in any of the above methods, it reads from a file from the predefined location. We can identify the location of the log files by any of the below methods.

using XP_Readerrorlog

using SQL Server configuration Manager

– SQL Server Configuration Manager -> Right click on SQL Service, properties -> Select “Startup Parameters”
– Parameter that starts with “-e” represent the SQL Server log path

using SERVERPROPERTY


How do we clear the Logs from SQL Server?

To clear the Logs, there are two options as below. To be clear, there is no concept of clearing the log *immediately*, but closing the most recent one and clearing the oldest one as per the configuration.

sp_cycle_errorlog
– Closes the current log for SQL Server logs and flush the oldest one.
– Customize the number of maximum archives that you want to keep. The range is between 6(Min) to 99(Max).
– You can configure the number of error logs by Right click on “SQL Server Logs” under Management -> Configure -> specify the number of files. By default, the value is 6.
sp_cycle_agent_errorlog
– Closes the current log for SQL Server logs and flush the oldest one.
– Make sure the current database is msdb.
– Make sure the SQL Server Agent is up and running.
– Can have maximum of 9 archives for agent logs.

Hope this post helps you to understand the Logs in SQL Server, Looking for your feedback if I missed anything.

Leave a comment Posted on by latheeshnk DBA Utilities, SCRIPT GALLERY

How to get last running query based on SPID in SQL Server

We often need to identify the SQL text that was executed last time for a spid in SQL Server. Recently one of my friend was asking to get these information for his troubleshooting purpose. I have come up with a very simple query to help him as below.

Hope this will be helpful for those looking for similar information.


SELECT
sysprc.spid,sysprc.waittime,sysprc.lastwaittype,DB_NAME(sysprc.dbid) AS database_name,
sysprc.cpu,sysprc.physical_io,sysprc.login_time,sysprc.last_batch,sysprc.status,
sysprc.hostname,sysprc.[program_name],sysprc.cmd,sysprc.loginame,
OBJECT_NAME(sqltxt.objectid) AS [object_name],sqltxt.text
FROM sys.sysprocesses sysprc
OUTER APPLY sys.dm_exec_sql_text(sysprc.sql_handle) sqltxt
Leave a comment Posted on by latheeshnk DBA Utilities

DBCC UPDATEUSAGE – Is it important post SQL Server version upgrade?

As part of migration from SQL server 2008 R2 to SQL server 2014, we listed out a list of items to be taken care post upgrade. There was a question in the panel on DBCC UPDATEUSAGE – should we really do this as part of post migration. Here are few points that I support to do this operation.

What is DBCC UPDATEUSAGE doing?

UPDATEUSAGE is a DBCC command that reports and correct inaccurate pages and rows in the catalog views.These catalogs will be used to report information for sp_spaceused and therefore it is very important to update those information using UPDATEUSAGE command.

Why do we need post migration?

When we are upgrading a SQL Server version, this is mandatory to update these catalogs, as these catalogs may be used internally for various purposes. UPDATEUSAGE command primarily updates page allocations of data/index. In addition, the command can also be used to update the row counts as well with an optional parameter – WITH COUNT_ROWS.

DBCC UPDATEUSAGE(0) WITH COUNT_ROWS

Few other details –

It is to be noted that you should not get confused by the SELECT COUNT() method and other methods that uses catalogs to get the row count information. SELECT COUNT always reads the from underlying objects, hence the information will be always accurate. Read more about it in the below link.

https://sqlzealots.com/2017/01/29/sql-server-count-table-rows-for-all-databases-using-sp_msforeachdb-and-sp_msforeachtable/

DBCC UPDATEUSAGE holds a shared lock on the object, hence this may cause a blocking scenario in a highly concurrent environment. This is not advisable to run the command on production drectly , rather to plan these to happen on maintenance window.

The other way to invoke the UPDATEUSAGE is with sp_spaceuded as below:

sp_spaceused @updateusage=true

Hope you enjoyed this blog post, let me know your thoughts on the topic.

Leave a comment Posted on by latheeshnk DBA Utilities, SCRIPT GALLERY

How to track SQL Server Database connections using T-SQL

One of my friend was asking to get him information from SQL Server to troubleshoot some performance issues.
My initial understanding, to collect data from the Performance counters, however, I realized that my friend is looking for information at grouping HostName, ProgramName etc.

Hence, come up with a small script as below to capture the details every 1 minute(customized as per the request).


If exists(Select 1 From sys.tables where name='Capture_DBConnection')
Drop table Capture_DBConnection

Create Table Capture_DBConnection (CapturedTime datetime,SPID Varchar(MAX), [Status] varchar(50),Login nvarchar(100),HostNAme varchar(5000),BlkBy Varchar(MAX), DBNAME sysname null,Command varchar(MAX),CPUTIME Varchar(MAX), DISKIO Varchar(MAX),
LastBatch Varchar(MAX), ProgramName Varchar(MAX),SPID1 Varchar(MAX), REQUESTID Varchar(MAX))

While(1=1)
Begin
	Create Table #Capture_DBConnection (SPID Varchar(MAX), [Status] varchar(50),Login nvarchar(100),HostNAme varchar(5000),BlkBy Varchar(MAX), DBNAME sysname null,Command varchar(MAX),CPUTIME Varchar(MAX), DISKIO Varchar(MAX),
		LastBatch Varchar(MAX), ProgramName Varchar(MAX),SPID1 Varchar(MAX), REQUESTID Varchar(MAX))
	
	Insert into #Capture_DBConnection Exec Sp_who2
	Insert into Capture_DBConnection 
	Select getdate(),* from #Capture_DBConnection 
	Drop table #Capture_DBConnection
	Waitfor delay '00:01:00'
END

Usage:
Here are few examples how to use the data collected.


-- Row data
Select * From Capture_DBConnection

-- To get count of connection for specified group
Select CapturedTime, HostName, ProgramName, Count(CapturedTime)
From Capture_DBConnection
Group by CapturedTime, HostName, ProgramName

Hope, this script will help you if you come across such situation.

Leave a comment Posted on by latheeshnk DBA Utilities, SQL Server, SQL Server 2016

Its always important to look at the License type of your SQL Server

Its always important to look at the License type of your SQL Server!!!

Problem Statement:

We recently had an issue with CPU utilization reaching more than 95% always for database server in one of our performance test environment. Load test environments are resource intensive test, hence it is expected to have high CPU utilization. However, we could observe the number of tests processed and number of transactions are very less spiking the CPU utilization to 95%.

Let me explain a bit more on my environment, We have 4 sockets with 10 physical cores and HT enabled in our test environment. As per the configuration we have total 80 logical CPU available. SQL Server version information as below:


Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 
               Jun 17 2016 19:14:09 
               Copyright (c) Microsoft Corporation
               Enterprise Edition ((missing))Core based Licensing>((missing)) (64-bit) on Windows NT 6.3  (Build 9600: )

Here we can observe that the version information is missing “Core based Licensing”, that means, the SQL version is not Core based, but CAL based. Let us look at the excerpt from the MSDN article:

“Enterprise Edition with Server + Client Access License (CAL) based licensing (not available for new agreements) is limited to a maximum of 20 cores per SQL Server instance. There are no limits under the Core-based Server Licensing model”

So, though we have 4 sockets with 10 Cores, ONLY 20 cores are VISIBLE for SQL Server. In our environment, this was 20*2(HT enabled) = 40 Logical CPUs are visible.

Ref: https://technet.microsoft.com/en-us/library/cc645993(v=sql.120).aspx

Lets confirm the above with other parameters DMV -sys.dm_os_schedulers.


Select parent_node_id,Count(cpu_id) Total_Schedulers,
 count(Case when Status = 'VISIBLE ONLINE' Then 1 Else null End) Visible_Count,
 count(Case when Status = 'VISIBLE OFFLINE' Then 1 Else null End) NotVisible_Count
From sys.dm_os_schedulers 
where status in ('VISIBLE ONLINE','VISIBLE OFFLINE') 
and parent_node_id not in (64)--DAC
Group by parent_node_id

The result looks like below:


This is clear that SQL server was not able to utilize more than 40 logical CPU in the above environment.

Once we upgraded the license to Core Based License, we were able to use all available CPUs in our environment and observed the database CPU utilization has come down to 65% resulting more number of tests and transactions.

Let me reiterate, Its always important to look at the License type of your SQL Server !!!