Archive

Posts Tagged ‘SQL’

SQL 2012 Analysis Services Error: "No connection could be made because the target machine actively refused it"

May 15, 2013 2 comments

In Microsoft SQL Server 2012, you receive an error that resembles the following when you try to connect to a named instance of SQL Server Analysis Services (SSAS) :

No connection could be made because the target machine actively refused it: Port number

 

This issue can occur if the server that hosts the named instance of SSAS was configured to use IPv4 and IPv6 when SQL Server 2012 was installed. Then, the server was later reconfigured to use only IPv6.

 

To resolve this issue, follow these steps:

  1. Stop the SQL Server Analysis Services service.
  2. Open the Msmdredir.ini file in Notepad.
    Note By default, the Msmdredir.ini file is located in the following folder:

    %ProgramFiles%\Microsoft SQL Server\90\Shared\ASConfig

  3. In the Instances section, verify that the values for the Port property and the IPv6 property are different for the named instance.
  4. Delete the PortIPV6 property.
  5. Save the Msmdredir.ini file, and then exit Notepad.
  6. Start the SQL Server Analysis Services service.

 

Source KB

Advertisements

DPM 2012 Rollup Update 3 Failed to execute SQL string

November 30, 2012 6 comments

We faced that during our Private cloud update to SC 2012 SP1

trying to install Rollup Update 3 to DPM 2012 server we got this error

MSI (s) (28!F4) [16:08:33:788]: Note: 1: 2262 2: Error 3: -2147287038
ExecuteSqlStrings:  Error 0x80040e2f: failed to execute SQL string, error: The statement has been terminated., SQL key: sqlscript_KB2751230Fix.sql SQL string: insert into tbl_DLS_GlobalSetting
(PropertyName, PropertyValue)
values (‘ConsiderOfflineMediaToCheckOffsiteReady’, ‘1’)
MSI (s) (28!F4) [16:08:33:788]: Transforming table Error.

MSI (s) (28!F4) [16:08:33:788]: Transforming table Error.

MSI (s) (28!F4) [16:08:33:788]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (28!F4) [16:08:33:788]: Product: Microsoft System Center 2012 – Data Protection Manager — Error 26204. Error -2147217873: failed to execute SQL string, error detail: The statement has been terminated., SQL key: sqlscript_KB2751230Fix.sql SQL string: insert into tbl_DLS_GlobalSetting
(PropertyName, PropertyValue)
values (‘ConsiderOfflineMediaToCheckOffsiteReady’, ‘1’)

 

it seem that the update running a script which need to insert a property in a database table as this error state

ExecuteSqlStrings:  Error 0x80040e2f: failed to execute SQL string, error: The statement has been terminated., SQL key: sqlscript_KB2751230Fix.sql SQL string: insert into tbl_DLS_GlobalSetting

(PropertyName, PropertyValue) values (‘ConsiderOfflineMediaToCheckOffsiteReady’, ‘1’)

so we try to run a query on the Database to check this property value and found that its already exist with same value which prevent the update from continue updating the DB

Select * from tbl_DLS_GlobalSetting where PropertyName = ‘ConsiderOfflineMediaToCheckOffsiteReady’

so we backed up the database and delete this RAW from the effected table:

Delete from tbl_DLS_GlobalSetting where PropertyName = ‘ConsiderOfflineMediaToCheckOffsiteReady’

then the update went successfully 🙂

Categories: DPM Tags: ,

SQL Server / Dynamic Memory Whitepaper

Today SQL Server team published a whitepaper about considerations for Dynamic Memory in SQL Server VMs.

You can download the paper from:

Running SQL Server with Hyper-V Dynamic Memory – Best Practices and Considerations

Or

http://sqlcat.com/whitepapers/archive/2011/08/01/running-sql-server-with-hyper-v-dynamic-memory-best-practices-and-considerations.aspx

Summary: Memory is a critical resource to Microsoft SQL Server workloads, especially in a virtualized environment where resources are shared and contention for shared resources can lead to negative impact on the workload. Windows Server 2008 R2 SP1 introduced Hyper-V Dynamic Memory, which enables virtual machines to make more efficient use of physical memory resources. Hyper-V Dynamic Memory treats memory as a shared resource that can be reallocated automatically among running virtual machines. There are unique considerations that apply to virtual machines that run SQL Server workloads in such environments. This document provides insight into considerations and best practices for running SQL Server 2008 R2 in Hyper-V Dynamic Memory configurations on Windows Server 2008 R2 SP1.

 

I am not SQL Expert but I will try to provide any hints or comments on this Whitepaper

 

Enjoy it Smile

Is Hyper-V Dynamic Memory supported for SQL Server?

February 24, 2011 1 comment

Simply YES

Q7: Is Hyper-V Dynamic Memory supported for SQL Server?

