Tag Archives: windows

No transaction is active message when accessing Linked Server

Last week I had worked on an issue related to Linked Server. The customer had migrated the SQL Server Instances to Virtual Servers. They had quite a few Linked Servers setup. After migration any Distributed Transaction like the one below across the linked servers would fail immediately.

begin distributed tran
select * from RemoteServer.DBName.dbo.TableName
commit tran

Error message

OLE DB provider "SQLNCLI11" for linked server "linkedservername" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "linkedservername" was unable to begin a distributed transaction.

The first place I checked for problems was the Component Services (run –> dcomcnfg).

LocalDTCProperties

The options in the Local DTC Properties were correctly set as seen in this screenshot.

DTCProperties

Restarting the “Distributed Transaction Coordinator (MSDTC)” service didn’t help either.

The next step was to look for possible error messages in the Event Log. In the Application Event Log, the following error message was logged.

The local MS DTC detected that the MS DTC on ServerName has the same unique identity as the local MS DTC.
This means that the two MS DTC will not be able to communicate with each other.
This problem typically occurs if one of the systems were cloned using unsupported cloning tools.
MS DTC requires that the systems be cloned using supported cloning tools such as SYSPREP. Running 'msdtc -uninstall' and then 'msdtc -install' from the command prompt will fix the problem.
Note: Running 'msdtc -uninstall' will result in the system losing all MS DTC configuration information.

The error message in bold indicates that the Unique Identity for the MS DTC (SID) was same on both the local and the destination servers. How is this possible? While the new servers were being built they had syspreped servers. Hence the configuration of MS DTC was also propagated to all the servers where the same image was used.

Now that we knew the root cause, the resolution was pretty straight forward. Executed the following steps as explained in this article.

    • Opened the Command Prompt as an Administrator and executed “msdtc -uninstall”
    • Deleted the following registry keys (after exporting them as a precautionary measure)

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSDTC
HKEY_CLASSES_ROOT\CID
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC

  • Executed “msdtc -install” command in the Command Prompt
  • Rebooted the server

After reboot, the linked server queries returned the expected results.

SaveToSQLServer method error | Communication Link Failure

Last week one of our team members was implementing a Change Control to create a Maintenance Plan on a SQL Server 2005 instance. Since this instance was getting backed up using a Third Party tool, it did not have any Maintenance Plans on it. Since we wanted to backup one of the databases to disk, this Change Control was put in place.

Everything went fine until we tried saving the Maintenance Plan. It could not be saved and popped up the following error message.

I came across this error message for the first time. My team members stumbled on this KB article which explains the cause for this error. Here is the summary of the symptoms and cause.

  • Maintenance Plans are saved as SSIS packages and stored in MSDB by default
  • These packages use encryption to connect to SQL Server.
  • Secure Network packets are sent over Secure Socket Layer (SSL) and Transport Layer Security(TLS) layers.
  • Data fragments sent over these layers have a limit of 16K.
  • If the packet size sent from SSIS is greater than 16K, then SSIS terminates it by throwing the “Communication Link Failure” exception.

This scenario occurs only when the Network Packet size sent from SQL Server is > 16K. The default Network packet size in SQL Server is 4096B (4K). This confirmed that on this instance, the Network Packet Size was modified manually. This value can be checked in the Server Properties in SSMS or by executing sp_configure. The Server Properties for this instance looked like this.

The Network Packet Size value was set to 32767 B (~32K). I have no clue when and why was this value set. When client applications send and receive lot of network packets, increasing the Network Packet Size helps in reducing the number of packets sent across the network. This in turn helps the application performance.

But I am of the opinion that any settings under sp_configure should only be changed after knowing the pros and cons and also after thorough testing. This article clearly explains the aspects that need to be taken into consideration before changing the Network Packet Size value.

Moral of the story: Don’t change the settings on the fly. Take your time for analysis. At the end of the it is the instance that you support and you never know when the surprise package arrives!

Find SQL Server version details from the Registry

Last week, the customer wanted to know the Edition information of a Non-Production SQL Server instance. We can easily get this information by executing the following query in SSMS.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

 

This article discusses various approaches that are available to get the SQL Server version information.

To get the Edition information on this Instance was not that straight forward. The Production DBA team was part of the Local Administrators but our group was not granted access on the SQL Server Instance. Hence executing the above query against the instance was not an option.

The information related to SQL Server Setup/version is stored in the Windows Registry. This helped us to get the information related to SQL Server Edition without having access to the instance.

In Windows Registry, this information is stored in the following path.

The highlighted part in the above screenshot varies depending on how many instances are installed on a server. The remaining part in the path does not vary. The information under the Setup key looks like this (click on the image to enlarge).

Do you think of any other approach to get this information? Please feel free to post it in Comments.

Stop the command prompt from disappearing after execution

