Amazon Deals

Wednesday, May 6, 2015

SQL Server 2008 SP3 Fails with "There was an error generating the XML document" Exit code 2064843076


SQL server 2008 R2 Service Pack 3 installation failed on one node of a cluster.


The Setup Bootstrap log file showed failure message with exit code 2064843076

Log summary:
Instance xxxxxxxxx overall summary:
  Final result:                  The patch installer has failed to update the shared features. To determine the reason for failure, review the log files.
  Exit code (Decimal):           2064843076
  Exit facility code:            787
  Exit error code:               324
  Exit message:                  There was an error generating the XML document.
  Requested action:              Patch

The clue to the problem were the following statements in Details.txt file

2015-04-02 12:31:37 Slp: Checking patch packages for instance "xxxxxxxxx2"
2015-04-02 12:31:37 Slp: Checking patch packages for shared features
2015-04-02 12:31:37 Slp: File locked status check has been canceled by the user.
2015-04-02 12:32:43 Slp: Error: Failed to run patch request for instance: xxxxxxxxx2 (exit code: 2064843076)

2015-04-02 12:30:16 Slp: Sco: Attempting to get feature configuration state for feature SQL_Replication_Core_Inst, instance MSSQL10_50.xxxxxxxxx1
2015-04-02 12:30:16 Slp: Sco: Attempting to get instance feature flag ConfigurationState for feature SQL_Replication_Core_Inst, instance MSSQL10_50.xxxxxxxxx1, machine name xxxxxxxxx2
2015-04-02 12:30:16 Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine xxxxxxxxx2
2015-04-02 12:30:16 Slp: Sco: Attempting to open registry subkey

The Registry hive under the following location

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.xxxxxxxxx2\ConfigurationState

Found values for following components was reflecting as  4





Changed the values to  1 for the above registry locations
Post Changing the value , Rebooted the Node

Applied the patch later it got patched to SP3 successfully

Confirmed the SQL Cluster configuration is fine by doing failover and checking registry as well

Ran Select @@ Version and confirmed SP3  version .

Checked ErrorLog found all services were functional

Monday, March 23, 2015

The Execute Permission was Denied on the Object 'xp_instance_regread', database 'mssqlsystemresource'

Microsoft grants rights to many system stored procedures to Public role and it has been a thorn for IT Auditors. We revoked privileges to xp_instance_regread from Public in SQL Server 2008. However doing the same in SQL Server 2012 produced this error every time a user right clicks on a table in Management studio.

We had to grant these privileges to Public to avoid this error.

Grant Execute ON [sys].[xp_instance_regread] TO [public]

Grant Execute ON [sys].[xp_regread] TO [public]

Wednesday, May 21, 2014

NFS mounted file system owned by nobody:nobody

This can happen if there is a mismatch between how NFS export was created and how it is mounted in your machine. In one situation we encountered the NetApp volume was exported as NFS version 4.0 but the /etc/fstab in Linux server was configured with NFS version 3. When the volume was mounted in Linux, the folder was owned by nobody:nobody and you could not use chown to change it.

The solution was to export the volume from NetApp as NFS version 3 and remount it in Linux.

Tuesday, May 6, 2014

Informatica Session Log - LM_2006 Unable to create log file

An Informatica job failed due to failure to create a session log file. The NFS mount point that hosted the SessLog folder had plenty of space. There was no problem touching a file on the folder as Informatica user, so there was no issue with the permission.

We discovered that we could create files with shorter name but if the file name is longer than 70 characters, it failed. It did not make sense as the NFS mount was on ext3 file system which allowed file names to be 255 characters long.

Upon further investigation we found that session log folder had over a million files. We deleted half the number of files as they were old log. Informatica was happy about it and completed the failing jobs without any issue.

A Linux file system uses inodes to manage files. However running df -hi showed only 20% utilization of inodes, so it is still a mystery to us.

You can use this command to get details on number of inodes

# tune2fs -l /dev/mapper/osvg-rootlv | grep Inode

Monday, April 7, 2014

SQL Server Error: 1222 - Lock request time out period exceeded

This is one annoying error which prevents you from doing anything on SQL Server Management Studio.

When you work on Management studio, your actions may create temporary tables, call number of system stored procedures and if there are any other actions happening on the database your management studio connection may be blocked. After sometime you will get this error.

Old version of SQL Server enterprise manager would simply freeze, so this is kind of an improvement.

In one situation, I had a script running which failed. The script had many DDLs and DMLs interspersed, so when I started to look into changes that were made so far, I was hit with this error.

To come out of this, I simply ran a ROLLBACK on the last query window. The reason I hit this one was due to a bug in the script which created a nested transaction and there was no corresponding COMMIT or ROLLBACK. The script terminated with an error and one OPEN TRANSACTION.

The problem was GO statement after BEGIN TRANSACTION like

DELETE dbo.ApplicationTable WHERE AppID = 3
ALTER TABLE dbo.ApplicationTable ADD Note Char (255)
UPDATE dbo.ApplicationTable set Note='Nothing'

Running SELECT @@TRANCOUNT would return 1. The developer intended to put GO after COMMIT, but had it after BEGIN TRANSACTION.

Monday, March 31, 2014


The goal is to change the schema of a table from dbo to TestUser.

In pre-SQL Server 2005 world I could have used sp_changeobjectowner to change owner of the table. However with SQL Server 2005, the concept of SCHEMA and OWNER comes into picture.

SCHEMA and OWNER may sound like the same thing and you may expect to behave same but they are not.

For example when I run this statement


The TestTable is still in dbo schema, however TestUser becomes the owner with all privileges on the table. You can see the privileges TestUser has by running

  SELECT * FROM sys.fn_my_permissions('dbo.TestTable','OBJECT');

But if you check the sys.sysobjects table the TestTable is still under dbo schema.

select name, USER_NAME(uid) from sys.sysobjects where name='TestTable'

To move the table to TestUser schema, you will have to use ALTER SCHEMA command as


Please keep in mind that when you change the object owner, any other users who had GRANTs on that object will be revoked.

Thursday, March 27, 2014

ORA-01031 Insufficient Privilege when using Toad

You can get ORA-01031 for valid reason if you do not have SYSDBA or SYSOPER privilege and you try to connect to a database as those user type. 

However I ran into this error using Toad 11.6 64-bit on Windows 7 PC when connecting as Normal user. The difference here was I could click OK button and it would open Toad. I could not figure out what was going on. I had another PC with Toad 10.6 that worked fine with the same user and database.

Upgrading to version 12.1 fixed the issue. The database I was trying to connect to was 12c as well, not sure if this error is only happening on 12c with Toad 11.6. Since I already upgraded I can't tell now.