Change Data Capture gets disabled when you break mirroring and bring the database online

  Issue ================   Recently we encountered an issue where customer performed a planned failover for a mirrored database and after the failover the new principal went into suspect mode because of some reason (which is outside the scope of this blog) and was not accessible. The database which was mirrored had CDC(Change Data Capture)…


How to monitor deadlock using extended events in SQL Server 2008 and later

Before I talk about how to monitor the deadlocks let us have a small brief what is deadlock and how we were handling them in the previous versions of SQL Server.   Deadlock: A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within…

9

Can I listen to you Mr. MSDTC?

MSDTC unlike on earlier version of Windows, support multiple instances on Windows 2008. You can create a Clustered MSDTC resource in each Cluster Group. The below link gives you more information understanding MSDTC resources in Windows 2008 Failover Cluster. http://technet.microsoft.com/en-us/library/cc730992(WS.10).aspx   Some Default Facts: 1.       If there are no clustered MSDTC resources, SQL Server will…

1

Unable to see the properties of DTC in the Component Services

After Preparing and Installing SQL Server 2008 Cluster on a Windows 2008 Server Cluster from the “Advanced” option in the Landing Page, we are unable to see the properties of one of the Instances of DTC in the Component Services UI. It appears blank with the message: No properties are available on this object. We…

1

SQL Server 2008 & SQL Server 2008 R2 clustering FAQ’s:

1.       Can I use SQL 2008 slipstreamed installer to install SQL Server 2008 failover cluster? Yes. Launch the setup.exe from command prompt with PCUSource = <path to extracted SP1>. Refer http://blogs.msdn.com/petersad/archive/2009/03/02/sql-server-2008-basic-slipstream-steps.aspx for additional information. You can use merged slipstream installer for SQL 2008 cluster installation.   2.       If I install failover cluster using slipstream, do…


Installation for SQL Server 2008 Management studio fails with the MSI error code: 0x5EBE5729

SYMPTOMS: ========== Installation for SQL Server 2008 Management studio fails with the MSI error code: 0x5EBE5729   //////////////From Summary.txt//////////////////// Feature: Management Tools – Complete Status: Failed: see logs for details MSI status: Failed: see details below MSI error code: 0x5EBE5729 MSI log file location: <OS Drive>:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\<Iteration ID>\trin_aide_Cpu32_1.log MSI error description: Note:…


SQL Server 2008 Slipstream Setup on Windows Cluster May Fail With “The system cannot find the path specified”

Recently I encountered an issue where the in-place upgrade of the SQL Server 2005 to SQL Server 2008 on the Windows Server 2003 cluster failed with following error:   TITLE: Microsoft SQL Server 2008 Setup —————————— The following error has occurred: The system cannot find the path specified ———- OK ———-   We were using…


SQL 2005 (SQL 2008) Analysis Services Server Side Tracing

With Analysis Services 2005 (2008) you can collect traces without using gui this feature is known as Server Side Tracing. In this article we’ll talk about how to achieve Server Side Tracing. How to create a Server Side Trace Open SQL Server profiler connect to Analysis Services and in an Event Selection define the SSAS…

5

A delete statement in SQL Server 2000 takes around 1.5 GB of Transaction Log Space, Where as in SQL 2008 it can take around 3.2 GB!

There are a lot of Changes in the Transaction Log Architecture of SQL 2000 and SQL 2008. There is a huge difference in the way the log records are maintained in SQL 2000 vs SQL 2005/2008. We keep more information in the Transaction Log in SQL 2005/2008. For example, we do not keep the LOCK…

1