Tag Archives: maintenance plan

SaveToSQLServer method error | Communication Link Failure

Last week one of our team members was implementing a Change Control to create a Maintenance Plan on a SQL Server 2005 instance. Since this instance was getting backed up using a Third Party tool, it did not have any Maintenance Plans on it. Since we wanted to backup one of the databases to disk, this Change Control was put in place.

Everything went fine until we tried saving the Maintenance Plan. It could not be saved and popped up the following error message.

I came across this error message for the first time. My team members stumbled on this KB article which explains the cause for this error. Here is the summary of the symptoms and cause.

  • Maintenance Plans are saved as SSIS packages and stored in MSDB by default
  • These packages use encryption to connect to SQL Server.
  • Secure Network packets are sent over Secure Socket Layer (SSL) and Transport Layer Security(TLS) layers.
  • Data fragments sent over these layers have a limit of 16K.
  • If the packet size sent from SSIS is greater than 16K, then SSIS terminates it by throwing the “Communication Link Failure” exception.

This scenario occurs only when the Network Packet size sent from SQL Server is > 16K. The default Network packet size in SQL Server is 4096B (4K). This confirmed that on this instance, the Network Packet Size was modified manually. This value can be checked in the Server Properties in SSMS or by executing sp_configure. The Server Properties for this instance looked like this.

The Network Packet Size value was set to 32767 B (~32K). I have no clue when and why was this value set. When client applications send and receive lot of network packets, increasing the Network Packet Size helps in reducing the number of packets sent across the network. This in turn helps the application performance.

But I am of the opinion that any settings under sp_configure should only be changed after knowing the pros and cons and also after thorough testing. This article clearly explains the aspects that need to be taken into consideration before changing the Network Packet Size value.

Moral of the story: Don’t change the settings on the fly. Take your time for analysis. At the end of the it is the instance that you support and you never know when the surprise package arrives!

Clean up Maintenance Plan History

Sometime back I had responded to a question where the user wanted to know the methods to cleanup the Maintenance Plan history. It is common for the DBA to come across a situation where the Maintenance Plan history grows large and as a result the Log Viewer takes a long time to load. Here are some of the approaches to clean up the Maintenance Plan history.

History Cleanup Task

SQL Server Maintenance Plan GUI has a task named History Cleanup Task. Using this task in the Maintenance Plan will help to clean up the Maintenance Plan History for the duration selected in it.

The History Cleanup Task has an option to selectively delete the history for Backup and Restore, SQL Server Agent Job and Maintenance Plan.

Log File Viewer

This is one of the lesser known options in SSMS. Using the Log File Viewer also the Maintenance Plan History can be cleaned up as shown in the below screenshot.

sp_maintplan_delete_log stored procedure

The Maintenance Plan History can also be cleaned up through T-SQL commands. The undocumented stored procedure sp_maintplan_delete_log can be used for this. The syntax for this stored procedure is as follows.

sp_maintplan_delete_log @plan_id, @subplan_id, @oldest_time

 

If I want to clear the history which is older than today for all the Maintenance Plans, this script would do the job for me.

EXECUTE msdb..sp_maintplan_delete_log null,null,'2010-03-16T00:00:00'

Unable to determine if the owner of job has server access

On Wednesday, I was working with my team member to fix some of the jobs failing on a SQL Server 2005 instance. All these jobs were failing with the following error.

Unable to determine if the owner (domainlogin) of job User Databases.Backup - User Databases has server access
(reason: Could not obtain information about Windows NT group/user 'domainlogin', error code 0x534. [SQLSTATE 42000] (Error 15404))

These jobs were executing few Backup and Rebuild Index Maintenance Plans. As the error message stated, the Job Owner did not have access on the instance. The user had access when the Maintenance Plans were created but not anymore.

If the jobs are failing because the job owner does not access, the usual approach is to change the Job Owner to a login which has requisite privileges on the SQL Server Instance. But in this case it would not help. The job owner can be changed. Since these jobs are created by the Maintenance Plans, any modifications done to the jobs would be wiped out if the Maintenance Plan is modified. Just opening the Maintenance Plan and clicking on the Save button is enough to undo all the changes done to the corresponding SQL Agent jobs.

