A few of my favorite blogs

Today I decided to give credit and a special thank you to a lot of people in the SQL community whom have either helped me directly or indirectly with their knowledge and skills and most importantly their willingness to share back with the community.

So who do I look to for expert reliable sources for all things SQL Server. Let me start with my favorite trainers who blog. Without advanced training I would not have the skill set I have today. You can learn a lot on the job and my first DBA job lasted 6 years, covered 5 different database technologies across a few hundred servers. You can learn how to firefight well in that environment, but you rarely get a chance to hone your skills in one area. That is where training comes in. It is easy to get caught up in the day to day tasks of your particular job, and loose sight of what is new and changing. SQL Server has a lot of features that may not be applicable to your current job, but important to other jobs you may want to pursue in the future.

My first great training was Kalen Delaney, in Portland OR a few years ago, shortly after SQL 2005 had come out. The class blew my mind. Literally, I went in to the class feeling pretty competent in my skills and came out feeling very humble, and with a huge list of things I wanted to fix, change, and improve in my current job. In addition it made me want to know more and keep up with the changing technology. For that I thank Kalen and refer to her blog often as a great resource!

Kalen Delaney
http://sqlblog.com/blogs/kalen_delaney/default.aspx

As a side note I had the privilege of taking her internals class a second time when SQL 2008 first came out, it wasn't in person, but still was excellent, and I highly recommend her trainings.


Last year was the first time I had the pleasure and opportunity to take a course from Kimberly Tripp and Paul Randal. Wow, what a fantastic training. By the time I took their class I had been a production DBA for 6 years with lots of training, and was supporting over 2000 databases on more than 170 servers. I felt I had a pretty good understanding of all things SQL. My first surprise was that their was no computers or labs, it was more reminiscent of a graduate school class, with lecture, and discussion and some demonstration. It was fantastic, every day you felt like your brain had just ran a marathon. I learned a lot and tuned what I already knew even more. The networking was fantastic as well. I am very excited I get to go back for the second Immersion Event this August.

Kimberly Tripp
http://www.sqlskills.com/blogs/kimberly/

Paul Randal
http://www.sqlskills.com/blogs/paul/


Ok, I haven't taken a class from Brent, but I hear that the SQL Cruise is pretty awesome. I have met him once and he's a really nice guy. His social influence in the SQL Server realm is incredible and I have used his blog on many occasions.

Brent Ozar
http://www.brentozar.com/


I've been to a couple of Buck's presentations and he is a fantastic presenter, his blog is great too. Lots of good DBA administration stuff there, great ideas. In fact, one of Buck's talks got me started on tracking performance baselines for all of my systems.

Buck Woody
http://blogs.msdn.com/b/buckwoody/


In the category of I've never met, but am very grateful that they share their knowledge with the community, here are a few of my favorites. There are others too, but I use Glen and Ola's scripts all the time. Fantastic stuff.

Ola Hallengren
http://ola.hallengren.com/

Glen Berry
http://sqlserverperformance.wordpress.com/

Erland Sommarskog
http://www.sommarskog.se/index.html

Michelle Ufford
http://sqlfool.com/

SSIS subsystem failed to load. The job has been suspended.

I came across a new one a month ago that I hadn’t seen before. I got a complaint from a developer that a job was hanging on one of our development servers. A consultant had set the server up before my time here, so I really didn’t know much about the install or configuration on this particular server.The error in the job history looked like this:

Step 1 of job '' (0x1077ED50A744154FFEEBC695E3C87432) cannot be run because the SSIS subsystem failed to load. The job has been suspended.

Additionally, the job had been suspended and we were unable to run it again. If we drop and recreate it, it would run, fail and suspend all over again.
Well I hadn't seen that one before so I did some high tech DBA debugging (i.e. google search).

The good news was that I got search results. The first link that I chased down was a kb article and it was regarding a similar error when you restore msdb on 2005. Well this was a 2008 R2 box that I had recently upgraded from 2008, but after some more digging I figured out that indeed the consultant who had built the instance originally had tried to duplicate another server by simply restoring msdb from that server. The key is the msdb.dbo.syssubsystems table and the msdb.dbo.sp_verify_subsystems undocumented stored procedure.

Looking at the syssubsystems table I verified that the paths listed for subsystem_dll did not exist on the server. However they did match the install path layout of another server.

To check the current file paths that SQL believes exist

select * from msdb.dbo.syssubsystems

subsystem	subsystem_dll	agent_exe
TSQL	[Internal]	[Internal]
ActiveScripting	C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLATXSS.DLL	NULL
CmdExec	C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLCMDSS.DLL	NULL
Snapshot	C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLREPSS.DLL	C:\Program Files\Microsoft SQL Server\100\COM\SNAPSHOT.EXE
LogReader	C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLREPSS.DLL	C:\Program Files\Microsoft SQL Server\100\COM\logread.exe
Distribution	C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLREPSS.DLL	C:\Program Files\Microsoft SQL Server\100\COM\DISTRIB.EXE
Merge	C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLREPSS.DLL	C:\Program Files\Microsoft SQL Server\100\COM\REPLMERG.EXE
QueueReader	C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLREPSS.dll	C:\Program Files\Microsoft SQL Server\100\COM\qrdrsvc.exe
ANALYSISQUERY	C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLOLAPSS.DLL	NULL
ANALYSISCOMMAND	C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLOLAPSS.DLL	NULL
SSIS	C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLDTSSS.DLL	C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
PowerShell	C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLPOWERSHELLSS.DLL	C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\SQLPS.exe

The Fix:

use master GO --ALLOW updates EXEC sp_configure 'allow updates', 1; reconfigure with override; GO --select * from dbo.syssubsystems --(verify old values) --DELETE syssubsystems use msdb GO delete from msdb.dbo.syssubsystems GO --REPOPULATE syssubsystems exec msdb.dbo.sp_verify_subsystems 1 GO --select * from dbo.syssubsystems --(verify new values) --DISABLE updates use master GO EXEC sp_configure 'allow updates', 0; reconfigure with override; EXEC sp_configure;

Links:

• http://support.microsoft.com/kb/914171

• http://www.sql-server-performance.com/faq/sql_agent_job_suspended_p1.aspx