SqlServer Begin's

Last evening I ran into the precarious situation when trying restore a database. I have cut down a lot of steps to just get to the problem.   By 9.00 pm I had done few database restores at a client and one of the large databases just wouldn’t get restored with the standard error. Msg 3702, Level 16, State 3, Line 2 Cannot drop database “DataBaseName” because it is currently in use. I went through the sysprocesses and killed all the active connections but the same message kept popping out all the time. After many attempts and 10 minutes gone I was still at square one. Tried standard opening and closing management studio ,Stopping sql agent   none would work. Finally I took the database into single user mode with Alter database set single_user with rollback immediate ( this took about 5 minutes to execute) and created hole load of informational messages in between. Then dropped the database which took another 5 minutes to take effect.

There was a query that was posted few weeks back where the performance of the application gradually deteriorated towards the end of the week but at the beginning of the week it was working optimally. What does this mean from a technical perspective? Towards the end of the week the indexes gradually get fragmented and during the weekend a optimization job reorganizes or rebuilds the index. Even though, from database maintenance perspective we saved our selves there seems to be room for improvement. If we read between lines it does say the index get fragmented fairly soon and that’s clear indication 1.)   That there is notable amount of data written to the indexes and -         2.) The index design is not optimal (or) 3    3.) The fill factor settings may need to be changed after monitoring As we were not in a state to recommend any indexing strategy to the client( due to vendor ownership) we needed to identi...

Last week I had the opportunity in attempting to salvage a MDW configuration. As at the time of compiling this   it was still not working ; I’m hoping it a stone through away from getting the collections running. The client had setup the MDW and found it was collecting far too much data as part of the default collections and wanted it stopped. And as you know there is no Microsoft documented way of uninstalling the MDW.   However, Aaron has a good article with disclaimer ridden code offered at http://www.mssqltips.com/sqlservertip/2473/removing-the-sql-server-management-data-warehouse/ . With my limited experience with the code and testing, I’d say it is very close to safe as long as you don’t drop any of the schedules. FYI -   If MDW is ever re-enabled the CollectorSchedule_Every_15min , CollectorSchedule_Every_6h schedules needs to exists for three of the data collection to be reinitiated. Unfortunately the client seem to have gone the extra step an...