Distribution clean up not working

Hi
Pulling my hair out trying to find a resolution to the following problem.
Any help would be greatly appreciated.
Windows 2000 OS SP4, SQL Server 2000 SP3 replicating using transactional
replication from one server (lets call this server 1) inside our network out
to the internet to another server (lets calls this server 2)
Replication job failing for Distribution Cleanup on server 1 where articles
are created and replicated from. This is the job which removes all the old
snapshots after a set period of hours.
The error produced is the same each time bar the folder name which changes
depending on the folder it has problems with.
Executed as user: domain\username. Could not remove directory
'\\DEV-SQL\H$\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040601045610\'.
Check the security context of xp_cmdshell and close other processes that may
be accessing the directory. [SQLSTATE 42000] (Error 20015) Associated
statement is not prepared [SQLSTATE HY007] (Error 0)
Replication-@.rowcount_only parameter must be the value 0,1, or 2. 0=7.0
compatible checksum. 1=only check rowcou: agent ReplicationDB@.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.
The domain user which the sqlserver and sqlserveragent services use is in
the local administrators group on server 1.
If I login as the user which the services use, I can execute xp_cmdshell
'rmdir /S /Q "\\DEV-SQL\H$\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040601045610"' in
query analyzer with no issues and the directory is deleted.
To my knowledge there is nothing accessing the files within the folder which
would stop the server from gaining an exclusive lock on them for deletion.
Thanks
Andy
Andy,
I can't spot anything strange about your setup. Ideally the share shouldn't
be an admin one - it should be in the form \\DEV-SQL\Repldata with the
associated rights on htis share. You could reconfigure with this, but this
shouldn't be the issue in your case. I'd try to schedule a job yourself
which runs the remove directory command and see if it fails with the same
error message. Also check the opened files for any remotely locked files in
computer management. Finally if none of this helps then have a look at
filemon to monitor locked files
(http://www.sysinternals.com/ntw2k/source/filemon.shtml) or if you're on Win
2003, http://www.mvps.org/sramesh2k/processlock.htm. Please post up your
findings, and also I'm assuming that you have a central
publisher/distributor - is this the case?
Regards,
Paul Ibison
|||Paul
Thanks for your response, FileMon has helped me make some progress with this
issue. I have attached some output from the log below. CMD.exe tries to get
the directory but it is already opened by inetinfo.exe and CMD.exe then
returns a sharing violation (towards the bottom of the log output).
InetInfo.exe is the IIS Admin Service Helper, any ideas why it may be
looking at this directory ?
Andy
13591 10:49:22 CMD.EXE:5224 QUERY INFORMATION H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS Attributes: D
13592 10:49:22 CMD.EXE:5224 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS Options: Open Directory Access: All
13593 10:49:22 CMD.EXE:5224 DIRECTORY H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS FileBothDirectoryInformation: *
13594 10:49:22 CMD.EXE:5224 READ H: SUCCESS Offset: 0 Length: 4096
13595 10:49:22 CMD.EXE:5224 DIRECTORY H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS FileBothDirectoryInformation
13596 10:49:22 CMD.EXE:5224 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.BCP SUCCESS Options: Open Access: All
13597 10:49:22 CMD.EXE:5224 READ H: SUCCESS Offset: 531218432 Length: 4096
13598 10:49:22 CMD.EXE:5224 QUERY INFORMATION H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.BCP SUCCESS FileObjectIdInformation
13599 10:49:22 CMD.EXE:5224 DELETE H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.BCP SUCCESS
13600 10:49:22 CMD.EXE:5224 CLOSE H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.BCP SUCCESS
13601 10:49:22 CMD.EXE:5224 READ H: SUCCESS Offset: 40960 Length: 4096
13602 10:49:22 inetinfo.exe:1080 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS Options: Open Directory Access: All
13603 10:49:22 CMD.EXE:5224 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.SCH SUCCESS Options: Open Access: All
13604 10:49:22 CMD.EXE:5224 READ H: SUCCESS Offset: 531169280 Length: 4096
13605 10:49:22 inetinfo.exe:1080 DIRECTORY H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\ NO
SUCH FILE FileBothDirectoryInformation: TBLLAT~1.BCP
13606 10:49:22 CMD.EXE:5224 QUERY INFORMATION H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.SCH SUCCESS FileObjectIdInformation
13607 10:49:22 inetinfo.exe:1080 CLOSE H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS
13608 10:49:22 CMD.EXE:5224 DELETE H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.SCH SUCCESS
13609 10:49:22 CMD.EXE:5224 CLOSE H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.SCH SUCCESS
13610 10:49:22 inetinfo.exe:1080 DIRECTORY H:\ SUCCESS Change Notify
13611 10:49:22 CMD.EXE:5224 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.IDX SUCCESS Options: Open Access: All
13612 10:49:22 inetinfo.exe:1080 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS Options: Open Directory Access: All
13613 10:49:22 CMD.EXE:5224 READ H: SUCCESS Offset: 531214336 Length: 4096
13614 10:49:22 CMD.EXE:5224 QUERY INFORMATION H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.IDX SUCCESS FileObjectIdInformation
13615 10:49:22 CMD.EXE:5224 DELETE H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.IDX SUCCESS
13616 10:49:22 inetinfo.exe:1080 DIRECTORY H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\ NO
SUCH FILE FileBothDirectoryInformation: TBLLAT~1.SCH
13617 10:49:22 CMD.EXE:5224 CLOSE H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\TBLL
AT~1.IDX SUCCESS
13618 10:49:22 inetinfo.exe:1080 CLOSE H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS
13619 10:49:22 CMD.EXE:5224 DIRECTORY H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\ NO
MORE FILES FileBothDirectoryInformation
13620 10:49:22 inetinfo.exe:1080 DIRECTORY H:\ SUCCESS Change Notify
13621 10:49:22 CMD.EXE:5224 CLOSE H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS
13622 10:49:22 inetinfo.exe:1080 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS Options: Open Directory Access: All
13623 10:49:22 CMD.EXE:5224 OPEN H:\Program Files\Microsoft SQL
Server\MSSQL\ReplData\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SHARING VIOLATION Options: Open Directory Access: All
13624 10:49:22 inetinfo.exe:1080 DIRECTORY H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\ NO
SUCH FILE FileBothDirectoryInformation: TBLLAT~1.IDX
13625 10:49:22 inetinfo.exe:1080 CLOSE H:\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA\unc\dev-sql_WebShared_WS_LatestAds\20040602094908\
SUCCESS
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Or%23DS9%23REHA.2976@.TK2MSFTNGP10.phx.gbl...
> Andy,
> I can't spot anything strange about your setup. Ideally the share
shouldn't
> be an admin one - it should be in the form \\DEV-SQL\Repldata with the
> associated rights on htis share. You could reconfigure with this, but this
> shouldn't be the issue in your case. I'd try to schedule a job yourself
> which runs the remove directory command and see if it fails with the same
> error message. Also check the opened files for any remotely locked files
in
> computer management. Finally if none of this helps then have a look at
> filemon to monitor locked files
> (http://www.sysinternals.com/ntw2k/source/filemon.shtml) or if you're on
Win
> 2003, http://www.mvps.org/sramesh2k/processlock.htm. Please post up your
> findings, and also I'm assuming that you have a central
> publisher/distributor - is this the case?
> Regards,
> Paul Ibison
>
|||Andy,
the only relationship I can see is if you're doing a FTP initialization as
the inetinfo.exe does manage FTP access. Is it possible there is a FTP
subscriber in mid-flow?
Regards,
Paul Ibison
|||Hi Paul
Thanks for you reply.
Cannot see anything to do with FTP replication in our setup here.
If this is of any help, this is a copy if the SQL alert which is sent to me
when the job retries.
DESCRIPTION: Error: 14152, Severity: 10, State: 1
Replication-@.rowcount_only parameter must be the value 0,1, or 2. 0=7.0
compatible checksum. 1=only check rowcou: agent ReplicationDB@.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
Regards
Andy
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e7iSIwISEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Andy,
> the only relationship I can see is if you're doing a FTP initialization as
> the inetinfo.exe does manage FTP access. Is it possible there is a FTP
> subscriber in mid-flow?
> Regards,
> Paul Ibison
>
|||Andy,
unfortunately this error message is a fairly generic one to the job failing.
Sorry but I really don't know why inetinfo is locking your snapshot files. As an interim solution you could kill the inetinfo process before running the cleanup agent.
I'd post up a question related to this on the IIS newsgroup and see if anyone has any ideas.
Regards,
Paul Ibison