New builds of SQL 2005 Service Manager are now available that fix an issue with Analysis Services and SSIS discovery

• Download SQL 2005 Service Manager (1.0.0.12) for XP/2003/2008
  
• Download SQL 2005 Service Manager (2.0.0.3) for Vista
  
• Download Source Code

 

The February CTP of SQL 2008 is now publicly available and can be downloaded from the link below. If you've been holding off trying out SQL 2008 this is a great release to give a try as it is practically feature complete and will let you try out all the new features of SQL 2008.

 Download SQL 2008 Feb CTP

 

After you apply SQL 2005 Cumulative Update 3 (3186), members of the processadmin fixed server role can no longer kill spids (which since that is all that role allows you to do effectively makes it useless). This is supposedly fixed in Cumulative Update 4 (though I haven't tested it myself). What is annoying is that there is no mention of this that I can find in the KB article for Cumulative Update 4 so the only way to confirm the bug and find out that it got fixed is to raise a case with PSS. There needs to be more transparency when regression bugs are introduced and then silently fixed.

The SQL 2005 September 2007 BOL update is now (finally since it's almost December!) available for download. You can download it from the link below

• Download SQL 2005 BOL September 2007 Update

 

New versions of SQL 2005 Service Manager are now available for WinXP/Win2003/Win2008 and Vista. The only changes in these new releases are the addition of SQL 2008 support and Windows 2008 support for the non Vista version. As it turned out, adding SQL 2008 compatability only required a single line to be changed (and it could be argued that was a bug!). Download links below as well as the link to the Codeplex site where you can download the source code.

• Download SQL 2005 Service Manager (1.0.0.11) for XP/2003/2008
  
• Download SQL 2005 Service Manager (2.0.0.2) for Vista
  
• Download Source Code

 

I got a question recently asking if it is possible using the Expressmaint utility to perform an integrity check of a database and then do a full backup only if there were no errors reported from the integrity check. Although there is no built in functionality for this, it's very easy to automate using a simple batch file as demonstrated below

:: Check Integrity and only backup if ok
@echo off
SET DBNAME=master
SET INSTANCE=(local)\SQLExpress
SET BACKUPFOLDER=C:\backups
SET REPORTFOLDER=C:\reports

expressmaint -S %INSTANCE% -D %DBNAME% -T CHECKDB -R %REPORTFOLDER% -RU DAYS -RV 7

IF %ERRORLEVEL% EQU 0 (
expressmaint -S %INSTANCE% -D %DBNAME% -T DB -B %BACKUPFOLDER% -BU DAYS -BV 2 -R %REPORTFOLDER% -RU DAYS -RV 7
) ELSE (
echo CheckDB finished unsuccessfully so no backup done
)

As you can see, we run an integrity check and only if the return code is 0 (i.e. no errors reported) is the full backup performed. Using simple batch files or scripting, it's easy to customise how the utility works to suit your environment. And if you're feeling adventurous, you can always download the source code from Codeplex and customise it yourself!

The November CTP of SQL Server 2008 is now available for download at the link below

• Download SQL 2008 November CTP

There's a lot of new functionality and features in this latest CTP including (but not limited to)

• Backup Compression
• Resource Governor
• TSQL Intellisense (well sort of )
• Data collection and Performance Warehouse improvements
• Transparent Database Encryption
• Filestream
• Geospatial support
• Change Tracking
• Declarative Management Framework enhancements
• Configuration Servers

 

I'm happy to announce that 2 of the most popular tools on sqldbatips.com are now available on Codeplex including full source code. I've set up projects for SQL 2005 Service Manager and for Expressmaint. Downloads will still be available on both sqldbatips.com and Codeplex but source code is only available from the Codeplex projects

• ExpressMaint Project
• SQL 2005 Service Manager Project 

On the 6th October 2007 there will be a FREE UK SQL Server conference held at Microsoft HQ in Reading. Details of the proposed sessions and speakers are now up on the SQLBits.com site and you can now register on the site as well. Places are limited to 300 so book early to avoid missing out! When registering, pick the top 10 sessions you are interested in to help decide the final conference agenda.

A new release of Reporting Services Scripter is now available for download

Fixes in Release 2.0.0.10

• Fixed an issue with the command line version opening the inputfile as read/write which causes an error when the file is under source control

New Functionality in Release 2.0.0.10

• Added a new advanced configuration option (UseTimeoutForRSExe) to allow overriding the default timeout for scripts run via RS.EXE. This option adds a new TIMEOUT script variable to the generated batch file
• Added a a new advanced configuration option (UseLazyLoading) to enable lazy loading of the Report Server catalog which improves performance for very large catalogs.
  

A new version of SQL2005 Service Manager is now available for Windows Vista users. This version is specifically designed for the UAC (User Account Control) requirements of Vista. Exisiting users running Windows XP or Windows 2003 do not need to install this new version. There is no new functionality in this release other than its ability to run as a standard user in Vista.

SQL2005 Service Manager is a replacement for the SQL Service Manager that came with SQL Server 2000. It allows the monitoring and management of all local SQL Server related services from a convenient system tray application.

Download SQL2005 Service Manager for Vista

A new release of Reporting Services Scripter is now available for download

New Functionality in Release 2.0.0.9

• This release adds a a new advanced configuration property to RSScripter.cfg called WebServiceTimeout. This allows the increasing of the Timeout property of the Reporting Services webservice proxy for installations with extremely large catalogs (100,000+ items) to prevent timeout errors when initially fetching the catalog. This property is set in seconds and defaults to 300 (5 minutes)

The July CTP of SQL Server 2008 is now available for download on the SQL Connect site. You can see a nice graphical representation of what's new on the SQL Server 2008 Improvement Pillars page. Highlights incude:

•  Reporting Services Enhancements to core processing and redering engine, Tablix support and removal of IIS dependency as well as new Report Designer and configuration tools
  
• New date and time data types
  
• Performance Data Collection
  
• Object Dependencies improvement provides reliable discovery of dependencies between objects through newly introduced catalog view and dynamic management functions.
  
• Database Mirroring Enhancements

One thing to note if you currently use Notification Services is section 5 of the readme for the July CTP

5.0 Deprecated Features

I've recently seen quite a few performance issues related to the size of the TokenAndPermUserStore cache in x64 SQL2005 so wanted to share a simple technique to manage the size of this cache. The issues around this store are described in KB 92736 however certainly in our case, the symptoms highlighted in the KB article would never had led me to it. Our primary symptom was a massive increase in SOS_SCHEDULER_YIELD waits, our CPU utilisation was the same as normal however less work was getting done. When I finally looked at the size of the TokenAndPermUserStore cache (having recently read the KB article but not really associating it woth the problem) I found it to be over 200MB. Flushing it using DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') immediately relieved the performance issues (you can see this very clearly in the wait graph below)

 

 

 

 

 

 

 

 

 

 

Whilst this issue is still being investigated, there are a number of workarounds in the KB article such as explicitly parameterizing queries, enabling forced parameterization using stored procedures etc which may or may not help. In the meantime, to avoid performance issues, I set up a SQL job that both tracks the size of the store and also flushes it when it reaches a threshold you define. This has shown no detrimental performance impact during the flush operations. One thing to note, as part of the PSS case we applied SQL Hotfix Build 3161 which supposedly contains a fix in this area and now under certain load conditions are not able to free the cache at all, this is something to be aware of and may require using one of the other workarounds. We never saw this issue on plain SP2. There's still a lot of work being done in this area and hopefully a real fix is imminent

First we need a couple of tables to track the size of the cache and record the flush activity

CREATE TABLE [dbo].[TokenAndPermUserStore](
    [loaddate] [datetime] NOT NULL,
    [sizemb] [decimal](10, 2) NULL,
    CONSTRAINT [PK_TokenAndPermUserStore] PRIMARY KEY CLUSTERED
    (
        [loaddate]
    )
)
go

CREATE TABLE [dbo].[TokenAndPermFlush](
    [Flushdate] [datetime] NOT NULL,
    [SizeMBBefore] [numeric](10, 2) NOT NULL,
    [SizeMBAfter] [numeric](10, 2) NOT NULL,
    CONSTRAINT [PK_TokenAndPermFlush] PRIMARY KEY CLUSTERED
    (
        [Flushdate]
    )
)
go

Then we need to set up a job to run the following TSQL which will track the size and also flush if it breaches a threshold

declare @SizeMBBefore numeric(10,2)
declare @SizeMBAfter numeric(10,2)

-- set threshold here
declare @ThresholdMB numeric(10,2) ; set @ThresholdMB = 15.0

select @SizeMBBefore = SUM(single_pages_kb + multi_pages_kb)/1024.0
from sys.dm_os_memory_clerks
where [name] = 'TokenAndPermUserStore'

insert dbo.TokenAndPermUserStore(loaddate,sizemb)
select getdate(),@SizeMBBefore

if @SizeMBBefore >= @ThresholdMB
begin

    DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')
    print 'Cleared TokenAndPermUserStore'

    select @SizeMBAfter = SUM(single_pages_kb + multi_pages_kb)/1024.0
    FROM sys.dm_os_memory_clerks
    WHERE name = 'TokenAndPermUserStore'

 

    insert dbo.TokenAndPermFlush select getdate(),@SizeMBBefore,@SizeMBAfter

end

delete dbo.TokenAndPermUserStore where loaddate < dateadd(dd,-14,getdate())
delete dbo.TokenAndPermFlush where Flushdate < dateadd(dd,-14,getdate())

A couple of SQL tools are available for download this week. First off is the July 2007 release of SQL 2005 BPA (Best Practice Analyzer). New and updated BPA rules will now be released independent of SQL Service Packs (read more here). Second of all is the DMVStats tool, an application that can collect, analyze and report on SQL Server 2005 DMV performance data, written by members of the SQL Server Customer Advisory Team

Download SQL 2005 BPA (July 2007)

Download DMVStats