The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog



No Ads ever, except search!
Monday, May 27, 2024 Login

Blog posts for the month of August,2012.
SQL Agent Housekeeping8/10/2012 8:50:16 AM

A follow up to this email.  Rick found a very important detail that I left out of this “fix”.  When adding the SQL Server account to the permissions, you have to add it as

NT Service\MSSQL${Instance} .  You also need to change the “location” from the domain to the Servername. 


SQL Agent jobs using xp_cmdshell that delete files failing.

Kayden Kross inspires a bit of friday morning contemplation . . . Also, there's a SQL Agent housekeeping issues we've finally solved.   SQL Agent delete steps failing, for backups and replication jobs. (Actually Tom Reeves, thankyou!).

In the past the quick and dirty way of solving this was by adding “everyone” to the folder permissions with modify rights. 


Below is an error I copied from one of the distribution jobs, as you can see it leads you to believe that SQLAgent${Instance} is having the issue.  I ran a profiler capture while I ran the job and it even stated that the job was being run as SQLAgent${Instance}.  I added SQLAgent${Instance} to the folder permissions to no avail, it still didn’t work.  I then added MSSQL${Instance} to the folder permissions with Modify rights and this seemed to have fixed it!  In this example I added the permissions to the folder E:\Replication\unc\{server}${Instance}_{Instance}12_{Instance}12


We only seem to have this issue with the newer Windows Server 2008 machines, due to the UAC (User Access Controls). 


Step ID                 1

Server                   {server removed}\{Instance Removed}

Job Name                            Distribution clean up: distribution

Step Name                         Run agent.

Duration                              00:00:00

Sql Severity        16

Sql Message ID 14152

Operator Emailed           

Operator Net sent          

Operator Paged               

Retries Attempted          0



Executed as user: NT SERVICE\SQLAgent${Instance}. Could not remove directory 'E:\Replication\unc\{Server}$ACCOUNTMASTER_ACCOUNTMASTER12_ACCOUNTMASTER12\20120809000011\'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015)  Replication-@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only check rowcou: agent distribution@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only  scheduled for retry. Could not clean up the distribution transaction tables. [SQLSTATE 01000] (Message 14152).  The step failed.


Blog Home