SQL Server 2008 SP1 - Cannot drop database because it is currently in use.

Recently I came across an issue in SQL Server 2008 SP1 where I was trying to rebuild our DEV databases from script (we have Nant scripts that perform a continuous build process so we know that they work) but during the rebuild the process failed because one of the databases could not be dropped. At first I thought that there must be existing connections to the database but a quick review of the output of "sp_who2" showed no such thing.

Anyway, to shortcut the whole build process I figured I would just use SQL Server Management Studio to manually drop the database and enable the "force close existing connections" option. However, this too failed saying that the database was currently in use, even with the "force close" option set and even more bizarrely once again when I checked the output of "sp_who2", I could not see any connections to the particular database in question. Restarting the SQL Server service didn't help either.

A quick Google search on this error message produced quite a few hits (as you probably know if you found this page that way), a lot of which seemed to indicate that the way to resolve this is to alter the database into single user mode with the immediately rollback option set. Using this produced no joy for me either, which wasn't really surprising considering that there appeared to be no connections in the first place.

In the end, this is how I solved it… You need to get SQL Server to tell you who is blocking the drop command. Not rocket science is it, once you know the answer. ;-) So…

• First of all, using SQL Server Management Studio, open a query window on to the server and ensure that it is connected to the [master] database.
• Next, also using SQL Server Management Studio, right click on the database in question and select the option to drop the database and enable the "close existing connections" option. Click OK.
• While this is trying to drop the database, quickly switch to the open query window and run "sp_who2". Look through the result set for the row with a status of "SUSPENDED" and note the value in the "BlkBy" column.
• Also in the query window, execute the kill command to kill the spid with the value noted in the point above.

And there you go, the database will have been dropped as soon as you killed the SPID.

Wierd eh?

Analysis Services 2005 - Dynamic Default Member

Whilst migrating a database from AS2000 to AS2005 I came across in interesting issue when trying to replicate the same behaviour as the "legacy" version. Firstly, a little background...

For the sake of expediency I used the migration wizard to convert most of the AS2000 database over to AS2005. I say most because although the validation checker stated that everything was a-okay, when it came to actually do the migration the wizard whinged about a dimension that was joined to different cubes at different levels within the dimension. In order to progress this, I deleted the dimension and migrated the rest.

So, what I was left with was a pretty complete system that, although not overly complex, it wasn't trivial either. The database contained a number of cubes and virtual cubes etc. and they contained a financial line dimension making use of unary operators and custom MDX for specific members. When designing the AS2000 database originally, I specifically avoided calculated cells so there were none of those but it did contain quite a number of calculated members. I must say that I was actually quite impressed with the conversion process once it worked. Certainly the dimensions were migrated quite well, though it made a bit of a mess of migrating the "alternate hierarchy" dimensions. I'm also not a big fan of using linked measure groups etc. so I'll probably back those out into a series of cubes using multiple measure groups etc. but I digress.

Whilst going through all the dimensions, to compare the migrated version to the original version, I noticed that the [Currency] dimension had not been migrated to be the same as the original. The dynamic default member definition was missing and needed to be added back. Now, based on AS2000 functionality, the [Currency] dimension had the default member defined using the "Filter" function to filter the members of the dimension based on a "Default Flag" member property such that when the member property was "1" it identified the default member.

In AS2005 the preferred method is to use the "Exists" function and it performs a lot better than filtering. Indeed, there is also a known issue in AS2005/8 where use of the "Properties" function when specifying a default member causes heap corruption during processing so it should be avoided. Now the interesting bit, The currency dimension does not have an "all" level so I set the "IsAggregatable" property to false, changed the "Default Flag" attribute's "AttributeHierarchyEnabled" property to true, to change it from a member property to a true attribute and set about deploying and processing the revised dimension. All of that resulted in the following error:
Errors in the metadata manager. The 'Currency' attribute with IsAggregatable=false must not have any relationship to 'Default Flag' attribute that has AttributeHierarchyEnabled=true

So, rather interestingly, you cannot have attribute 'x' related to attribute 'y' if attribute 'y' has its "IsAggregatable" property set to false. Converting attribute 'x' to a member property serves to allow the dimension to deploy but prevents the use of the attribute in the "Exists" function. One would have to revert back to the use of the "Filter" function which we really need to avoid. Now I'm not sure why this limitation exists, it just does. So how to work around it?

Well what I did, was to create a copy of the key attribute and have it related to the key. I changed the attribute relationship to "Rigid", as this should never change, and the cardinality to "one to one" even though this latter property isn't used in AS2005. Then, on the key attribute, I set the "IsAggregatable" property to true, the "AttributeHierarchyVisible" property to false and left the "Default Flag" attribute relationship on the key. The key also had to be renamed so I added an underscore to the end of it.

On the copied attribute (i.e. the copy of the key) I renamed it to the name of the key attribute but without the underscore, set the "IsAggregatable" property to false and defined the default member with MDX using the "Exists" function and the "Default Flag" attribute as follows:
Exists([Currency].[Currency].[Currency].Members, [Currency].[Default Flag].&[1]).Item(0)

This appears to do the trick at the expense of duplicating the attribute, providing a dynamically set default member on an attribute that is not aggregatable.

I hope this helps others out there who may face the same issue and hey, if you know why you can't do this straight on the key, please enlighten me!