SQL Server Q&A

Symptoms
When you create a new maintenance plan that runs on a schedule in Microsoft SQL Server 2005, you may receive the following error message:

Create maintenance plan failed.
Additional information:
Create failed for JobStep ‘Subplan’. (Microsoft.SqlServer.MaintenancePlanTasks)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The specified ‘@subsystem‘ is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error 14234)This behavior may occur when you create the maintenance plan manually or by using the Maintenance Plan Wizard.
Resolution
This behavior occurs when you install SQL Server 2005 without installing the SQL Server 2005 Integration Services (SSIS) service. When you create the maintenance plan, the following code is run.

EXEC msdb.dbo.sp_update_jobstep @job_name=N’System DB Maintenance Plan’, @step_id=1 , @subsystem=N’SSIS’, @command=N’/SQL “\Maintenance Plans\System DB Maintenance Plan” /SERVER ASHBSQL701 /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF’This code tries to access the Integration Services service.

Symptoms
This article discusses two alternative approaches to a disaster recovery plan for transactional replication. This article contains more information about the following:Problems in the approach for the disaster recovery plan that is mentioned in SQL Server Books Online.Two alternative approaches to a disaster recovery plan.Step-by-step procedure to set up a disaster recovery plan for the two alternative approaches.Step-by-step procedure to recover the replication databases for the two alternative approaches.
Resolution
In SQL Server Books Online, the “Strategies for backing up and restoring transactional replication” topic discusses the disaster recovery plan for transactional replication. However, the approach that is mentioned in SQL Server Books Online may not satisfy the requirement of minimum latency. This article discusses two alternative approaches that can be used to set up a disaster recovery plan for databases that are involved in transactional replication. The approaches that are discussed in this article can be used to recover the replication databases from a failure situation, such as a hardware failure.

Symptoms
Consider the following scenario. You create a maintenance plan in an instance of Microsoft SQL Server 2008. You perform a full backup for the MSDB database.You restore the MSDB database to another instance of SQL Server 2008. You delete the maintenance plan in the second instance by using SQL Server Management Studio.In this scenario, the original maintenance plan in the first instance is deleted unexpectedly. When you refresh the maintenance plans folder in SQL Server Management Studio in the second instance, the maintenance plan is not deleted from the second instance.
Resolution
A maintenance plan is aSQL Server Integration Services (SSIS) package that is stored as XML in the MSDB database. A maintenance plan contains a list of connection strings that use the actual name of the server. Therefore, if you restore the MSDB database of a server to another server, the maintenance plan connection strings still point to the original server.

Symptoms
When you frequently back up or restore a database in Microsoft SQL Server 2008, the memory usage increases gradually. Additionally, the size of the procedure cache grows at a steady rate.
You can see this problem by running the following statements.

SELECT p.plan_handle,CONVERT (varchar, GETDATE(), 126) AS runtime, LEFT (p.cacheobjtype + ‘ (‘ + p.objtype + ‘)’, 35) AS cacheobjtype,p.usecounts, p.size_in_bytes / 1024 AS size_in_kb, stat.total_worker_time/1000 AS tot_cpu_ms, stat.total_elapsed_time/1000 AS tot_duration_ms, stat.total_physical_reads, stat.total_logical_writes, stat.total_logical_reads, LEFT (CASE WHEN pa.value=32767 THEN ‘ResourceDb’ ELSE ISNULL (DB_NAME (CONVERT (sysname, pa.value)), CONVERT (sysname,pa.value))END, 40) AS dbname,sql.objectid, CONVERT (nvarchar(50), CASE WHEN sql.objectid IS NULL THEN NULL ELSE REPLACE (REPLACE (sql.[text],CHAR(13), ‘ ‘), CHAR(10), ”)END) AS procname, REPLACE (REPLACE (SUBSTRING (sql.[text], stat.statement_start_offset/2 + 1, CASE WHEN stat.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), sql.[text])) ELSE stat.statement_end_offset/2 – stat.statement_start_offset/2 + 1END), CHAR(13), ‘ ‘), CHAR(10), ‘ ‘) AS stmt_textFROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_plan_attributes (p.plan_handle) pa INNER JOIN sys.dm_exec_query_stats stat ON p.plan_handle = stat.plan_handle OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) AS sqlWHERE pa.attribute = ‘dbid’ ORDER BY p.plan_handle DESCIn the result, you may find that there are many records in which the values of the stmt_text column resemble the following:
select @media_set_id = media_set_id from msdb.dbo.backupmediaset where media_uuid = N’{7C5DCC4D-3065-47F3-AA5F-2C498DFF27F8}
insert msdb.dbo.backupmediaset (media_uuid, media_family_count, software_name, software_vendor_id, MTF_major_version, mirror_count, is_password_protected, is_compressed) values (N’{7C5DCC4D-3065-47F3-AA5F-2C498DFF27F8}’, 1, @param0, 4608, 1, 1, 0, 1)
select @media_set_id = @@identity end select @media_count = media_count from msdb.dbo.backupmediafamily where media_set_id = @media_set_id and family_sequence_number = 1These operations are the read operations and the write operations to the msdb backup history tables.
Resolution
The procedure cache grows because the backup statements or the restore statements are not fully parameterized. Each statement results in a prepared statement in the cache that is never reused.