I’ve recently moved the database behind DavidOverton.com (also uksbsguy.com) from a Windows Server 2003 system with SQL 2005 to Windows Server 2008 with SQL 2008.  This would be the same process if you were potentially performing a migration from SBS 2003 with SQL 2005 to SBS 2008 Premium with SQL 2008 (or SQL 2005, but the performance issue only happens with SQL 2008). The process was amazingly simple:

1. Stop the database on the Windows 2003 system
2. Copy the datafiles, errorlogs, logs etc to the new system
3. Install SQL 2008 on the new system, creating an instance by the same name
4. Modify the registry to point to my new files (details here)
5. Start SQL 2008

Obviously I had to do some IIS stuff (create a new site) and install and redirect my logging software (WhosOn), but overall it went very well.

Or so I thought until I noticed that my normally nominal CPU utilisation had gone bananas.  One of the SQL 2008 new and improved features is the Server Activity History which makes it very easy to pinpoint problems, along with the Activity Monitor.  To get the monitor you need to configure the SQL Data Collector (run it twice, once to configure the data warehouse and once to configure the collection process itself).  Then right click on the data collection and chose one of the reports.

Notice the Activity History above shows 50% cpu utilisation when less than 50 people are hitting the website.  This should be a snooze for the system.  I ended up disconnection the web site, my monitoring software and everything else and running a trace with every box ticked.  This gave me a trace output like this:

I got around 200,000 lines like this when every process on the system that could access the system was stopped. Wow!  A quick search of the internet talked about corrupt msdb files and then it hit me.  I had lifted all the database files from my SQL 2005 system, including the MSDB files to the SQL 2008 system.  A quick checked showed that the SQL 2008 files were still intact and that SQL Server itself was referencing my SQL 2005 files. 

By entering the two commands below I changed back to the default SQL 2008 datafiles and after I restarted SQL, my cpu was back where it should be.  Note that the directory may be different for you and may well contain and instance name, which is used to replace <instance> in the commands.

alter database msdb modify file (NAME= MSDBData, FILENAME=’C:\Program Files\Microsoft SQL Server\MSSQL10.<instance>\MSSQL\datamsdb.mdf’);

alter database msdb modify file (NAME= MSDBLog, FILENAME=’C:\Program Files\Microsoft SQL Server\MSSQL10.<instance>\MSSQL\data_log.mdf’);

So, what impact did it have?

I mentioned earlier the Activity monitor which gives you a realtime status of the system, but did not show the msdb issues’ root cause.  A great tool that looks like resource monitor:

ttfn

David