Category: SQL

SCRIPT GALLERY, SQL

How to identify table rows and size in a database in SQL Server

Problem Statement:

At times, we may need to understand the size of the tables in a database for various reasons. I often query these details to understand the growth of the data and clean up data for lower environments.

Code:
Here is a simple script to understand the rows, size of the table in a database.
You can further make changes to filter for any tables/group the results based on the indexes/partition etc.


SELECT  sc.name + '.' + t.NAME AS TableName,  
        SUM(p.[Rows]) NumerOfRecords,
        -- p.partition_number, i.index_id,i.name,
        ( SUM(a.total_pages) * 8 ) / 1024 AS TotalReservedSpaceMB, 
        ( SUM(a.used_pages) * 8 ) / 1024 AS UsedDataSpaceMB,  
        ( SUM(a.data_pages) * 8 ) / 1024 AS FreeUnusedSpaceMB  
FROM    sys.tables t  
        INNER JOIN sys.schemas sc ON sc.schema_id = t.schema_id  
        INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id  
        INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID  
                                            AND i.index_id = p.index_id  
        INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id  
WHERE   t.type_desc = 'USER_TABLE'  
        -- Replace with valid table name if you want to filter for a table
        --AND t.NAME='YourTableName' 
GROUP BY sc.name + '.' + t.NAME,  
        i.[object_id]
        --,i.index_id, i.name, p.[Rows], p.partition_number
ORDER BY ( SUM(a.total_pages) * 8 ) / 1024 DESC
Date & Time, SQL

Find Max and Min value of dates in a row in SQL Server

Problem Statement:
How to identify the Max and Min date value in a row in SQL Server?

Code:
Here is a solution to identify Max and Min date value with the help of CROSS APPLY in SQL Server.


create Table FindMaxMindate
(
    ID int, 
    Firstdate datetime,
    Seconddate datetime, 
    ThirdDate datetime
)

Insert into FindMaxMindate 
Values
(1,'2018-01-26 19:14:07.587','2018-04-26 19:14:07.587','2018-12-26 19:14:07.587'),
(2,'2018-12-26 19:14:07.587','2018-01-26 19:14:07.587','2018-03-26 19:14:07.587')

Select ID,Min(Dates) MinDate,Max(Dates) MaxDate
From FindMaxMindate A
Cross apply (values (Firstdate),(SecondDate),(ThirdDate))B (Dates)
Group by ID

Drop Table FindMaxMinDate
SCRIPT GALLERY, SQL

How to identify Process Name of a Process Id in SQL Server

We had an immediate requirement to identify process names of process ids in SQL Server. The first reaction to the requirement was to refer the program name. However, program name is customized through application, it was not the expected result.

I tried multiple ways to identify a simple solution within SQL Server, but failed to identify a straight forward solution.(You can share if you have any simpler solution.)

With the help of Powershell script, developed a small script to get the process name as below. Hope, this will be helpful for you in the similar situation.

Code:

create Table #TblProcessName(ProcessName varchar(max))

Insert into #TblProcessName
Exec xp_cmdshell 'powershell.exe "Get-Process | Select ProcessName,Id | % {$_.ProcessName + " "- + $_.Id}"' 

;With cte as
(
Select Substring(Processname,0,charindex('-',Processname)) as ProcessName, Substring(Processname,charindex('-',Processname)+1 , Len(processname)) as ProcessID 
From #TblProcessName A
)
Select * From cte A
Inner join sys.sysprocesses B on A.ProcessID = B.hostprocess

DROP Table #TblProcessName

Output:

DBA Utilities, Errors and Exceptions, SQL

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.

Linked Server, SQL

More about MSDTC

What is MSDTC?

MSDTC is a acronynm for Microsoft Distributed Transaction Coordinator which is a windows service to maintain the transactions in a distributed environment. The internals of MSDTC is a black box for users, however, this service is very important to maintain the transactions between two different systems. A Distributed transaction is a transaction that spans across multiple machines.Distributed transaction will ensure if there are multi operation happens between servers, if any of the operation is failed and cancelled, none of the operations in the transaction is getting committed in a multi server environment. I am not sure if I can simply explain than this.

Where do we need MSDTC?

Typically, in the context of SQL Server, yes we may need MSDTC for Linked Server usages.If transaction is opened to do some operations across servers in a linked server environment, this needs to be converted as a distributed transaction to maintain the integrity of the data. We need to understand two things clearly here.

1. When two physical machines are communicating with a transaction. If you are using two instances of the same machine, DTC will not be used. However, if the instances are in a cluster, you need to use DTC as you cannot ensure the instances are in the same physical nodes in the cluster environment.

2. Irrespective of type of transaction, whether its implicit or explicit, you need to use DTC. A typical example for implicit transaction is Trigger. Please find more detail here.

How to troubleshoot MSDTC issues?

Troubleshooting MSDTC issues is a pain area as you will not have complete information about the DTC component. We can use DTCPing tool to troubleshoot the issues which will provide much better insights.

DTCPing tool can be downloaded here.

How to configure and troubleshoot using DTCPing tool?

Test Network Connectivity
1. On Computer A, run DTCPing.exe.

2. On Computer B, run DTCPing.exe.

3. On Computer A, type the NetBIOS name of Computer B, and then click Ping.

4. On Computer B, type the NetBIOS name of Computer A, and then click Ping.

NOTE: The DTCPing log file can be found in the same folder as the DTCPing.exe file. The log file name has the following format: “NetBIOSName” + “ProcessID” + .log

You can see more details in the readme.txt available in the tool kit.

See Also:

How to troubleshoot msdtc issues with dtcping tool