Backup Failures in OLAP

I've recently come across backup failures on an SSAS database with the below error...

"Memory error: Allocation failure : Not enough storage is available to process this command."

So I was thinking about the errors & think these can be gotten around to a degree.

In AS there is a setting called MemoryLimitErrorEnabled that controls whether an error will be generated if the estimated memory needed for an operation can’t be granted. See this technet article for more in-depth info.

I’m pretty sure this is causing at least some of the errors. Since this is for estimated memory usage only there shouldn't’t be any detrimental effect to disabling it for backups (presumably you won't be processing cube dimensions while taking backups). The worst case is the backup will just fail a bit later when it actually runs out of memory; at best the backup will succeed & not require manual intervention or a restart.

The plan would be to

1. set MemoryLimitErrorEnabled to false

2. take a backup

3. set MemoryLimitErrorEnabled back to true

Setting MemoryLimitErrorEnabled does not require a restart to take effect & can be scripted.

I've tested this method on a Development server & it worked perfectly.

Quick update, on implementing this solution I realized that the workflow of the SQLAgent job would need to change. This workflow is designed to always leave MemoryLimitErrorEnabled - true regardless of whether the backup step succeds or fails. Please forgive the roughness of the document, but I don't have access to Visio.

SQLSaturday 21 - Encrypting SSIS Connection Strings

I had the chance to speak at the recent SQLSaturday in Orlando on Encrypting SSIS Connection Strings. Many thanks to Andy Warren for giving me the opportunity.

As always the day was top notch all the way. Andy & team put on a great event.

I've commented the code used for the demo (it's fully functional, just needs the passwords & file path for the master key backup changed) with enough information to implement the solution & placed it here.

Thanks again to Andy & team for a great event. Also a special thanks for all those who attended my session & restrained from any rotten fruit throwing :-).

Finding Linked Servers with Open Mappings to SA

Recently I've been working on implementing PBM (Policy Based Management) in our environment. In addition to using the predefined conditions & facets I've been creating custom Policies using executesql().

As part of this effort we wanted a policy to alert us to Linked Servers with an open mapping to SA. Since this isn't something that was really obvious it seemed worthy of a quick post. There are 2 versions of the code, one for SQL2000 & one for SQL2005. The code is written with PBM in mind so parses as one string, you can take this code out & it will still work just fine. The code is here.

Don't forget SQLSaturday #21 in Orlando is less than 2 weeks away.

SQLSaturday #21 is Coming to Orlando...

...On October 17th.

For anyone who's not familiar with SQLSaturday, it's a free (a $10 fee for lunch is being charged this year due to funding challenges) all day event for SQL Pro's & those wanting to expand their SQL skills. There's a wide variety of sessions throughout the day organized in "tracks". For example there's a track for Administration which focuses more on, well, the administrative aspects of working with SQL & a track on SSIS which focuses on ETL. The sessions cover a broad range of depth & there are sessions suitable for beginners all the way through advanced users.

I had the privilege of attending the very first SQLSaturday & several more since then & I can't recommend it highly enough. Top notch speakers (for example Andy Warren, Brian Knight, Buck Woody & Kendal Van Dyke to name a few), lots of info, lots of fun & you certainly can't beat the cost. In today's economic climate, with training budgets being slashed or eliminated this is a great way to pick up high quality training for the cost of lunch.

In addition to the free event, EndToEnd Training is also presenting 5 full day seminars the week leading up to SQLSaturday. Each seminar is $149 with a 20% discount to those that attend 2 or more sessions.

<shameless plug>
This year I'll be presenting a 15 minute session on Encrypting SSIS Connection Strings.
</shameless plug>

So, get signed up & plan on being in Orlando on the 17th. Trust me, you'll thank yourself.

Checking SQL & SSIS Versions with Powershell

I'm currently in the process of applying SP3 + CU5 to all our SQL2005 Instances. I wanted to be able to quickly check whether the install was successful (we're experimenting with using SCCM to push to our standalone Instances).

Checking SQL was easy enough, just using @@version in a multi-query was enough. However SSIS is a little tougher, you can't check it via T-SQL and I don't have the patience to login to every machine and check it that way.

The solution I came up with uses SERVERPROPERTY('ProductVersion') to check the SQL build & [system.diagnostics.fileversioninfo]::GetVersionInfo() to check the Product version on the SSIS executable.

It returns a table with the Instance, Version, SQL & SSIS Builds and Notes (for example whether SSIS is installed & if installed do the SQL & SSIS Builds match). The code is here.

Finding Event ID's

A while back, I had the need to look for a particular EventID across all our SQL servers. The script I came up to do the work is here.

This was one of my earliest efforts so its a bit primitive, but works just fine. The main working portion of the script is a straighforward query of the Win32_NTLogEvent wmi class.

The fun part was converting the wmi time into something more usable/readable. I did this in a function that breaks the wmi time into discrete chunks & then strings them all back together in my preferred format. Additionally it modify's the new date/time to account for daylight savings.

Function Convert-WMITime
{
    param([string]$WMITime)

    #Break Date & Time into discreet elements
    $ds = [string]$WMITime.substring(21,4)
    $yr = [string]$WMITime.Substring(0,4)
    $mo = [string]$WMITime.substring(4,2)
    $dy = [string]$WMITime.substring(6,2)
    $tm = [string]$WMITime.substring(8,6)
    $hr = [string]$tm.Substring(0,2)
    $min = [string]$tm.substring(2,2)
    $sec = [string]$tm.substring(4,2)
    
    #Create string in desired format
    $s = "$mo/$dy/$yr " + $hr + ":" + $min + ":" + $sec
    
    #cast result as DateTime type
    $result = [DateTime]$s
    
    #Account for DST
    if ($cp = $ds.Contains("-240"))
    {
        $result = $result.AddHours(-1)
    }
    
    return $result
}

Get Files, Sizes & Percent Free by DB

This week I've been working on our space needs for the upcoming budget year. As part of that effort I spent a few minutes writing a script to return infrmation on database files; including file group, logical name, physical name, allocated size, used size & percent free space for each file. The code is here.

In the next few weeks I'm planning to do a write up on the entire process I used to generate my space requests, it involves several pieces, inluding a process to populate database size (this is not mine).