Ha! You tricked me... I thought this was going to be about an issue with managing a GiS Database especially when it came to the ArcSde 9.3 to 10.0 upgrade. In addition to be a DBA, I'm also a GIS Analyst.
I've experienced that same up issue you experienced except the problem was that our server team thought they were just rolling out OS patches to the server but, actually rolled out SQL Server hot fixes and Cumalative Updates. We started experiencing the same thing when Master was corrupted and we tried restoring from backup. It took us a bit to notice that the versions were different since we were not expecting it. Once we figured that out, it was smooth sailing.
How has your upgrade been to ArcSDE 10.0? We are still using ArcSDE 9.3 but, I know there are some fundamental changes that occur there. The largest being that the geometry storage has changed from ESRI's format (SdeBinary) to native SQL Server Geometry format. The biggest gotcha I see for the DBA perspective is that spatial indexes are no longer created on the GIS side, so they need to be created on the database side.
Thanks for mentioning GIS, in my organization GIS is one of my heavily used databases and people tend to forget that it's stored on the same server as the rest of the databases and until recently people also treated it as something completely different than what it is... Just a database