For most of the tasks I use the keyboard shortcuts. The touchpad comes in to picture only when there is no shortcut for a particular task or I don’t recollect the shortcut. Hence to launch any applications I use Start –> Run dialogue more often than not.

As a DBA while troubleshooting issues, I need to quickly check the IP Address of a computer. The easiest approach is to run the ipconfig command. By practice, I open Start –> Run and execute ipconfig.

But soon after executing this, the ipconfig window appears but it disappears quickly because the command prompt window does not “stay”. Then I would open cmd from the Run dialogue and hit ipconfig.

If you find this approach too long and would like to save a couple of key depressions, here is the approach that I use. Instead of executing ipconfig or any other command from Run, cmd.exe has a switch named /k. Using this switch, the given command would be executed but the command prompt window does not disappear but it would remain. The new approach to execute ipconfig from Run, would be like the one below.

After executing with cmd with the /k switch, the command prompt would stay.

This might not be a big trick but it is useful while doing certain repeating tasks from the Run dialogue.

Cannot open MSI file | Windows cannot access the specified file error

In order to try out the features of Microsoft SQL Server Database Publishing Wizard as explained in this post, I had downloaded the installer (an .msi file) on my laptop. Since I had the SQL Server running on a Virtual Machine, I copied this installer from my laptop to the VM (Windows Server 2003). But when I tried executing the installer from the VM, the following error message was displayed.

I had logged on to the server using a Login which was part of the Local Administrators group. Hence permissions to the file was not an issue. When I checked the Properties of the .msi file here is what I saw.

Like I mentioned earlier, this was downloaded from one computer and copied to another. Hence Windows had blocked it considering it as a potential threat. This is a security feature in Windows Server 2003, which blocks execution of executables like .exe, .msi files, which were downloaded on some other computer.

Since the root cause of this error message was now known, all I had to do was to click on Unblock button in the Properties of the installer. After unblocking it the installer executed fine and Microsoft SQL Server Database Publishing Wizard was successfully installed.

Script to check the Page File Settings

Last week when my team was working on a Performance issue, I wanted to check the Page File setting on that Cluster Node. Usually I navigate to the System Properties to check the Paging File settings.

Since that instance was critical one, the performance issue on it had got a lot of attention. Hence all the sessions on that instance were in use and I could not Remote Desktop to the node. At that time me and my colleague were discussing if there was a script to check the Page File allocation on a server. I did not have any scripts in my repository at that time. That night I came up with the below script.

 

On Error Resume Next
set oArgs=wscript.Arguments
Set objStdOut = WScript.StdOut
ServerName = trim(oArgs(0) )
Set objWMIService = GetObject("winmgmts:\\" & ServerName & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_PageFile")

For Each objItem in colItems
	objStdOut.WriteLine " "
	objStdOut.WriteLine "-----------"
	objStdOut.WriteLine "Name: " & objItem.Name
	objStdOut.WriteLine "Initial Size: " & objItem.InitialSize & " MB"
	objStdOut.WriteLine "Current Size: " & objItem.FileSize/(1024*1024) & " MB"
	objStdOut.WriteLine "Maximum Size: " & objItem.MaximumSize & " MB"
	objStdOut.WriteLine "-----------"
Next
objStdOut.Close

 

This script accepts the Server Name as the parameter. Hence this script can be used to check the Paging File settings on the servers across the network. If this script is saved as checkpf.vbs the command to check the Paging File would be

 

cscript checkpf.vbs ServerName

 

When I executed this script on a server which had the Page files on three different drives, the output was as below.

Find the Last Reboot time of the Server

Last week I had posted about some of the approaches I frequently use to find out when a SQL Server instance was restarted. Here are some of the methods I usually use to find out when a Server was rebooted.

Event Viewer

The Event Log service is the first one to start when a Server is rebooted. The Event ID 6005 is associated with the Event Log startup.

Uptime.exe

Uptime is a very useful utility to find out since when the Server is up. The other good thing about this tool is that the uptime of server across the network can be obtained from a single location. The command to get this information is uptime \servername.

This utility is not installed by default with Windows, it needs to be installed separately.

Systeminfo.exe

systeminfo provides detailed information about the computer configuration and hardware. Along with that it also provides the uptime of a given computer. Similar to uptime.exe this utility can also be used to get the information servers across the network. Since the output of this utility is very lengthy, the find parameter can be used to filter the requisite information. The command to find the uptime of the local server is as below.

systeminfo | find  "System Up Time"

Since this utility finds all the information related to hardware, at times it takes a lot of time to fetch the information. In my opinion, just to get the uptime of a server this utility is little too heavy.

Net Statistics

Net Statistics displays the time since the Server started collecting the statistics (which is also the time since the server was rebooted). The command to get that information is as follows.

net stats srv | find "Statistics since"

This is a light-weight tool which gives the information real quick.

Please share if you follow any other method to find out when a server was rebooted? Share your comments.