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.

SSIS, 64-bit and a breakpoint

Despite of 2 service packs and the numerous hotfixes we installed since the release of Sql Server 2005, we still discover problems with SSIS much too often. This time, it was some weird message in our acceptance environment telling me the "Script files failed to load". I had thoroughly tested the package on our development environment, so I was amazed to hear it failed on another environment.

A quick search on the specialized forums returned some hits - mostly related to the Script task the package contained - but nothing really described the conditions I faced. The only difference I could think of was the fact that the acceptance environment was a 64-bit edition, unlike development. In such a case, the settings of the Script task are quite simple: the PrecompileScriptIntoBinaryCode flag should be on.

There was a non-active breakpoint however that caught my attention which I hadn't noticed before because the Script task was not part of my changes to the package and the tests had never halted at that break. After removing the breakpoint, the package effectively ran without a problem.

It's a shame we had to discover this bug the hard way, but it proves that an acceptance environment should stick as close to production as possible. In this case, it payed off.

SSIS's interaction with DTC

I encountered some locking problems while developing an SSIS package today. The purpose of the package is to upload new content from flat file to a table. It contains basically two control flow tasks - one clearing the table (TRUNCATE TABLE) and another one filling it with a data flow task - that require a transaction. But, while executing, the package blocked at the start of the data flow task.

Investigating the blocking, I encountered some SPID -2 owned locks in sys.dm_tran_locks, thus blocking the data flow task from retrieving the necessary metadata from the database at the PreExecute event. I fiddled around with the setting of the TransactionOption and IsolationLevel of the different components, but nothing resolved my problem adequately. With the help of profiler however, I could determine that SSIS did not propagate the DTC transaction - started for the SQL task - to the Data Flow task.

The blocking can be resolved by either changing the ValidateExternalMetadata attribute of the OLE DB Destination or changing the TRUNCATE TABLE of the SQL task into a DELETE statement.

The reason why both of these solutions have such an impact remains unclear to me. It's true that each of these modifications affect the locks that are required by the package, but that doesn't explain the fact that the Data Flow task was not enlisted appropriately into MSDTC.

sys.indexes and table valued functions

Today, while lending a hand in the creation of a new operational reindexing job, we discovered the existence of primary keys on table valued functions. I had written a little script to gather all the clustered and non-clustered indexes with their necessary attributes from sys.indexes. I assumed that indexes are only present on physical tables, so I didn't made any checks on the type of object it was associated with. In our first test-run, that turned out to be a mistake.

In the definition of the return value of a table valued function, you can define the primary key for that table and although it isn't an actual table - just a table variable created at run-time - metadata about the index is kept similar to any user table.

USE tempdb
GO
CREATE FUNCTION dbo.udf_TableValuedFunction ()
RETURNS @Table TABLE ( id int PRIMARY KEY , comment varchar(200) )
AS
BEGIN
INSERT @Table VALUES ( 1, 'comment 1' )
INSERT @Table VALUES ( 2, 'comment 2' )
RETURN
END
GO
SELECT *
FROM sys.objects o
JOIN sys.indexes i ON i.object_id = o.object_id
WHERE o.name = 'udf_TableValuedFunction'
GO
DROP FUNCTION dbo.udf_TableValuedFunction
GO