Manual Failover error on SQL Server 2014SP1 AlwaysOn

21 Jan

I implemented AlwaysOn Availability groups solution on SQL Server 2014 SP1.

During the testing of a manual failover (via the GUI), I encountered the following error:

Availability-group DDL operations are permitted only when you are using the master database.  Run the USE MASTER command, and retry your availability-group DDL command. (Microsoft SQL Server, Error: 35208)

I was puzzled by the error, as it manifested itself just after we installed the latest  Cumulative update package 4 (CU4) for SQL Server 2014 Service Pack 1.
For a few moments I though that I have encountered a new bug.

I tried restarting the machine (the whole implementation wasn’t in production just yet) and I could perform one manual failover, but upon attempting another manual failover we got that error message again.

I tried performing a manual failover via Transact-SQL:


the failover went fine with no issues.

This led me to suspect that it’s a login/user related issue.

I logged out and connected with SA account. I tried performing  a manual failover via the GUI, and it finished successfully.
This confirmed me it was a login/user and not a GUI bug.

I checked the properties of my login (it was a group for the SQL admins) and saw that the default database wasn’t master, but a user database that was recently created by the application.
Once the default database was changed back to master, we had no issues with the manual failovering.

Hope it’ll help somebody in case of need.
I haven’t found many articles regarding this issue online.


Running a SSIS package via SQL Agent job with a Proxy User

23 Sep

I recently needed to create a SQL Agent job that runs a SSIS package on SQL Server 2012.

I followed the steps that are detailed in the below link, to be able to run a SSIS Package from SQL Server Agent Using a Proxy Account:

However, once I finished the steps and tried running the job, it kept failing with the error:

Unable to start execution of step 1 (reason: Error authenticating proxy *****\******, system error: Logon failure: unknown user name or bad password.).  The step failed.

I was sure that password of the Proxy Account was correct, and I was quite puzzled with the issue.
I even tried changing the Proxy Account to another sysadmin account but no change.


Check the SQL Server Agent service account in SQL Server Configuration Manager. Make sure the service account is configured with the format of DomainName\UserName and not in the format of

Once I change the Service account configuration, the job ran with no issues.

Sending an Outlook Calendar meeting event via sp_send_dbmail

1 Oct

I came across a request from the business department that needed an Outlook Calendar meeting event (a Calendar reminder) whenever a specific type of data was recorded in the database.

I’ve created a procedure. In it I performed the check required by the business. In case rows returned, I sendtthe information in the mail with an ICS file attachment that contains details on the necessary reminder.

All the user has to do was to open the ICS attachment, and to click Save&Close to add it to his calender.

Prerequisites : A functioning Database mail configuration in your database.

The main part of the procedure is the sp_send_dbmail proc that sends the mail to the user

 EXEC msdb.dbo.sp_send_dbmail
     @recipients =''
    ,@profile_name = 'ProfileName'
    ,@subject = 'A Friendly reminder'
    , @query = '
+ CHAR(13)
+ ''PRODID:-//My Company//Company Calendar//EN''
+ CHAR(13)
+ ''VERSION:2.0''
+ CHAR(13)
+ CHAR(13)
+ CHAR(13)
+ CHAR(13)
+ ''DESCRIPTION:Please make a reminder''
+ CHAR(13)
+ ''SUMMARY:Please make a reminder''
+ CHAR(13)
+ ''DTEND:20140930T190000''
+ CHAR(13)
+ ''DTSTART:20140930T190000''
+ CHAR(13)
+ CHAR(13)
+ CHAR(13)
 , @attach_query_result_as_file = 1
 , @query_result_header = 0
 , @query_result_separator = 'CHAR(10)+CHAR(13)'
 , @exclude_query_output = 1
 , @query_attachment_filename = 'DividendEvent.ics'

The interesting part is the @query parameter.
In this part, you create an iCalendar file that Outlook support and recognize.

PRODID:-//My Company//Company Calendar//EN
METHOD:PUBLISH                                          Post notification of an event.
DESCRIPTION:Please make a reminder       The event description.
SUMMARY:Please make a reminder              The event title.
DTEND:20140930T190000                             Event end time, in ISO 8601 format
DTSTART:20140930T190000                    Event start time, in ISO 8601 format
LOCATION:                                                          The venue name.

More information on iCalendar format:

More information on sp_end_dbmail:

Errors in SQLAGENT.OUT log – Unable to open or read TSX cache file

14 Mar


I’ve recently implemented a MSX/TSX jobs environment in my organization.

I’ll blog about it at a different time.




Everything seams to work fine with no issues, except for a strange error message in the SQLAGENT.OUT log:

[419] Unable to open or read TSX cache file ‘E:\MSSQL10.DB\MSSQL\JOBS\TSXCACHE.BIN’ (win32 error code: 2)

Despite the error above, there’s no issues with the implementation – all jobs are working as expectantly.


The solution to the problem is to give the user account of the SQL Server Agent modify permissions to the directory:

After giving the permissions, you’ll see immediately that a new file called TSXCACHE.BIN will be created in the directory.

Roni Vered Adar.

Hello world!

30 Nov

Welcome to! This is your very first post. Click the Edit link to modify or delete it, or start a new post. If you like, use this post to tell readers why you started this blog and what you plan to do with it.

Happy blogging!