"Troubleshooting Performance Problems in SQL Server 2008"

After the excellent white papers that were written for the previous versions and with all the improvements that have been made in Sql Server 2008 in this area, I'm looking forward to read the new edition in the Troubleshooting Performance Problems series. You can find a link to download it here.

SSRS configuring a rendering extension

Reporting Services has a default rendering extension to create csv files with a comma as the default fielddelimiter. But I needed to have the possibility to create a csv with semicolon delimited fields. It's easy to change this behaviour - and many others - when calling the Render method or by adding some settings to the URL. But I wanted that file delivered through a subscription and that makes it a lot more difficult.

Just add the following snippet to the Render section of the RSReportServer.config file and you'll have a new option to render your subscriptions.

<Extension Name="CSVSemicolon" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
  <OverrideNames>
    <Name Language="en-US">CSV (semicolon delimited)</Name>
  </OverrideNames>
  <Configuration>
    <DeviceInfo>
      <FieldDelimiter>;</FieldDelimiter>
    </DeviceInfo>
  </Configuration>
</Extension>

Arrivederci W.

Dis4ea is dead, long live WesleyB!

Last week my favorite colleague - you know the one :) - left his company to launch his career into a whole new, interesting direction.
His old blog tells the story of his four years at our company as a database architect. It won't get updated anymore but many posts are still as relevant now as at the time they were posted - it's his long term memory after all. And there is the excellent blogroll of course.
The content of his new blog will surely treat topics differently (the host's always watching), but I hope he will continue to produce quality.

Enough said... success!

Dive into msdb: BackupSet & RestoreHistory

Simple question today: can we find out how recent the backup is that has been used to restore our production copy?

select top 1 b.backup_finish_date

from msdb..backupset b

join msdb..restorehistory r on b.backup_set_id = r.backup_set_id

where r.destination_database_name = 'MyDb'

order by r.restore_date desc

Transaction log files after reverting from snapshot

Watch out with the transaction log file(s) when you revert a database from a snapshot! A snapshot database has no transaction log itself and when you revert the original database to the snapshot database, it is documented that Sql Server rebuilds the log. It does however not reuse the characteristics of the original transaction log file(s) as one could expect. The actual and maximum size, the autogrowth property and even the number of files are not necessary the same as before reverting. This could get you easily into trouble.

Lets illustrate this with an example, by creating a database SnapshotTest with 2 transaction log files, which will probably not be the same number as is the case for the model database. A snapshot of SnapshotTest with the name SnapshotTest_ss gets created.
Now for the interesting stuff: we revert the database from the snapshot and we investigate the transaction log files as we did earlier. Only the first transaction log file will be present for the SnapshotTest database but not with the same characteristics as before and not even the ones of model's transaction log. The second file is no longer part of the database. Is this desired behavior?

USE [master]
GO
CREATE DATABASE SnapshotTest
ON (NAME = SnapshotTest, FILENAME = 'C:\TEMP\SnapshotTest.MDF')
LOG ON (NAME = SnapshotTestLog1, FILENAME = 'C:\TEMP\SnapshotTestLog1.LDF')
, (NAME = SnapshotTestLog2, FILENAME = 'C:\TEMP\SnapshotTestLog2.LDF')
GO
EXEC sp_helpdb SnapshotTest
GO
CREATE DATABASE SnapshotTest_ss ON (NAME = SnapshotTest, FILENAME = 'C:\TEMP\SnapshotTest_ss.MDF')
AS SNAPSHOT OF SnapshotTest
GO

RESTORE DATABASE SnapshotTest
FROM DATABASE_SNAPSHOT = 'SnapshotTest_ss'
GO
EXEC sp_helpdb SnapshotTest
GO
USE [master]
GO
DROP DATABASE SnapshotTest_ss
GO
DROP DATABASE SnapshotTest

Don't forget to clean up the database file that is left behind on the file system - it's no longer part of a database so it's not removed when you drop the database.

Focusing on SSIS

Since our migration to Sql Server 2005 a couple of months ago, Integration Services has always been a constant point of attention to our database development team. That's mainly because of the necessary and upcoming migration of the existing DTS packages, but also because of the constant trouble we experience with the technology.

Yep, we like it just as much as we hate it at times. Some random stuff from the past weeks...

* SSIS is in a lot of ways much stricter than DTS, certainly when it comes to data transfer tasks. Encoding of files didn't bother us in the past, but now we have to be very aware of how we handle and manipulate our imports. A data conversion task gave me some hastle when it discovered a special character in an import which caused an error concerning the truncation of data for that field. Although the content wasn't exceeding the length of the field, it appeared that the task couldn't correctly determine the length, so I had to ignore the so-called truncation in the error handling options.

* On our main production cluster we had to deploy a package. We store our packages in Sql Server (msdb) at this time, but we just did not succeed in the import on the server (with all the SP's and CU's installed). It produced the following error:

Element not found. (Exception from HRESULT: 0x8002802B
(TYPE_E_ELEMENTNOTFOUND)) (Microsoft.SqlServer.DTSRuntimeWrap)

In the end, we had to import the package using the client (SSMS) from another server.

Database snapshot files

Be aware of the fact that a database snapshot contains all the data of the referenced database at a certain moment. Not only the actual data, but also the metadata, the bookkeeping of Sql Server, is present in the snapshot.

This can give some unexpected results when you query tables like sys.database_files for example. Instead of getting the set of files the snapshot exists of, you get the info that was originally in the referenced database, being the files of that database, not the snapshot. That information and much more is available through master.sys.master_files.