Tom's blog

Thoughts about SQL and related stuff…

Update of the Service Pack and Cumulative Update list SQL 2008

I have updated the Service Pack and Cumulative Update list for SQL 2008 R2 and SQL 2008:

SQL 2008 R2

Build Description Build Description
10.50.1600.1 RTM
10.50.1702.0 R2 CU1
10.50.1720.0 R2 CU2
10.50.1734.0 R2 CU3
10.50.1746.0 R2 CU4
10.50.1753.0 R2 CU5
10.50.1765.0 R2 CU6 10.50.2500.0 SP1 RTM
10.50.1777.0 R2 CU7
10.50.1797.0 R2 CU8 10.50.2769.0 SP1 CU1
10.50.1804.0 R2 CU9 10.50.2772.0 SP1 CU2
10.50.1807.0 R2 CU10 10.50.2789.0 SP1 CU3
10.50.1809.0 R2 CU11 10.50.2796.0 SP1 CU4

 

SQL 2008


Build Desc. Build Desc. Build Desc. Build Desc.
10.0.1600.0 RTM
10.0.1763.0 CU1
10.0.1779.0 CU2
10.0.1787.0 CU3 10.0.2531.0 SP1 RTM
10.0.1798.0 CU4 10.0.2710.0 SP1 CU1
10.0.1806.0 CU5 10.0.2714.0 SP1 CU2
10.0.1812.0 CU6 10.0.2723.0 SP1 CU3
10.0.1818.0 CU7 10.0.2734.0 SP1 CU4
10.0.1823.0 CU8 10.0.2746.0 SP1 CU5
10.0.1828.0 CU9 10.0.2757.0 SP1 CU6
10.0.1835.0 CU10 10.0.2766.0 SP1 CU7
10.0.2775.0 SP1 CU8 10.0.4000.0 SP2 RTM
10.0.2789.0 SP1 CU9
10.0.2799.0 SP1 CU10
10.0.2804.0 SP1 CU11 10.0.4266.0 SP2 CU1
10.0.2808.0 SP1 CU12 10.0.4272.0 SP2 CU2
10.0.2816.0 SP1 CU13 10.0.4279.0 SP2 CU3
10.0.2821.0 SP1 CU14 10.0.4285.0 SP2 CU4 10.0.5500.0 SP3 RTM
10.0.2847.0 SP1 CU15 10.0.4316.0 SP2 CU5
10.0.2850.0 SP1 CU16 10.0.4321.0 SP2 CU6 10.0.5766.0 SP3 CU1
  10.0.4323.0 SP2 CU7 10.0.5768.0 SP3 CU2

Tom

Bookmark and Share

New Service Pack for SQL 2008: SP3

Recently Microsoft has released Service Pack 3 for SQL 2008. I have updated the overview for both SQL 2008 R2 and SQL 2008

I changed the overview a bit, so you can better see the levels of cumulative updates and how they correspond to each other

SQL 2008 R2

Build Description Build Description
10.50.1600.1 RTM
10.50.1702.0 R2 CU1
10.50.1720.0 R2 CU2
10.50.1734.0 R2 CU3
10.50.1746.0 R2 CU4
10.50.1753.0 R2 CU5
10.50.1765.0 R2 CU6 10.50.2500.0 SP1 RTM
10.50.1777.0 R2 CU7
10.50.1797.0 R2 CU8 10.50.2769.0 SP1 CU1
10.50.1804.0 R2 CU9 10.50.2772.0 SP1 CU2

 

SQL 2008


Build Desc. Build Desc. Build Desc. Build Desc.
10.0.1600.0 RTM
10.0.1763.0 CU1
10.0.1779.0 CU2
10.0.1787.0 CU3 10.0.2531.0 SP1 RTM
10.0.1798.0 CU4 10.0.2710.0 SP1 CU1
10.0.1806.0 CU5 10.0.2714.0 SP1 CU2
10.0.1812.0 CU6 10.0.2723.0 SP1 CU3
10.0.1818.0 CU7 10.0.2734.0 SP1 CU4
10.0.1823.0 CU8 10.0.2746.0 SP1 CU5
10.0.1828.0 CU9 10.0.2757.0 SP1 CU6
10.0.1835.0 CU10 10.0.2766.0 SP1 CU7
10.0.2775.0 SP1 CU8 10.0.4000.0 SP2 RTM
10.0.2789.0 SP1 CU9
10.0.2799.0 SP1 CU10
10.0.2804.0 SP1 CU11 10.0.4266.0 SP2 CU1
10.0.2808.0 SP1 CU12 10.0.4272.0 SP2 CU2
10.0.2816.0 SP1 CU13 10.0.4279.0 SP2 CU3
10.0.2821.0 SP1 CU14 10.0.4285.0 SP2 CU4 10.0.5500.0 SP3 RTM
10.0.2847.0 SP1 CU15 10.0.4316.0 SP2 CU5
10.0.2850.0 SP1 CU16 10.0.4321.0 SP2 CU6

