Tag Archives: sql 2008

Add node fails with Attempted to read or write protected memory error

Recently I had worked on an issue where the end user was trying to add a third Node to an existing 2 node cluster. Soon after the setup is intiated, it would fail with the following error message (snippet from the Summary.txt).

Exception type: System.AccessViolationException
    Message: 
        Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
    Data: 
      DisableWatson = true
    Stack: 
        at Microsoft.SqlServer.Chainer.Infrastructure.MsiNativeMethods.MsiOpenPackageEx(String szPackagePath, UInt32 dwOptions, UInt32& hProduct)
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.SetCommonProperties()
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.RunMsiCore(String commandLine)
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.RunMsiWithRetry(String commandline)
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.RunMsi(IEnumerable`1 commandLineProps)
        at Microsoft.SqlServer.Configuration.SetupExtension.MSIInstallerEngine.InstallPackage(PackageId pkg, InstallAction pkgAction)
        at Microsoft.SqlServer.Configuration.MsiExtension.PackageInstallAction.Execute(String actionId, TextWriter errorStream)
        at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun)

“Attempted to read or write protected memory. This is often an indication that other memory is corrupt.” Scary little error message! Since this is a setup related issue, the best place to start troubleshooting it is from the Setup Logs.  This article has more details on how to read SQL Server Setup logs.

Detail.txt pointed in the right direction as seen below.

Opening existing patch 'c:WindowsInstaller97363c.msp'.
Couldn't find local patch 'c:WindowsInstaller97363c.msp'. Looking for it at its source.
Resolving Patch source.
SOURCEMGMT: Looking for sourcelist for product {D7C6A337-F6BB-46CB-AE32-204DD6A8825D}
SOURCEMGMT: Trying source c:7cb5fee2d4775f9d53c7f95659x64setup.
Note: 1: 2203 2: c:7cb5fee2d4775f9d53c7f95659x64setupsql_ssms.msp 3: -2147287037 
SOURCEMGMT: Source is invalid due to missing/inaccessible package.
Unable to create a temp copy of patch 'sql_ssms.msp'.
Searching provided command line patches for patch code {D7C6A337-F6BB-46CB-AE32-204DD6A8825D}
Could not find source for missing patch {D7C6A337-F6BB-46CB-AE32-204DD6A8825D} -- orphaning this patch
SequencePatches starts. Product code: {72AB7E6F-BC24-481E-8C45-1AB

Now it was very clear that the issue is the classic missing MSI/MSP file issue. It was just matter of copying the right files to C:WindowsInstaller as explained here and here.

Once the missing files were replaced, the setup completed without any further errors.

SQL Server Install | Use Role Management Tool to install Microsoft .Net framework 3.5 SP1

I was trying to setup SQL Server 2008 on my lab machine running on Windows Server 2008 R2. When the setup was installing the prerequisites, the following error message popped up.

.net2

This is a very simple error message, which a DBA would come across often. However the next screen which popped up after this message was interesting.

.net1

Since Windows Server 2008 R2 has the .Net framework 3.5 already available as a feature, all we need to do is just enable that feature. There is no need to download the installer and install it separately. As the error message states, just enable it using the “Role Management Tool”. Role Management tool can be found under Server Manager –> Features –> Add features as seen in the following screenshot.

.net3

.net4

That’s all. Now the .Net framework 3.5.1 is installed and ready to use! After this the SQL Server setup completed successfully.

When was SQL Server restarted?

Over the weekend, I interacted with some of the “wanna be” DBAs. During the interaction I checked with them how they would find when was the SQL Server instance restarted? Everyone had a different approach to find out the information. Here are some of the ways to find out the date & time on which SQL Server service was last restarted.

SQL Server Error Log

By making use of sp_readerrorlog, first time stamp in the SQL Server Error Log can be treated as the time when SQL Server service was restarted.

sp_readerrorlog 0,1,'Copyright (c)'

This approach is not useful if the SQL Server Error Log is cycled manually.

Using sys.dm_os_sys_info DMV

Starting SQL Server 2008, sys.dm_os_sys_info DMV has a column named sqlserver_start_time. This column as the name suggests stores the time when SQL Server was restarted.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

But this query does not work in versions which are lower than SQL Server 2008.

Using sys.dm_exec_sessions DMV

The following query will get the Login time of the Session Id 1.

SELECT login_time FROM sys.dm_exec_sessions
WHERE session_id = 1;

session_id is created when the SQL Server is started and the login time remains the same until the service is restarted again.

Start time of the Default Trace

The Default Trace is started when the SQL Server is started. The start_time of this trace can also be taken as the time when the SQL Server service was restarted.

select start_time from sys.traces
where is_default = 1

This approach is useful except for the instances where the Default Trace is explicitly disabled.

Creating date of tempdb

The creation date of tempdb database will also be the time when SQL Server service was restarted. This is because the tempdb database is re-created whenever the SQL Server service starts up.

SELECT create_date FROM sys.databases WHERE name = 'tempdb'

The above query works on SQL Server 2005 and above. For SQL Server 2000 the same query will vary a little bit.

SELECT crdate FROM sysdatabases WHERE name='tempdb'

This is the query which I always use to find out when the SQL Server service was restarted.

Script to check database user permissions

On Sunday I was responding to a question in one of the forums where the jobs were failing with permission denied error. During the troubleshooting I wanted to check if the permission for those stored procedures were explicitly denied for the SQL Server Agent/Job owner account.

On SQL Server 2005 and above, I usually use the below script to check the permissions granted/denied for database users.

SELECT
USER_NAME(dppriper.grantee_principal_id) AS [UserName],
dppri.type_desc AS principal_type_desc,
dppriper.class_desc,
OBJECT_NAME(dppriper.major_id) AS object_name,
dppriper.permission_name,
dppriper.state_desc AS permission_state_desc
FROM    sys.database_permissions dppriper
INNER JOIN sys.database_principals dppri
ON dppriper.grantee_principal_id = dppri.principal_id

The output of this script would be like the one in this example.

But on that instance, the permission was not denied for any of the stored procedures. Still trying to figure out the possibilities.

Hide SQL Server Instance on all versions

It is holiday season everywhere. It means that Freeze on Change Controls. Nothing changes so nothing breaks. Also the instances my team supports have been very supportive this holiday season. Hence most of my recent posts have been inspired by the questions that I respond in the forums, not from the issues that I face. This one is also a detailed version of the answer that I had posted.

While trying to connect to an instance using SQL Server Management Studio (SSMS), it *looks* for all SQL Server instances available on the network. Similarly sqlcmd -L command lists the SQL Server instances available on the network.

Due to some reasons you may want to hide these instances so that they do not get listed in SSMS or sqlcmd. Here is how to do it on various versions of SQL Server.

SQL Server 2000

  • Open Server Network Utility
  • Select the properties of TCP/IP protocol
  • Select the Hide Server option.

For this change to take effect, the SQL Server service needs to be restarted. This hides the instance alright but there is a drawback. The Default port gets changed to 2433 and this cannot be changed! Certainly not an good thing to try out on Production Instances.

SQL Server 2005 & above

Starting SQL Server 2005, the feature to “hide” an instance is available in SQL Server Configuration Manager.

Unlike SQL Server 2000, there is no drawback with this. However the service needs to be restarted after making this change. The instance can also be hidden through Registry.

The location of the key (MSSQL.1 in this example) changes according to the Instance Name.

It should be noted that *hiding* the instance will only prevent it from getting listed in the SSMS or in sqlcmd/osql. It does not prevent the users from connecting to it by directly entering the instance name.

Server is in script upgrade mode. Only administrator can connect at this time

Yesterday I replied to a question in which the user was not able to connect to the SQL Server 2008 instance after installing Service Pack 2. The error message is

Login failed for user ‘LoginName’
Reason: Server is in script upgrade mode.
Only administrator can connect at this time.

I was able to quickly respond to this question because I had faced this situation when I had installed SQL Server 2008 SP2 on my VM. Here is what I had done during the installation of SP2.

  • SQL Server service was stopped prior to SP2 installation (this service is in manual mode in my VM, hence I forgot to start it)
  • SP2 was successfully installed on the instance
  • I started the SQL Server service
  • Immediately I launched SSMS to connect to the instance and I got the above error.

This issue happens because the SQL Server service was stopped when the SP2 installation was in progress. Service Pack installation completes successfully but certain scripts (most of the times sqlagent100_msdb_upgrade.sql) in the Service Pack will be applied only after the SQL Server service starts the next time. Hence when I started the SQL Server service, these scripts were still being applied to the instance and I was trying to connect to the instance during that time. Hence the error message.

What is the resolution? Just wait! Wait for few minutes after the SP is installed and you start the SQL Server service for the first time. Once these scripts are applied to the instance, all the users will be able to connect to the instance normally.

There is already a BUG reported for this issue and it is under “consideration”. Even I feel that the installer should finish its work before reporting that it is “successful”.

I have seen many DBAs stopping the SQL Server services before applying any Service Packs/hotfixes. In fact there is no need to do it. The Service Pack installer will take care of stopping and starting the instance as and when required. This issue is a reason for not stopping the service before applying the SP. Even though there is not much difference, for sure it will prevent a few anxious moments while trying to connect to the instance for the first after installing the SP

Missing domain group error while installing SQL Server 2008 failover cluster

One of the regular readers of my blog $sammy was trying to install SQL Server 2008 in a clustered environment on his Virtual Machine. The underlying operating system was Windows Server 2003. He had configured all the prerequisites correctly. But the Setup was not progressing beyond the Cluster Secutiry Policy screen. Even after giving the correct domain groups in this screen, he was getting an error.

The error message read

Missing domain group.
To continue, provide a valid domain group for the service.

When I looked at this error, I checked with him if the Domain Group was valid and it had the SQL Server Service Account as its member. He confirmed that it was a valid group and the startup account was already a member of that group. Moreover he had granted that group necessary privileges on the domain (enjoying the liberty of installing this on his on VM). He tried adding the group by clicking on the […] button on all the three text boxes and also tried copy/pasting the group name in all the fields. Same error. I was not sure what could be the reasons behind it and could not dig deeper into the issue, since I got caught up with several “production” instance issues.

A day after I got an email from him stating that he got past that error and I was surprised to see the workaround.

  • Click on the browse […] button in the first field i.e. Database Engine domain group text box and select the correct Domain Group.
  • Then copy the Domain Name from the first field and paste it on the next two fields (should not click on […] button)

No more error and the setup progresses to the next step. This could not get any weird than this. One of the most strange behavior I have seen in SQL Server setup!