SQL Server Q&A

Symptoms
If index maintenance operations such as DROP, CREATE, or REBUILD statements occur at the time of a database or transaction log backup, subsequent attempts to load the backup may fail with the following error messages:

spid8Could not continue scan with NOLOCK due to datamovement.
spid8Error: 1511, Severity: 21, State: 5
spid8Sort cannot be reconciled with transaction log.The client receives the 1511 error message and the restore processing terminates, which leaves the database in an unrecovered state.
Resolution
Starting with Microsoft SQL Server Service Pack 1 (SP1) build 7.00.699, recovery speed was increased by enabling read ahead abilities.
The error condition is triggered when recovery is too aggressive with read ahead operations while rolling forward index maintenance activities.

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
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.