Tom

Bookmark and Share

Error while installing SQL 2008: Wait on the Database Engine recovery handle failed

I recently had a strange error when installing a slipstreamed installation of SQL 2008 SP2:

Wait on the Database Engine recovery handle failed

The summary didn’t give a clear explanation:

Overall summary:
  Final result:                  SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup.
  Exit code (Decimal):           -2068643839
  Exit facility code:            1203
  Exit error code:               1
  Exit message:                  SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup.
  Start time:                    2011-08-17 11:08:48
  End time:                      2011-08-17 12:22:59
  Requested action:              Install

 

So I took a look in the detailed setup log, more or less the same:

2011-08-17 12:23:01 Slp: Error result: -2068643839
2011-08-17 12:23:01 Slp: Result facility code: 1203
2011-08-17 12:23:01 Slp: Result error code: 1

 

Apparantly the SQL services have tried to start, and an ERRORLOG was generated:

2011-08-18 11:34:14.28 Server      Error: 17182, Severity: 16, State: 1.
2011-08-18 11:34:14.28 Server      TDSSNIClient initialization failed with error 0×80092004, status code 0×80. Reason: Unable to initialize SSL support. Cannot find object or property.

2011-08-18 11:34:14.28 Server      Error: 17182, Severity: 16, State: 1.
2011-08-18 11:34:14.28 Server      TDSSNIClient initialization failed with error 0×80092004, status code 0×1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

2011-08-18 11:34:14.28 Server      Error: 17826, Severity: 18, State: 3.
2011-08-18 11:34:14.28 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2011-08-18 11:34:14.28 Server      Error: 17120, Severity: 16, State: 1.
2011-08-18 11:34:14.28 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

 

After searching for this on the www, I found some references to a corrupt user profile for the account running SQL Services. I took a look at the profiles and it seemed that for this user, only a temporary profile was created. After looking at the account properties in AD it seemed that there was something weird with it, no user logon name was specified and the pre-windows 2000 logon name had a space at the end.

We deleted the account, re-created it, re-installed SQL and no problems anymore!

Bookmark and Share

Error “System Assertion Check has failed” Msg 3624, Level 17, State 1 when trying to do a log restore with the STOPATMARK option in SQL 2008

Recently our operational team had to do a restore of a database for BizTalk. These databases require a special restore routine, to let the BizTalk components in sync with the DB.

First they restored the latest full backup, which went without problems.

Then they needed to restore the transaction log backups, with the STOPATMARK option set:

RESTORE LOG [Database] FROM

DISK = N’LocationOfBackupFile’

WITH FILE = 1, NOUNLOAD, STATS = 10,

STOPATMARK = N’BTS_2011_06_06_00_00_29_087′ AFTER N’2011-06-06T02:00:32′

GO

which results in an error:

100 percent processed.

Processed 0 pages for database ‘Database’, file ‘Database’ on file 1.

Processed 2 pages for database ‘Database’, file ‘Database_log’ on file 1.

Location:     diskio.c:1769

Expression:   filepos + cBytes <= (DWORDLONG) m_size << PAGESIZEBITS

SPID:         65

Process ID:   1908

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

Msg 3624, Level 17, State 1, Line 1

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.

When restoring the log without the STOPATMARK option, no problems are encountered.

The log mark entries where all filled in correctly. You can see this by querying the dbo.logmarkhistory table in msdb.

