Rebulding MSDB in SQL Server 2005

Again I spent a gorgeous day in front of the computer trying to solve a major problem.

Seems that because of some diskspace issures we had in April, my MSSQL configure got hosed and the server hasn’t been able to perform any backups nor has the sql server agent been running. This was due to the fact that the MSDB database has been corrupted.

The following are the steps that I took to get everything back up and running. I will warn you that by doing these steps, you will lose all you Maintenance Plans that you had and you will have to recreate them. Figure that this is a small price to pay because let’s face it, you got to have backups.

I would strongly suggest that you don’t be an idiot like me and make sure that you backup your msdb database. However if you are like me and didn’t do this, these steps should get everything back on track.

1. Put MSSQL into single user mode
   1. Click -> START -> Microsoft SQL Server 2005 -> Configuration Tools – > SQL Server Configuration Manager
      
   2. Right click on SQL Server and choose Properties
   3. Click on the Advanced tab. Under Startup Parameters you will be adding the following parameters to the beginning of the string: -m;-c;-T3608
2. Restart SQL Server
3. Connect to SQL server through the Management Console. From this point on we will be using TSQL to issue the commands so click the New Query button on the top left. At this point you should be in the master database inside the query window.
4. Detach the MSDB database using the following commands:
   use master
   go
   sp_detach_db ‘msdb’
   go

   and click Execute

5. We need to move (or rename, I prefer moving them) the existing MDF and LDF files for the MSDB database so that we can recreate it.
   1. Usually these files are located in the following directory:
      C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
      Your’s might differ.
   2. Move (or rename) the MSDBDATA.mdf and MSDBLOG.ldf files.
6. Back to the Management Studio. Open up the instmsdb.sql file in a new query window. This file is usually located in the following directory:
   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install
7. Execute the file. If you see any errors about xp_cmdshell, just ignore them. They are common and the script will take care of it.
8. At this point you should have your MSDB database restored. The only thing left is cleanup.
9. Execute the following command to make sure that xp_cmdshell is once again set to disable for security reasons:
   EXEC sp_configure ‘show advanced options’, 1
   GO
   RECONFIGURE WITH OVERRIDE
   GO
   EXEC sp_configure ‘xp_cmdshell’, 0
   GO
   RECONFIGURE WITH OVERRIDE
   GO
10. Shutdown SQL Server
11. Go back into your Startup Paremeters for your server in the SQL Server Configuration Manager and removed the -c;-m;-T3608 parameters we added earlier.
12. Restart SQL Server

Everything should be cool at this point and you’ll be able to recreate any Maintenance Plans and Jobs.

Let me know if you have a better way or a way to restore the configuration information from the old msdb database.

CF8 Error: Cron service not available error

This morning all of our sites were throwing this cryptic ColdFusion error, even after a reboot the error was still there. I did a search on Google and came across this post that describes the fix.

1. open your {cf_root}\lib directory
2. rename neo-cron.xml to neo-cron.xxx
3. if you have a neo-cron.bak, trying renaming it to neo-cron.xml. If you don’t you can download an empty neo-cron.xml from my box.net widget.
4. restart ColdFusion.

Ironman Movie Secrets (No Spoilers)

For those of you who didn’t get the see the advanced screening of Ironman last night at your local Muvico, I thought I should inform you about two things…

1) Get there early…The new trailer for The Dark Knight will be played. Yes, it kicks ass and will make you wish July was hear already. It is re-confirmed that Heath kicks Jack’s ass as the best Joker. I don’t want to tell you too much, so get there.

2) Stay late…like til after the last credit roles. I feel sorry for the people who left right after the movie, because they missed the biggest surprise of all. Don’t leave til the very, very, very end of the movie. You won’t be disappointed.

As for the movie itself, Robert Downey Jr. did a great job. The movie was funny, cool, action-packed and just kicked loads of ass. 2 hours seems to just pass by because we were having so much fun. You are going to love this movie and wish it wouldn’t end. Oh, the graphics kicked ass and the Ironman suit looks as real as real can get.

WARNING: There will be a bunch of kids at this movie, so I would go to a later showing if possible unless you want to be sitting with a bunch of little kids. There were kids as young as 4 in the movie with us.

Oh, don’t forget to visit my brother’s blog today for some laughs. Here is the link:

http://myjunkmail.blogspot.com