Subsystem 'CmdExec' could not be loaded (reason: The specified module could not be found)

After a recent server crash, we had the opprotunity to rebuild the SQL Server instance from the previous nights backups. After going through the various steps to restore the Master and MSDB databases, as well as change the location of the tempdb, resource and model databases, we thought everything was set.

Over the weekend, the server in question was set to backup user databases to a file share using SQLCMD. Upon starting the job, the SQL Agent returned the following error message: "Unable to start execution of step 1 (reason: The CMDEXEC subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended).  The step failed."

After looking throught the SQLAGENT.OUT file (which yielded the same error message), we took a look at the MSDB..SYSSUBSYSTEMS table. Looking in the subsystem_dll column, the issue presented itself.

 SELECT subsystem
               ,subsystem_dll
   FROM msdb..syssubsystems

subsystem  subsystem_dll   
CmdExec    D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLCMDSS90.DLL   

During the server rebuild, the SQL Server program files where installed under the C:\Program Files directory whereas on the crashed server installation, all the old binaries were installed under D:\Program Files. Since we restored the MSDB database from an old backup, those values overwrote the values from the new server install.

In order for the binaries to load, MSDB..SYSSUBSYSTEMS table had to be updated.

UPDATE msdb..syssubsystems
   SET subsystem_dll = REPLACE(subsystem_dll,'D;\','C:\')   
 WHERE subsystem = 'CMDEXEC'

Once the subsystem_dll column has been updated to point to the C:\Program File directory, we restarted the SQL Agent service and checked the SQLAGENT.OUT file again. The error message was gone and the job completed successfully after we restarted it.

Extracting a Query Execution Plan from the Procedure Cache

From SQL Server 2005 forward, it has been possible to view the execution plan of a query that has been executed by someone other than yourself. This is extremely useful on procedures that you, as a DBA, would not want to execute in production because of data modification logic, but are used everyday as a part of the application. Finding out how the procedure is running and why it is running so poorly without having to execute it also saves a lot of setup time since many procedures require an extensive set of input parameters that we may or may not know.

First, a little setup. We need to create a test stored procedure that we will be looking for in the procedure cache.

CREATE PROCEDURE KevinsTestProc

AS
BEGIN
  SELECT TOP 1 * FROM sys.objects
END
GO

After we’ve created the stored procedure, execute it a couple times so that SQL Server will cache it. Once it is there, we can start querying various execution related DMV’s that will eventually show us the execution plan.

EXEC KevinsTestProc

GO

Since there can be an enormous amount of plans in the procedure cache, the first thing we need to help narrow down the search is some text that is unique to that procedure, like the procedure name.  By querying the sys.dm_exec_cached_plans DMV and using the sys.dm_exec_sql_text DMF we can get the plan handle will be used to extract the exection plan.

SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, query_plan

FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE usecounts > 1
AND text LIKE '%KevinsTestProc%'
AND objtype = 'Proc'
ORDER BY usecounts DESC;

Results:

We can see from here that we have the plan handle,  how many times this store procedure has been executed, how large the plan is, the text inside the cached plan, and an XML representation of the Execution Plan generated at compile time.

Now that we have this we can click on the XML hyperlink in the results window to open up the execution plan in SSMS.

Finding Stored Procedures Related to Replication

If you have transactional or merge replication configured in your environment, you may want to know what objects were create by replication in your subscribing database. For Stored Procedures, there’s nothing in the sys.objects or sys.procedures catalog views that denotes that a procedure is used with replication. Fortunately, there is a system table created in the subscribing database called MSreplication_objects. The query below will give you the Stored Procedures created by replication and the articles associated with them.

SELECT [publisher]
      ,[publisher_db]
      ,[publication]
      ,[object_name]
      ,[object_type]
      ,[article]
  FROM [MSreplication_objects]
 WHERE [object_type] = 'P'

Now that we know the procs that are related to replication, we can exclude them from sys.objects or sys.procedures when looking for user procs in the database.

SELECT *
 FROM   sys.procedures
 WHERE  name NOT IN (SELECT [object_name]
                     FROM   MSreplication_objects
                     WHERE  [object_type] = 'P')

Backing Up an Analysis Services Database

There are a lot of DBA’s out there who have to administer SQL Analysis Services, but haven’t the slightest clue on how to do anything more than set it up and grant security. After the database has been setup and cubes are being created, it will need to be backed up just like the SQL databases you are already familiar with. So how do you backup an Analysis Services database when you can’t use the T-SQL commands you know and love? The answer is Analysis Services commands.

Start by opening up a new Analysis Services XMLA Query and log into the Analysis Services instance. You can do this by selecting File -> New -> Analysis Services XMLA Query

It will open up what looks to be a T-SQL window but it will only accept XML commands against the Analysis Services database. The following query will back up a single Analysis Services database without encryption or compression.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Object>
    <DatabaseID>Insert SSAS Database name here</DatabaseID>
  </Object>
  <File>file_location\file_name.abf</File>
  <AllowOverwrite>true</AllowOverwrite>
</Backup>

Insert your own values into the <DatabaseID >, <File>,<AllowOverwrite> fields and run the command. Once you have it working, you can schedule it in a SQL Agent Job just like any other backup job that you may have out there. In the job step, select a type of “SQL Server Analysis Services Command”

A more in-depth reference be found here: http://msdn.microsoft.com/en-us/library/cc917611.aspx#XSLTsection124121120120