We opened a case at Microsoft and they confirmed that this is a bug and is fixed in CU4 for SQL 2008 SP2 (http://support.microsoft.com/kb/2527180). I’ve asked for more information, but they cannot provide it for the moment as it is confidential. I still hope to find some more information though, I’ll update this post with the details.

Tom

Bookmark and Share

New cumulative update packs for SQL 2008 SP1 and SP2

Microsoft recently released two new cumulative updates: CU14 for SQL 2008 SP1 and CU4 for SQL 2008 SP2.

This makes following build table:

Version Service Pack (SP) / Cumulative Update (CU)
10.50.1777.0 R2 CU7
10.50.1765.0 R2 CU6
10.50.1753.0 R2 CU5
10.50.1746.0 R2 CU4
10.50.1734.0 R2 CU3
10.50.1720.0 R2 CU2
10.50.1702.0 R2 CU1
10.50.1600.1 SQL 2008 R2 RTM

 

Current Cumulative Update Packs for SQL 2008:

10.0.4285.0 SP2 CU4
10.0.4279.0 SP2 CU3
10.0.4272.0 SP2 CU2
10.0.4266.0 SP2 CU1
10.0.4000.0 SP2
10.0.2821.0 SP1 CU14
10.0.2816.0 SP1 CU13
10.0.2808.0 SP1 CU12
10.0.2804.0 SP1 CU11
10.0.2799.0 SP1 CU10
10.0.2789.0 SP1 CU9
10.0.2775.0 SP1 CU8
10.0.2766.0 SP1 CU7
10.0.2757.0 SP1 CU6
10.0.2746.0 SP1 CU5
10.0.2734.0 SP1 CU4
10.0.2723.0 SP1 CU3
10.0.2714.0 SP1 CU2
10.0.2710.0 SP1 CU1
10.0.2531.0 SP1
10.0.1835.0 CU10
10.0.1828.0 CU9
10.0.1823.0 CU8
10.0.1818.0 CU7
10.0.1812.0 CU6
10.0.1806.0 CU5
10.0.1798.0 CU4
10.0.1787.0 CU3
10.0.1779.0 CU2
10.0.1763.0 CU1
10.0.1600.0 SQL 2008 RTM
Bookmark and Share

List of current Cumulative Update Packs for SQL 2008 & SQL 2008 R2

Current Cumulative Update Packs for SQL 2008 R2:

Version Service Pack (SP) / Cumulative Update (CU)
10.50.1777.0 R2 CU7
10.50.1765.0 R2 CU6
10.50.1753.0 R2 CU5
10.50.1746.0 R2 CU4
10.50.1734.0 R2 CU3
10.50.1720.0 R2 CU2
10.50.1702.0 R2 CU1
10.50.1600.1 SQL 2008 R2 RTM

 

Current Cumulative Update Packs for SQL 2008:

10.0.4279.0 SP2 CU3
10.0.4272.0 SP2 CU2
10.0.4266.0 SP2 CU1
10.0.4000.0 SP2
10.0.2816.0 SP1 CU13
10.0.2808.0 SP1 CU12
10.0.2804.0 SP1 CU11
10.0.2799.0 SP1 CU10
10.0.2789.0 SP1 CU9
10.0.2775.0 SP1 CU8
10.0.2766.0 SP1 CU7
10.0.2757.0 SP1 CU6
10.0.2746.0 SP1 CU5
10.0.2734.0 SP1 CU4
10.0.2723.0 SP1 CU3
10.0.2714.0 SP1 CU2
10.0.2710.0 SP1 CU1
10.0.2531.0 SP1
10.0.1835.0 CU10
10.0.1828.0 CU9
10.0.1823.0 CU8
10.0.1818.0 CU7
10.0.1812.0 CU6
10.0.1806.0 CU5
10.0.1798.0 CU4
10.0.1787.0 CU3
10.0.1779.0 CU2
10.0.1763.0 CU1
10.0.1600.0 SQL 2008 RTM
Bookmark and Share

SQL Clustered Instances automatic Max Server Memory settings

In our environment we have several SQL clusters, most of them in an active-active configuration.

Microsoft recommends that the sum of all maximum server memory for all instances is equal to the total installed memory – memory reserved for the system. This means that during normal circumstances, more than half of the memory is not used. While I can understand that Microsoft recommends this out of safety, I think it’s stupid to not let SQL profit from extra memory that is ready to be used.

I found some references to Memory Rebalance scripts on the net, but found them to be useless for me, as they either used a default node, were limited to 2 instances or required the nodes to have the same amount of memory installed.

Therefore, I have created a solution that uses ‘weights’ to calculate the maximum server memory for a given instance.

How it works:

  • I select one instance to be the ‘master’ instance. In the master database of this instance, I create a new table which contains the InstanceNames, NodeName on which this instance is currently active and the weight.
    Script:

    USE [master]
    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[MemorySettings]‘) AND type in (N’U'))
    BEGIN
    CREATE TABLE [dbo].[MemorySettings](
        [InstanceName] [varchar](256) NOT NULL,
        [ActiveNodeName] [varchar](256) NOT NULL,
        [Weight] [int] NOT NULL
    ) ON [PRIMARY]
    END
    GO

  • I fill in the InstanceNames, current Active Nodenames and weights in this table, for example:
    ExampleOfInput 
  • Now create on each instance a linked server to the other instances in the cluster.
  • Next I create a stored procedure in each master database. This stored procedure will be set to execute on SQL service start. It will update the MemorySettings database on the master instance and call the sp_MemorySettings procedure on each instance. 

    create proc dbo.sp_DetectInstanceAndNodeSettings
    as
    /*
        Purpose:    This procedure takes the instancename and current running node and places it in
                    the “master” config table.
                    This procedure will be set as autoexecute on startup of each instance.
                    The latest step will be a cursor to call sp_SetMemorySettings on each instance
        PreReq:        Table “MemorySettings” on a “master” instance
        Version:    0.1 Initial Version
        Author:        Tom Van Zele (tvz@live.be)
    */

    /* Step 1: Update the “master” table with the ActiveNodeName */

    declare @InstanceName as varchar(256)
    declare @ActiveNodeName as varchar(256)

    set @InstanceName = (select @@SERVERNAME)
    set @ActiveNodeName = (select CAST(SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as varchar(256)))

    UPDATE [YourMasterInstance].master.dbo.MemorySettings /* Replace [YourMasterInstance] with the name of your “master” instance */
    SET ActiveNodeName = @ActiveNodeName
    WHERE InstanceName = @InstanceName

    /* Step 2: call all registered instances to execute sp_SetMemorySettings in order to set their MaxMemory */
    declare @SQLToExecute varchar(1000)
    declare @InstanceNameToCheck varchar(256)

    declare AllInstancesCursor CURSOR for select distinct InstanceName from [YourMasterInstance].master.dbo.MemorySettings /* Replace [YourMasterInstance] with the name of your “master” instance */

    open AllInstancesCursor
    fetch next from AllInstancesCursor into @InstanceNameToCheck
    while @@FETCH_STATUS=0
    begin
        set @SQLToExecute = ‘EXEC [' + @InstanceNameToCheck + '].master.dbo.sp_MemorySettings’
        print (@SQLToExecute)
        exec (@SQLToExecute)
        fetch next from AllInstancesCursor into @InstanceNameToCheck
    end
    close AllInstancesCursor
    deallocate AllInstancesCursor

    GO

      exec sp_procoption @ProcName=[‘sp_DetectInstanceAndNodeSettings’], @OptionName=‘STARTUP’, @OptionValue = [on]
    GO

  • Then finally we create the sp_MemorySetting stored procedure in the master database of each instance in the cluster. This proc will check the current ActiveNode, detect what other instances are active on the same node and will calculate and set the Maximum Server Memory, based on the weights.
     
  • create proc dbo.sp_MemorySettings
    as
    /*
        Purpose:    This procedure will adjust memory settings based on the “master” instance input
        PreReq:        Table “MemorySettings” on a “master” instance
        Version:    0.1 Initial Version
        Author:        Tom Van Zele (tvz@live.be)
    */

    /* Step 1: Calculate our max memory setting */
    declare @InstanceName as varchar(256)
    declare @ActiveNodeName as varchar(256)
    declare @Weight as dec(10,2)
    declare @SumOfWeightOnActiveNode as dec(10,2)
    declare @Factor as dec(10,2)
    declare @MemoryInNode as int
    declare @MaxMemory as int
    declare @ReservedForSystem as int 
    set @ReservedForSystem = 2048 — Reserved memory for system, change as you like

    set @InstanceName = (select @@SERVERNAME)
    set @ActiveNodeName = (select CAST(SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as varchar(256)))

    select @Weight =  [Weight] from [YourMasterInstance].master.dbo.MemorySettings where InstanceName = @InstanceName

    /* Replace [YourMasterInstance] with the name of your “master” instance */
    – print @Weight
    select @SumOfWeightOnActiveNode = SUM(Weight) from [YourMasterInstance].master.dbo.MemorySettings where ActiveNodeName=@ActiveNodeName

    /* Replace [YourMasterInstance] with the name of your “master” instance */

    – print @SumOfWeightOnActiveNode
    select @Factor = @Weight/@SumOfWeightOnActiveNode
    – print (@Factor)

    select @MemoryInNode = physical_memory_in_bytes/1048576 from sys.dm_os_sys_info
    – print @MemoryInNode

    select @MaxMemory = (@MemoryInNode-@ReservedForSystem)*@Factor
    – print @MaxMemory

    /* Step 2: Set our calculated MaxMemory value */
    exec sp_configure ‘show advanced option’,’1′;
    RECONFIGURE;
    exec sp_configure ‘max server memory (MB)’,@MaxMemory
    RECONFIGURE;
    exec sp_configure ‘show advanced option’,’0′;
    RECONFIGURE;

    GO

 

Known issue:

If you have a SQL 2008, a linked server is not automatically configured to accept RPC (You get an error that Server [servername] is not configured for RPC). To change this, please execute the following:

exec sp_serveroption @server=[YourInstanceName], @optname=’rpc’, @optvalue=’true’
exec sp_serveroption @server=[YourInstanceName], @optname=’rpc out’, @optvalue=’true’

So far the script works fine for me. If you have any remarks/better ideas, feel free to add a comment!

Tom

Bookmark and Share

Feedback results of my last presentation

I recently received the feedback results of the presentation I gave for the Belgian SQL Users Group.

3 questions were asked:

  • Fulfilled this evening your technical expectations?
  • How do you evaluate the speaker about his presentation skills?
  • How do you evaluate the speaker about his knowledge of the subject?

The results: image

What I can conclude is that I should work on my presentation skills (which I already knew). I have the tendency to speak very fast when I’m nervous.

Something I don’t understand very well is that I got 2 scores of 3 for ‘Fulfilled this evening your technical expectations?’, where most of the scores were 7 or 8. It seems that I have really disappointed these 2 persons. If you are one of them, please feel free to contact me, so I can learn what I did wrong, or what I can do to improve this!

Tom

Bookmark and Share

Presentation Belgian SQL Users Group: SQL Server Forensics – Playing CSI with a database

Last week I gave a presentation to the Belgian SQL Users Group about database forensics.

You can download the presentation here.

Thanks to Kevvie Fowler, I may also put the contents (demo databases, forensic scripts) available for download. The included scripts are also published on the Application Forensics website, check there for updates.

If you want to learn more about the subject, take a look at the SQL Server Forensic Analysis book!

Tom

Bookmark and Share

Detailed Step-by-Step guide to create an HA/DR configuration in Denali CTP1

In this guide, I will instruct step-by-step on how to create an HADR configuration between Denali SQL servers. For more information about this new high availability type see my post: CTP1 for SQL ‘Denali’ available for download

First a little overview of my test environment, I’m running Windows 2008 R2 Hyper-V on a laptop. The host is also a domain controller and has the Microsoft iSCSI Software Target running to provide shared disk storage via iSCSI to my guest machines. The iSCSI Software Target is part of Storage Server, but can be installed separately.

I have 4 Hyper-V guests running, 2x 2 nodes within a Windows Failover Cluster (requirement for Denali HADR) with denali clustered.

So let’s start with our setup:

1. Enable the HADR service on both clustered denali instances 

  • Open SQL Server Configuration Manager
  • Select SQL Server Services
  • Right-click on your SQL Server, in my case SQL Server (INST1) and select Properties SreenCapture_04
  • Select the SQL HADR tab and click the checkbox Enable SQL HADR Service SreenCapture_05
  • Click Ok on the warning dialog box
    SreenCapture_06
  • Restart your SQL Services (Attention: SQL HADR requires a Windows Failover Cluster, so do not stop the service through the Services Control Panel, but go to the Failover Cluster Manager, select your SQL Server Instance, and take it offline. When everything is offline, bring it online again)

2. Create an Availability Group

  • Open SQL Server Management Studio
  • Select Management
  • Right-click Availability Groups and select New Availability Group SreenCapture_07
  • Click Next
    SreenCapture_08
  • Give your new Availability Group a name and click Next SreenCapture_09
  • Select which user databases you want to add to your Availability Group. If your database is not listed, you can select Show user databases not meeting requirements. This will give you the reason why a certain db cannot be added to the AG. Then click Next SreenCapture_10
  • In the Specify Replicas screen, you can add the instances you want to be enable as HADR in the secondary role, after you have done this, click Next
    SreenCapture_11
    SreenCapture_13
  • Next you have the overview screen, click Finish to start configuring the HADR setup
    SreenCapture_14
  • Next you get a progress screen, click Next to finish the HADR setup.
    SreenCapture_15

That’s it for this guide. I will continue playing with HADR and post some reviews afterwards.

Your remarks are welcome.

Tom

Bookmark and Share