David Overton's Blog and Discussion Site
This site is my way to share my views and general business and IT information with you about Microsoft, IT solutions for ISVs, technologists and businesses, large and small.  
SQL 2005 to SQL 2008 forklift upgrade resulted in 50% cpu utilisation (& resolution) – aka SBS 2003 to SBS 2008 SQL Application Move

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.

Configure Data Collections for SQL 2008   configure management data warehouse wizard   SQL 2008 Server Activity History - 1st pass

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:

sql 2008 trace with everything else turned off

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?

SQL 2008 Server Activity History - fixed

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:

SQL 2008 Server Activity Monitor

 

ttfn

David


Posted Mon, Dec 8 2008 9:26 AM by David Overton

Comments

Kael wrote re: SQL 2005 to SQL 2008 forklift upgrade resulted in 50% cpu utilisation (& resolution) – aka SBS 2003 to SBS 2008 SQL Application Move
on Thu, Jun 10 2010 4:00 PM

Hi David, How long did the migration take and how long for the trouble shooting?

Thanks

David Overton wrote re: SQL 2005 to SQL 2008 forklift upgrade resulted in 50% cpu utilisation (& resolution) – aka SBS 2003 to SBS 2008 SQL Application Move
on Fri, Jun 11 2010 10:25 AM

Kael,

the migration took a few minutes and the trouble shooting did not take long using the tools. I think I allowed about 10 minutes for the data capture to run.

Thanks

David

Add a Comment

(optional)  
(optional)
(required)  
Remember Me?

(c)David Overton 2006-18