A7: Hyper-V Dynamic Memory is fully supported with SQL Server. Only SQL Server versions and editions (Enterprise and Datacenter) that support Hot Add Memory can see memory that is added by using Hyper-V Dynamic Memory. SQL Server versions that do not support Hot Add Memory are still supported. But these versions will detect only the memory that is present in the operating system when SQL Server starts. Before you deploy Hyper-V Dynamic Memory, please read the following resources when you use Hyper-V Dynamic Memory with SQL Server:

SQL Server Consolidation with Microsoft Virtualization

January 27, 2010 3 comments

Source

Hello, my name is Vipul Shah and I’m a Senior Product Manager with the Virtualization Team.

Due to its ability to drive down costs and drive up resource usage, Microsoft SQL Server consolidation is top of mind for our customers these days. Microsoft virtualization, which includes Windows Server 2008 R2 Hyper-V and System Center, is one of the well known mechanisms to enable this. Today, Ted Kummert, Senior Vice President, Microsoft Business Platform Division, released a video (click here) that outlines how virtualization enables consolidation.

So the natural question is – can we achieve higher amounts of throughput as we consolidate? Can we improve the throughput with recent advances in hardware and the recent release of Windows Server 2008 R2 Hyper-V?

To address this, we recently performed tests that are discussed in the Best Practices for SQL Server Virtualization webcast (click here) and in the SQL Server Consolidation Guidance (click here).

In our tests, we ran a complex stock trading application workload on servers with Second Level Address Translation (SLAT). In physical environments, the operating system translates virtual memory addresses to physical addresses. However with virtualization, we have an additional translation (the second level address translation) because you are running operating systems within virtual machines. This means additional CPU cycles are spent doing this translation. The SLAT enabled processors complete this translation within the silicon, leading to performance advantage compared with non-SLAT enabled CPUs. You get these processors from both Intel and AMD.

We chose a 16-core HP DL585 server with SLAT-enabled AMD processors with HP EVA 8000 storage running Windows Server 2008 R2 Hyper-V. We created virtual machines (VM) each with 4 virtual processors and 7 GB RAM using a fixed-sized VHD format. We started to run our workload with one VM and gradually increased the load, adding more VMs as we went along. We found that we were able to increase the throughput with consolidation. The workload scaled near-linearly up to 4 VMs consuming all of the physical cores on the server (16 cores total). Then we added even more VMs, consolidating up to 8 VMs. We over-committed virtual-processors to physical-cores ratio by 2:1. We were able to run heavy load (3000 batch requests per second), consuming about 70% CPU on the server. The tests also found that Windows Server 2008 R2 offered improved performance than the prior release as shown by the dotted red-line in the graph.

Microsoft virtualization (Hyper-V and System Center) combined with advances in hardware technology (such as SLAT-enabled technology) can provide a solid consolidation platform for production workloads using SQL Server.

Microsoft continues to work with partners to offer solutions that help our customers realize the benefits of virtualization (click here). Further guidance from our partners will be forthcoming. For more resources on virtualizing Microsoft server applications, click here.

Vipul Shah

Microsoft Virtualization Team, Senior Product Manager

Updated -VMM Tricks: Remove failed jobs from VMM console

October 28, 2009 Leave a comment

VMM maintains job history for the past 90 days.

If you want to remove them manually, we need take the following steps to update directly in SQL DB. These steps are valid when you are using SQL Express for VMM DB.

1. Download SQL Management Studio Express from the following link and install in SCVMM
http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en
2. Open the management studio console and connect to <SCVMM server>\microsoft$vmm$
3. Select Databases > VirtualManagerDB

SQL Management Console

4. Click new query within the console and input the following command.
UPDATE tbl_TR_TaskTrail SET IsVisible = 0 WHERE TaskState = ‘Failed’

5. Click Execute, it will hide all the failed status job.

Delete Failed Jobs

6. Now re-open the SCVMM console and the failed jobs should be invisible.

Update: 8 Sep 2010

CAUTION!

Running this statement against the VMM db will cause TaskGC to no longer clean up jobs!

The command below, mentioned above, does not actually remove any jobs from the database. It merely causes them to not appear. This results in an ever increasing log of jobs that will not be correctly removed with TaskGC. One customer I worked with performed this operation and ended up with hundreds of thousands of jobs in the database. The highest number you would want is around ten thousand.

Do not use this command:

UPDATE tbl_TR_TaskTrail SET IsVisible = 0 WHERE TaskState = ‘Failed’;

The right way to clean up jobs:

SCVMM service may consume high memory or CPU utilization

http://blogs.technet.com/b/mbriggs/archive/2009/12/08/scvmm-service-may-consume-high-memory-or-cpu-utilization.aspx?wa=wsignin1.0

%d bloggers like this: