Tag Archives: configure

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.

Jobs fail with “Cannot execute as the database principal because the principal “dbo” does not exist this type of principal cannot be impersonated or you do not have permission” error

Recently I worked on an issue in which the jobs which were running fine started failing all of the sudden. The job history had the following error message.

Cannot execute as the database principal because the principal "dbo" does not exist this type of principal cannot be impersonated or you do not have permission

But the principal “dbo” exists, it can be impersonated and I have permission because I am the sysadmin! Now what changed?

Checked the job properties and “sa” was the job owner. This job was last modified a few years ago. The next step is to find out the properties of the database against which the job is executing the queries.

When I right clicked on the database and selected properties, got the following error.

Property Owner is not available for Database ‘[DatabaseName]‘. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

This error is generated when the database owner’s login does not exist in the active directory and I had blogged about it here.

When I executed sp_helpdb, the output looked like the one below.

sp_helpdb_invalidowner

As evident from the output, the database owner’s login was not present in the Active Directory and it was reported as ~~unknown~~. Now that we know the problem, the resolution is pretty simple. Just executed sp_changedbowner or ALTER AUTHORIZATION command against the databases in question. The job started running successfully.

The next time you create a database please take care to change the database owner to a different login. Else you leave the organization and someone else needs to cleanup your name from all the databases that you had created!

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!

Configuring SQL Server to use Windows Large-Page allocations

One of the Change Controls implemented today had inspired me to write this post. The Change Control was to enable Trace flag 834 on a SQL Server 2005 instance running on Windows 2008 Server (64 bit). As explained in this article, after enabling TF 834, SQL Server will make use of Large-Page allocations for the Buffer Pool memory. Here is my understanding of Large-Page allocations and its performance benefits.

Page allocations

The Virtual Address Space is made up of a number of pages. These pages are of two sizes. Large and Small. The Small pages are 4 KB in size (8 KB on IA64 systems) and the Large pages are 2 MB in size for x64 systems (16 MB for IA64 systems). The details regarding Virtual Addresses is maintained in a structure called Page Tables. Hence each Virtual Address has a corresponding Page Table Entry in the Page Table. The hardware translates these entries into a format readable by it. Whenever an address translation occurs, the entries in the Page tables has to searched. In order to speed up the lookup process, CPU maintains a cache called Translation Look-Aside Buffer (TLB). TLB works similar to the procedure cache, such that an entry once translated need not be translated again.

With this information it will be easy to understand how Large and Small page allocations make a difference to the performance. When a Virtual Address is making use of Small pages, the number of entries in the Page Table increases, which in turn increases the number of entries in the TLB. For example for few Virtual Addresses to be cached, using Small pages it would require more entries in TLB. More entries means whenever a new translation request is received, more number of cached entries in the TLB needs to be recycled. Hence allocating Virtual Addresses by means of Large pages has a definite performance benefit.

Page allocations and SQL Server

Like I mentioned earlier using Trace Flag 834 would force SQL Server to make use of Large Pages for Buffer Pool. This trace flag is only applicable to 64-bit of SQL Server and this also requires Lock Pages in Memory privilege for the SQL Server service account.

When SQL Server is making use of Large Page allocations, entries similar to the one below are logged in the SQL Server Error Log when the service starts.

Large Page Extensions enabled.
Large Page Granularity: 2097152
Large Page Allocated: 32MB
Using large pages for buffer pool.
10208 MB of large page memory allocated.

If the SQL Server service account does not have the Lock Pages in Memory privilege, this error message is logged.

Cannot use Large Page Extensions: lock memory privilege was not granted.

This article very nicely explains the Large Page allocations and also the fact that SQL Server Enterprise Edition (64-bit) will make use of Large Page allocations without the Trace Flag 834.

This is an attempt by me to cover a very vast topic with whatever I know about it. Hope you’ll like it.

 

Disable Unsigned driver installation warning in Windows

Last year when we were applying SQL Server service packs across enterprise, on few of the instances during the Service Pack installation the following warning message was displayed.

The software you are about to install does not contain a Microsoft digital signature. Therefore, there is no guarantee that this software works correctly with Windows.

After clicking the Continue anyway button, this warning would disappear. But the Service Pack installation used to fail. This is because the Driver Signing Options are by default set to Warn – Prompt me each time to choose an action in Windows. To overcome this issue, the Driver Signing Options need to be set to Ignore. This option can be configured as seen below.

Windows Server 2003

Open System Properties and select Driver Signing.

In the Options screen select Ignore.

This change will come into force without rebooting the server.

Windows Server 2008

Open the Command Prompt as Administrator.

In the Command Prompt enter the following command.

BCDEdit /Set LoadOptions DDISABLE_INTEGRITY_CHECKS

Using Local Group Policy Editor

This option can also be set using the Local Group Policy Editor and it is applicable to both Windows Server 2003 and 2008. Launch the Local Group Policy Editor by executing   Run –> gpedit.msc. Then select Code signing for device drivers as seen in the below screenshot.

In the Properties screen, select Ignore.

Using the methods explained above, the Unsigned driver installation warning can be disabled. After making this change, the SQL Server service pack completed successfully. Then the Driver Signing Options were brought back to the original state.

Remove SQL Server Trace messages from Error Log

It is common practice for a DBA to run Profiler/Server-side traces in order to troubleshoot performance issues. At times some server-side traces are also run for Auditing purposes. Whenever a SQL Server Trace is started or stopped, the following messages are logged in the SQL Server Error Log.

SQL Trace ID 2 was started by login "loginname"
SQL Trace stopped. Trace ID = '2'. Login Name = 'loginname'.

This is an informational message. But if the traces are started and stopped frequently, these messages can really bloat the SQL Server Error Log. I have seen a couple of instances in my environment which have these messages every 5 minutes. The relevant information can be easily searched in the SQL Server Error Log as I had explained here. But if these messages are causing the SQL Server Error Log to grow and thereby consuming a lot of drive space, then the DBA has to think of some alternative.

The easiest alternative is to stop these informational messages from getting logged in the SQL Server Error Log. Fortunately SQL Server 2005 onwards, the trace flag 3688 has been included. Starting the SQL Server with this trace flag on, the trace related messages do not get logged in the Error Log. Here is how to add this trace flag in SQL Server Configuration Manager.

After adding this trace flag, the SQL Server service needs to be restarted for the changes to take effect. This trace flag will stop all trace related messages (including the default trace) from appearing in the SQL Server Error Log.

How to disable rollback files in Config.msi folder

Sometime back I came across a question where the SQL Server uninstallation was failing because there was not enough space on the drive to create the .rbf file.

Error 1307. There is not enough disk space to install this file:
C:\Config.Msi\filename.rbf

As evident from the error message, SQL Server installer file was unable to create a Rollback file on the C drive. These .rbf files gets created depending upon whether the Windows Installer (.msi) file needs to rollback its actions if cancelled in between. By design these files are created in the Config.msi folder, which is always located on the drive hosting the Operating System (%systemdrive%). There is no option to change the Windows Installer to generate the .rbf file on any other drive.

The only workaround to this issue is to stop the Windows Installer from generating any rollback files. The below steps needs to be followed to stop Windows Installer from creating rollback files.

Navigate to HKEY_LOCAL_MACHINE\Software\Policies\Microsoft\Windows\Installer\DisableRollback in Registry Editor and create a new DWORD value called DisableRollback and set its value to 1.

After creating this value, the rollback files will not be created for any msi files. Since the post did not get updated after my response, I assume that the issue got resolved because of this workaround.