Distribution Cleanup Cannot Clean Up Snapshot Folder
Sep 7, 2007
We are using SQL Server 2005 SP2 to do transactional replication.
We and have a separate service account for the SQL Agents (sqladmin) vs. SQL Replication Agents (sqlrepadmin). It is my understanding this is a replication security best practice. The sqlrepadmin has full permissions on the snapshot share folder and it's subdirectories. The sqladmin account does not have permissions at all.
I have been getting an error message when we run the distribution clean up job.
Executed as user: PRODsqladmin. Could not remove directory '\Tes01boxRepldatauncqabox01_DB01_TO_ORACLE20070905104896'. Check the security context of xp_cmdshell
I have dropped the publication and recreated which is what appears to have caused the error.
From
http://technet.microsoft.com/en-us/library/ms151151.aspx
Note:
If a publication is dropped, replication attempts to remove the snapshot folder under the security context of the SQL Server service account. If this account does not have sufficient privileges, log in with an account that does have sufficient privileges and remove the folder manually. Removing a folder requires the Modify privilege if the folder is a local path or the Full Control privilege if the folder is a network path.
The note above implies that the SQL Server service account (sqladmin) needs permissions on the snapshot folder as well.
Finally my questions:
Is there a workaround that will allow the distribution cleanup job to run as sqlrepadmin and perform the delete?
If both sqlrepadmin and sqladmin need permissions to the snapshot what is the reasoning from a security perspective of separating them out?
View 3 Replies
ADVERTISEMENT
Jul 6, 2007
On SQL Server 2005 SP2 for Publisher and Distributor on the same instance, my old snapshots are not being cleaned up.
The following error is in the agent history:
Executed as user: DomainMyUser. Could not remove directory '\vmsql01ReplDatauncPublication_TRANSACTIONAL20070702104416'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.
xp_cmdshell is enabled and I can run commands like :
exec master.dbo.xp_cmdshell ' md c:TestFolder'
The permissions to the snapshot share and file system are that DomainMyUser has full control.
I have logged into the machine as this user and can remove snapshots so it does not seem to be a permission issue.
On other machines I do not get any errors but the snapshot folder still is not cleaned up.
Any suggestion as to what the problem could be?
Thanks,
Amy
View 3 Replies
View Related
Jun 17, 2007
I would love to be able to run the distribution cleanup job with a switch that says cleanup all distributed transactions.
Because when I use peer to peer replication the @allow_initialize_from_backup publication property is set to true which is good. But it has the down side that transactions are stored the max retention period in the distribution database. I want to use the deafault 72 hours for my retention period so that the subscritions don't get deactivated but in a system with a high transaction rate there wil be a lot of transactions in 72 hours. This means that the cleanup job will have a tough time to figure out which transactions to delete so the cleanup job will run for a long time not a very big problem but the problem is that the cleanup job will keep the log reader agent from delivering trtansactions to the distribution database and the subscribers won't get their data in time.
Could Microsoft please give me a switch so I can choose when I want to save my transaction and when I want to delete them as soon as they have been delivered to all subscribers?
Is this a feature in SQL Server 2008? Could it be released in SP3 for SQL Server 2005. (The SP 2 cleanup job has a bug so I have to use the SP 1 verison of the cleanup job)
View 2 Replies
View Related
Sep 15, 2006
Using SQL Server 2005. Replication working fine except the distribution table is continuely growing. Started to configure maintenance job (catagory: REPL-Distribution Cleanup) Any suggested steps that will not break the replication. Is there a SP available that will address my problem.
Thanks,
View 1 Replies
View Related
Apr 13, 2007
Hi all,
I found that my testing server is accumulating shapshot folder in repldata. Everytime, we refresh the tables (snapshot publication), a new folder created without the old folders. And I found that there is at most 1 snapshot folder remain in production server. Any parameter to adjust the retention period of the snapshot folder? Thanks in advance
View 4 Replies
View Related
Sep 11, 2006
Hi,
I need to restore a sql server 2000 database which does not have any backups apart from a snapshot folder which contains some .bcp, .IDX and .Sch files. (there was a transactional replication setup on this database at some point and the snapshot folder is still there.)
Is this possible? any help will be highly appreciated.
thanks,
Alind
View 4 Replies
View Related
Feb 23, 2005
At present our snaopshot folder for merge replication(mainly) and our snapshot replications are on a seperate server. We now need to move the folder to a new server.
Anybody explain how we will do this and what affects it will have
Thanks
View 2 Replies
View Related
Nov 28, 2006
One of the things that annoys me in 2005 is that you cannot change the snapshot location during the setup of the replication. Does anyone know if I missed something or if they are going to change that in a future patch?
View 5 Replies
View Related
Sep 22, 2015
I have a merge replication. Currently works fine. Publisher & Distributor are on the same server. I need to change the location of the alternate folder for the snapshot files.
I’ll probably just change it through the GUI, but would I use sp_changedistpublisher or sp_changemergepublication if I were scripting everything?
My real concern is the subscribers. Do I have to ‘tell’ the subscribers where the alt folder has been changed to? Do I just run sp_changemergepullsubscription on the subscribers?
View 1 Replies
View Related
Jun 6, 2007
hi all
when my snapshot folder is on a network drive, on which "simple filesharing" is enabled, i can access that folder without typing a user and password and configuring login security settings.
but in fact the network drive on which the snapshot folder will be located will do have a login and password. (when i go to it with windows explorer, i have to enter my login and password).
won't this cause problems for the replication? as i don't see where to enter net network login when i configure the replication via the wizard.
Thanks for help
View 3 Replies
View Related
Sep 19, 2007
I dont alot about sql server 2005(Express edition). For debugging purposes i want to copy the whole app_data folder(.mdf & .log files) on the production server to another folder on the same machine(or sometimes to a network folder). So when i copy and try to paste this App_data folder to a new location, i get this error message
"cannot copy ASPNETDB: it is being used by another person or program. close any programs that might be using the file and try again."
After reading the above message, i close visual web developer, stop the website in IIS and stop the SQLExpress service on the server and try again but still get the same message.
So how can i make sure that all the programs accessing these database files are closed such that i'm able able to copy them to a different location.
View 2 Replies
View Related
Jul 2, 2015
I know a WMI event watcher can be used to watch for a new file being added to a folder. However, I need to check for new folders being added to an existing folder. I haven't been able to find a post on doing this. Is there a way in WQL to check for a new folder being added instead of a new file? I've used SQL for years, but am new to SSIS.
View 2 Replies
View Related
Oct 31, 2007
Ok,
I have a network folder called A
I have a SharePoint (2007) Document Library site called B. Web Client is enabled on the server and B is mapped as a Drive (let's call it Y for this discussion)
I want to move documents in A to B. Easy enough, right? Not so....
I first started by creating a batch file that issues a COPY \A \Y /Y at the command prompt. Viola! Worked Great!
I then moved that command to a SQL Agent job as a CMDExec statement (exact same statement) and attempted to run it.....CRASH! It found the files in A but then said "The system cannot find the path specified"
Ok, so I tried it in SSIS. CRASH! Checked the error log. Same thing...
So I then checked the account under which the SQl Agent was running (special domain account for all our SQL Servers). Thinking it might mater I changed it to run under my name (I'm in Domain admin). I also ensured I had permissions to the SPS 2007 library as well. (I did).
Ran again! CRASH! Same error....
So, I created a batch file , placed thec ommand in the batch file and ran that from the command prompt! Viola! Worked Great.
So, I was thinking of how ingenious I was as I pasted my C:RootCopy.bat into my SQL Agent job. With a big grin on my face I right clicked and picked "Start Job at step".......CRASH! Same error.
Does anyone have any ideas on this ???????????????
Thanks,
Stephen
View 3 Replies
View Related
Jun 20, 2007
Hi
I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated.
Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name.
The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors.
I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name.
All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.
View 1 Replies
View Related
Sep 4, 2006
I have a set of folders with the following structure:
MainFolder
1999
SpreadsheetA.xls
SpreadsheetB.xls
SpreadsheetC.xls
2000
SpreadsheetA.xls
SpreadsheetB.xls
SpreadsheetC.xls
etc.
Is there a way that I can use the foreach loop container to loop the sub folders? My plan was to get the folder name and path into a variable, use this to build the connection string for each file in this folder, carry out the upload for that folder, then move onto the next folder and do the same thing, but I cannot see a way to do this.
Thanks,
Sam
View 2 Replies
View Related
Mar 28, 2008
I am having problem with moving a file from one folder to another folder. Here is the detailed scenario:
I want to move a input.csv file from shared input folder to shared archive folder. i am using the below code to do this.
declare @inpath varchar(100)
SET @inpath = 'move "\abcdefINPUTinput*.csv" "\abcdefARCHIVEarchive.csv"'
EXEC @filestatus = master..xp_cmdshell @inpath
but the problem was it was cutting the input.csv file from INPUT folder but not pasting it in the ARCHIVE folder.
I really appreciate if anyone can help me to solve this or anyone can tell some workarounds.
Thanks,
View 20 Replies
View Related
May 29, 2006
My DB is holding some data for the moment I'd like to clean.
I'm wishing to erase the WHOLE data stored in the database (just the data, not the DB itself). What SQL command should I use ?
If there is another way to do what I'm wishing to do, let me know please.
Thanks.
View 8 Replies
View Related
Dec 13, 2006
PHP Code:
WHERE
(dbo.document.docVisible = 'yes') AND (dbo.document.docHide = 'NO') AND (dbo.t_files.fileMoved = '1') AND (dbo.documentCategory.docCategoryName LIKE '%olic%') AND (dbo.minisite.sectionLive = 1) AND (dbo.t_files.fileName <> N'A LINK') AND (dbo.t_files.fileName NOT IN (SELECT exFileID FROM t_exclude))
OR (dbo.document.docVisible = 'yes') AND (dbo.document.docHide = 'NO') AND (dbo.t_files.fileMoved = '1') AND (dbo.minisite.sectionLive = 1) AND (dbo.t_files.fileName <> N'A LINK') AND (dbo.t_files.fileName NOT IN (SELECT exFileID FROM t_exclude)) AND (dbo.document.docDesc LIKE '%olic%')
OR(dbo.document.docVisible = 'yes') AND (dbo.document.docHide = 'NO') AND (dbo.t_files.fileMoved = '1') AND (dbo.minisite.sectionLive = 1) AND (dbo.t_files.fileName <> N'A LINK') AND (dbo.t_files.fileName NOT IN (SELECT exFileID FROM t_exclude)) AND (dbo.document.docType BETWEEN 1 AND 4)
OR (dbo.document.docVisible = 'yes') AND (dbo.document.docHide = 'NO') AND (dbo.t_files.fileMoved = '1') AND (dbo.minisite.sectionLive = 1) AND (dbo.t_files.fileName <> N'A LINK') AND (dbo.t_files.fileName NOT IN (SELECT exFileID FROM t_exclude))
OR(dbo.t_files.fileName IN ('127-2006-1-27-3321130.pdf', '127-2006-1-30-3726619.pdf', '127-2006-1-27-5700042.pdf', '127-2006-1-27-5678586.pdf', '127-2006-1-27-5693574.pdf', '127-2006-1-27-5873392.pdf'))
ORDER BY dbo.document.docDesc
Right - as you can see, some of the line slook pretty similar. When I try and do:
(one AND two AND three) AND (four OR five), I get
(one AND two AND three AND four)
OR
(one AND two AND three AND five)
when using enterprise manager. Is there anyway to keep the first way of doing it. Otherwise, everytime I add anotehr OR statement in, I'll have to create a new line!
View 5 Replies
View Related
Mar 28, 2006
I had received a message that my log file is full and it do not enable to me to do a database backup before free up disk space. How do i clean up de log file (_log.ldf)?
View 5 Replies
View Related
Apr 10, 2008
I have the following query which strips out middle initial data from the first_name column and populate the middle_initial column with the relevant data.
What my query does not handle is when a first name has a single character (IE: A). In its current state, the "A" would be moved to the middle_initial field with a period added to the end (IE A.). The first_name column would also include "A". Basically, when a single character first name is found in the first_name column, I do not want to populate the middle_name field.
Hope this does not sound too cryptic; query is below along with some sample data when run.
SELECT first_name,
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-1,1)=' '
THEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name))),1) +'.'
ELSE NULL
END AS 'middle_initial',
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-1,1)=' '
THEN LEFT(LTRIM(RTRIM(first_name)),CASE WHEN LEN(LTRIM(RTRIM(first_name)))>=2 THEN LEN(LTRIM(RTRIM(first_name)))-2 ELSE
LEN(LTRIM(RTRIM(first_name))) END)
ELSE LTRIM(RTRIM(first_name))
END AS 'first_name_removing_initials'
FROM contact
Sample Data
first_name, middle_initial, first_name_removing_initials,
Paul, NULL, Paul
A, A., A,
A Fred, NULL, A Fred,
Aaron, NULL, Aaron
View 4 Replies
View Related
Nov 28, 2007
Dear All,
i'm trying increase the performance of one select statement.
after trying first time, at the second time it is giving results fastly.because the data is there already in the buffers.
how can i clean the buffers everytime after run the query?
i'm using
--dbcc dropcleanbuffers
--dbcc freeproccache
are these enough or need some more....
please guide me
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 8 Replies
View Related
Jan 17, 2008
I have a db server that had served both my application data and reporting services. I have since installed a second db server to be the reporting services server; the primary box still stores the application data. I am keeping reporting services installed and set up on the primary box, since I would like it to act as a warm standby for the reporting services function.
However, the ReportServerTempDB on my primary box is still quite large. The ChunkData table is quite close to 10 gig. The CleanupCycleMinutes property is set to the default of 10. Clearly any data that might be in this table is far older than that.
Is there some recommended method of clearing this data? Can I just delete the rows or do a truncate on the table? I would prefer not to uninstall/reinstall reporting services, as this machine is actively serving my application data.
Thanks.
View 1 Replies
View Related
Apr 28, 2008
Hi everyone,
What is the easy way to clean up all ms sql 2005 tables?
For example, a database table named Customers which has triggers and primary key and foreign keys. Now I clean up the Customer table using the folowing statement
delete Customers
And the ms sql 2005 asks me to remove all triggers and foreign keys before allow me to clean up the Customers table. Is there a way to clean up all tables without remove the tiggers and foreign keys?
Thanks
May
View 4 Replies
View Related
Mar 3, 2004
Hi,
I need some advise in the log shipping. The log files in the primary server get cleaned up according to what I have specified in the maintenance plan. But the log files that got shipped to the secondary server stay there for ever wasting my hard disc. Will it make any problem if I remove them or can I set it up to remove all files earlier than past 2 hrs? Please advise.
Thanks
View 6 Replies
View Related
May 16, 2007
Hi,
My problem is that I cannot completely clean buffer cache on SQL Server 2005 version 9.00.2047.00 (probably SP1).
Right after I run DBCC DROPCLEANBUFFERS in the context of my database (this is development server, and so far I am only the one who is working with a particular database), I run a script that quetries sys.dm_os_buffer_descriptors view also from the context of my database to make sure that the buffer cache is really clean. However it shows large number of entries totalling 42 MB.
I ran both DBCC an the script in the past too, and it always showed nothing in the results, that means that buffers were really clean. The reason why I am running this is for benchmarking of existing and new application.
Does anybody have any idea, suggestions, how to troubleshoot this issue ? I already closed all connections to this database, but rebooting the server is not an option since other people are also working on it.
Thanks
View 2 Replies
View Related
Sep 22, 2005
How to clean all messages of a queue ?
View 5 Replies
View Related
Mar 13, 2006
I have a custom Data Flow task that creates temp files to the system temp directory during processing. A lot of times, we'll use SSIS to do one data transformation, running and tweaking the package along the way... we do this in the designer ... if we notice something that's incorrect in the data view, we just hit the stop button and fix it. However, when we do this, the Cleanup() function isn't called, and my temp files are left in the temp directory, when they really ought to be disposed of.
Is there a method that gets called every time when the DtsDebugHost quits, whether it finished, didn't finish properly, or was stopped in the middle? What would be a good way (other than having some service that monitors what temp files are used by what processes) to clean up temp files after we don't need them?
~Steve
View 1 Replies
View Related
Oct 12, 2007
We have SQL SERVER 2000 Runnin on our server. We are trying to do a db cleanup, ie all tbls/views that were created earlier and are not needed any more, need to be deleted. However, is there a way, to do a cleanup in a better method, other than going thru the whole bunch of tbls/views manually,and determining which ones are needed or not and delete the ones that are not needed.
View 5 Replies
View Related
Sep 11, 2006
Is there some recommended way to clean input before submitting it to the database? We'd like to develop a library that can be used on our ASP/ASP.NET apps to filter input before it's sent to the SQL Server and Oracle databases. Is there a way to create a .NET DLL that can be used for both ASP.NET and classic ASP apps. Thanks.
View 3 Replies
View Related
Feb 4, 2003
I am importing Visual FoxPro (6) views into SQL 2000 tables and I am looking for a snippet of code to "clean up" date fields upon insertion. I can insert the views into the SQL tables fine using the tables/views selection of DTS as my source. However, I would like to ensure the date fields are in fact vaild dates and not garbage using the SQL query option of DTS source. I would like to do the insert and cleanup in one step. Do you have a snippet of code to validate a date field that I can use? Thank you for any assistance in advance, Terry.
View 3 Replies
View Related
May 12, 2006
I posted this in the SQL Server Express forum as well...
My app deployed via ClickOnce. It ships with a database creation script which creates the database in the ClickOnce data directory. Upon uninstall, the entire data folder is deleted successfully. But if you then reinstall the app, SQL Server Express still thinks the database exists. Right now, to get a re-install to work, I have to go into Management Studio Express, click on the db (get an pop-up error as the physical db file is no longer there) and then click delete.
So my question is... what system proc should I execute in the db creation script before I run the CREATE DATABASE command?
View 1 Replies
View Related
Mar 13, 2008
Good afternoon.
I have an IS package that contains some legacy DTS tasks. The package does run successfully on some machines, but others not at all, generating a weird error that says "Error: The task failed to load. The contact information for the task is "Execute DTS 2000 Package Task;Microsoft Corporate...." There's more to the error but searching on it hasn't turned up any results so I'm not posting the entire thing. I can if requested.
On the server where the DTS packages fail, regular IS packages that contain no DTS legacy tasks run successfully, and the only difference between the machines that work and don't work is SQL Server 2000 is not and was never installed, whereas the machines that work are running 2000 and 2005. The servers where the tasks fail version 9.0.3050.
One thing I have noticed is that on the machines where DTS works there is an extra file in C:Program FilesMicrosoft SQL Server90DTSBinn called Microsoft.SqlServer.Exec80PackageUtil.dll. Copying the file to a non-working server did not solve the issue. I tried installing the backwards compatibility update but it said that a newer version was already installed.
If anyone could help I would greatly appreciate it. If you need more information just ask and I would be happy to provide it.
Thanks again,
Kelly
View 5 Replies
View Related