Next step? Modify the Owner of the Maintenance Plans. But SSMS does not have an option to modify the Maintenance Plan owner through GUI. Like Andrew Calvett mentioned in this post, modifying the ownersid column in sysdtspackages90 changes the owner of a Maintenance Plan.

Executing the below query would list the current owner for all the Maintenance Plans in an instance.

SELECT name as PackageName, suser_sname(ownersid) as Owner
FROM msdb..sysdtspackages90
ORDER BY name

The output would be like the one below.

We decided to change the Maintenance Plan ownership to sa login. Executing this script would do it for us.

UPDATE msdb..sysdtspackages90
SET
ownersid = SUSER_SID('sa')
WHERE name = 'MaintenancePlanName'

Cannot create Maintenance Plan | Library not registered. (msddsp)

Today I was responding to a question related to Transaction Log backups through SQL Server Maintenance Plans. In order to simulate the same problem, I tried creating a maintenance plan in SQL Server 2005 on my laptop. Surprise! While trying to add a new Backup Task, the following error message was displayed.

It was not clear what would have gone wrong by looking at the error message. Like I did in this post, I started the Process Monitor tool by filtering sqlwb.exe. Here is what I got.

The DdsShapes.dll file existed in the correct location but while loading it there were lot of NAME NOT FOUND errors. What had changed in my laptop recently? I had installed SQL Server 2005 SP4 couple of weeks back. This was the first time I had tried creating a Maintenance Plan after installing SP4. The installation of SP4 was reported to be *successful*.

Since DdsShapes.dll file was reported to be having issues, I navigated to C:Program FilesMicrosoft SQL Server90ToolsBIN folder to check if the file existed there. It did. Since it is common to see the dll files getting messed up during installation of Service Packs, I tried the easiest approach of registering that dll again.

regsvr32 "C:Program FilesMicrosoft SQL Server90ToolsBINDdsShapes.dll"

Voila! Issue fixed and the Maintenance Plan was created successfully. One more reason why I love all the sysinternals tools.

SQL Agent job fails | SELECT permission deined on object ‘sysdbmaintplans’

Today’s post is also about SQL Server Agent and jobs not starting up. Again it is on the same instance where SQL Server Agent was not starting and yesterday I had posted on how it was fixed. SQL Server Agent was running fine and all the scheduled jobs were executing successfully except for the Backup Jobs. These jobs were taking the database backups through the Maintenance Plans (sqlmaint.exe). The job history had only the following message.

Executed as user: domain\AgentAccount. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

To get more information related to the Job failure, I enabled Verbose Logging for the SQL Agent job step is shown in the below screenshot.

As expected the Output file of the Job Step had much more details in it.

Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.2039
Copyright (C) Microsoft Corporation, 1995 - 1998
(null)
Logged on to SQL Server 'InstanceName'
as 'Domain\SQLServerServiceAccount' (trusted)
SELECT permission deined on object 'sysdbmaintplans', database 'msdb', owner 'dbo'
Process Exit Code: 1(Failed) (SQLSTATE 01000) (Message 0) slssqlmain failed (SLSTATE HY000) (Error 500000). The step failed.

Permission denied error again? The SQL Server Agent account had sysadmin privileges and some of the other jobs were successful. The catch was this line in the output file.

Logged on to SQL Server 'InstanceName'
as 'Domain\SQLServerServiceAccount' (trusted)

On this instance the Service Accounts for SQL Server and SQL Server Agent services are different. Even though the job owner was SQL Server Agent service account, the job would connect to the Instance using the SQL Server service account credentials. This is because the SQL Server Maintenance Plans make use of sqlmaint.exe which in turn calls xp_sqlmaint an Extended stored procedure. With this understanding it was easy to track down why the Maintenance Jobs were failing. The SQL Server service account was entered as serviceaccount@domain.com.

Since this server had known issues with this naming convention, all it needed was to enter the service account of SQL Server service to domain\serviceaccount.

When the SQL Server Agent service account was changed to follow the correct naming convention, SQL Server had created a new login which had access to SQL Server through Group Membership.

But the SQL Server service account did not have an individual login through Group Membership. Hence even though the Domain Group of which the service account was a member of had access to the instance, it was not getting the requisite permissions because it was unable to authenticate with the Active Directory. It is all because the way the domain and the trust across the domain had been setup.

The Change Control to modify the naming convention of the SQL Server service account was successfully implemented on Sunday and the Maintenance Jobs have run successfully ever since.

My experiments with Maintenance Plans in SQL Server Denali

For sometime now everyday I used to think “Today I will spend some time on Denali”. Due to various reasons it would somehow get missed in my To-do list. I had downloaded and installed SQL Server Denali CTP1 as soon as it was available for download.

When it comes to comparing features and reporting bugs in SQL Server as far I have seen no one can beat Aaron Bertrand (blog | twitter). He had already posted quite a number of articles on the new features and lot of other stuff about Denali here.

Maintenance Planned!

Today I decided that I will give it a shot. I finally fired up Windows Server 2008 on my VM and started SSMS. Connected to the instance and now what? Since in the SQL Server forums many of the questions revolve around Database Backups, Maintenance Plans was the first thing that came to my mind. Maintenance Plan is one of the features in SQL Server which has been constantly improving.

Finally I was there looking at the Maintenance Plans node. Most noticeable difference was that there was no New Maintenace Plan option in the context menu.

I was left with only the Maintenance Plan Wizard to create the Maintenance Plans. There was not much change in the screens, all looked same as the one in SQL Server 2008.

Now the Maintenance Plan is created, the next step is to check how it looks like while editing and what new features are added there. Right clicked on the Maintenance Plan and click Modify.

Modify? Where is Modify? Opened my small eyes wide open and searched, no Modify! The next destination was obviously SQL Server Denali CTP1 Release Notes. Here is the Note about Maintenance Plans.

This is the reason why the New Maintenance Plan and Modify options were missing in the Maintenance Plans. The only option to Edit the Maintenance Plans is to use the Business Intelligence Development Studio (BIDS). So, opened up a new Integration Services Project in BIDS and imported the Maintenance Plan by right clicking on SSIS Packages and Add existing package.

Double clicking on the Tasks again did not open the Task Designer due to the reasons mentioned earlier.

As of now the only option to edit the Maintenance Plan taks is to make use of the Properties tab for each task as in the below screenshot.

It was indeed a journey on a bumpy road. Before I could explore any further, my phone started ringing. It was a call from the office, “Pradeep, some issues while changing the service account….“. My day had just begun. Shut the VM quickly to hop on to the call.

What’s in a name? Enough to make the backup jobs fail

Database backup failure is one of the common issues faced by the DBA. There are many reasons for the backup to fail like Permissions issue, lack of drive space and so on. Recently I came across a question where the backup was failing, but not for the obvious reasons.

What is the issue?

  • All the databases on an instance were configured to be backed up using a Maintenance Plan. One of the databases had some issue and it was detached/attached back to the instance.
  • The database backups for all the databases were successful except for the one which was attached recently.
  • The error message read something like this.
"Cannot open backup device 'D:\backup\mydatabase \mydatabase _backup_201012081958.bak'.

Operating system error 3(The system cannot find the path specified.).

What troubleshooting was done?

  • Checked if the folder D:\backup existed. It did.
  • Checked for the permissions of the service account on the backup path. It was not an issue since the other databases were being backed up to the same path.
  • Any error messages in the SQL Server Error Log during that time. No error messages reported except for the one mentioned above.
  • Checked the drive for any possible issues. No error messages were available in the Event Viewer.

How the issue got fixed?

A closer look at the error message reveals it all.

Yes, you guessed it right. The database name had trailing spaces in it! While attaching the database through SSMS one extra space got added at the end of the database name. Renaming the database using the below script fixed the issue.

ALTER DATABASE [mydatabase ] MODIFY NAME ='mydatabase'