Truncate Transaction Log On A Pull Transactional Replication Environment
Oct 3, 2007
I created transactional replication on a database and setup pull subscriptions on each subscriber to run at a scheduled time once a day. The scheduled start time on each subscriber can differ. The transaction log on the publishing database will eventually consume all possible disk space. Is it possible (and safe) to shrink or truncate the transaction log file for the publishing database before all the subscribers completed running its daily pull subscription? If not, how can I manage disk space for the transaction log on the publishing database and ensure all transaction are replicated to the subscriber?
Thanks in advance.
View 1 Replies
ADVERTISEMENT
May 5, 2007
Hi
Can we do two way transactional replication in WAN environment user SQL SERVER 2005 STANDARD EDITION.
Regards,
Anand
View 1 Replies
View Related
Jan 5, 2005
I have setup a pull subscription on the subscriber for transactional replication. The distribution job continues to run, but doesn't do anything because it says: The initial snapshot for publication 'man4' is not yet available.
How can I tell if the snapshot is running, and can I see the snapshot where it was created?
View 1 Replies
View Related
Jan 25, 2007
Hi
We have setup transactional replication between 2 databases on SQL Server 2000 SP3a (~70GB), using a concurrent snapshot (to prevent locking out of the live database) to initilaise the data and a pull subscription from the second database.
From analysing the msdistribution_history table in the distribution database on the subscriber it appears that the snapshot is being applied in a continuous loop to the subscriber database. Viewing the comments column in the msdistribution_history table we can see the following sequence of events occuring
Initialising
Applied script 'snapshot.pre'
Then it applies all the schema files .sch
Then it applies all the index files .idx
The it bulk copies the data in (bcp)
Then it creates the Primary Keys
Then it applies all the trigger files .trg
Then it applies all the referential integrity files .dri
These all complete successfully but then the process kicks off again immediately after reapplying the snapshot. We are unaware of any settings that may be causing this.
Any help on what maybe causing this would be much appreciated.
View 5 Replies
View Related
Feb 9, 2015
SQL 2012 Ent SP2
Database is in simple recovery mode, and published with transaction replication push subscription, just one subscriber but the database is huge. I don't want to overwrite the schema at the subscriber either.
I had to run an alter database command on a published database, it created so many logs that an extra drive had to be added along with an extra log file to accommodate all the logs.
The problem I have is I'd like to know clear the file of logs so I can drop the temporary log file, and give the drive back, but I cannot.
I have tried dbcc shrinkfile with the emptyfile option but it never clears, I have also tried it with notruncate and truncateonly options (mainly out of desperation).
I do not need to worry about point in time restore as a full backup is taken before and after the operation. After which the database will be put back into Full recovery mode.
I have looked at log_reuse_wait_desc and the file says 'Replication', so I am now thinking the file cannot empty because replication is keeping one of the VLFs active. I tried dropping and recreating the subscription hoping it might free something up and I could get somewhere, but it made no difference.
Do I have to remove replication completely to get round this? Surely not.
I have also tried putting the database back into full recovery mode, doing a full DB backup, and a transaction log backup, but its made no difference, which is also what makes me think a portion of the log is still active because of replication, and perhaps the transactions have not gone through to the subscriber, which raises another question, why not?
I have not tried restarting SQL server, as I'd like to know a way out of this without having to do that, plus I do not think it would make any difference anyway.
View 1 Replies
View Related
Mar 3, 2015
Setting up Transaction Replication in test environment. I am willing to bet that most of you take a production backup (if so, how, and using what?), restoring the database to your test environment, then running a snapshot to your subscriber and away you go.
But perhaps you take a backup of your publisher and subscriber, if so, how do you know there are no inconsistences because there were transactions sitting on the distributor?
What do you do if you have additional indexes on the subscriber for reporting, that are not on the publisher?
Here at work we are having issues with getting consistent databases set up with T Rep, missing rows, duplicate keys at subscriber etc. How to avoid these issues.
View 0 Replies
View Related
Dec 1, 2006
Hello,
I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:
We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.
Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?
We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.
That's the scenario.
Any feedback is welcome and appreciated.
Thanks,
Terence
View 4 Replies
View Related
Sep 13, 2007
Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.
View 2 Replies
View Related
Oct 7, 1999
Hello,
We have a SQL 6.5 Server with several DB's on it. Specially there's a critical DB on two separate devices for the Data and Transaction Log. The Data Device is 700MB and the Transaction Log started 210MB. Yet, the truncate function of the Log Device is not freeing space on it. We have been forced to expand the transaction device up to 860MB!!!! which is an outrageous size for it. We have tried the DBCC CHECKTABLE(syslogs)followed by DUMP TRANSACTION <<db_name>> WITH NO_LOG and then once again the DBCC CHECKTABLE(syslogs).
We even tried to create a new DB only with the dat file, but this also didn't work. Our Server Disks are almost full, and we can't grow the device no more.
Any suggestions??? PLEASE IT's REALLY URGENT!!!!
Best Regards,
BIT CeLA
Pfizer Labs.
Colombia
View 4 Replies
View Related
Feb 6, 2002
How do you truncate the transaction log in SQL 2000?
View 1 Replies
View Related
Feb 13, 2002
I need to truncate the transaction log, however, to do a backup on it we need 15GB of space free on the server which we don't have. How do we just force it to truncate it? I know the actual database is backedup and is OK...
Thanks,
Keith
View 1 Replies
View Related
Aug 1, 2001
SQL Server 6.5
Hi!
We have trans.log 200MB in total and 71 MB free space.
I run DBCC OPENTRAN and it shows no active transactions exist.
I run DuMP TRANSACTION .... WITH TRUNCATE_ONLY and it doesny clean log also.
What to do to get space back?
Thank you,
Elena.
View 4 Replies
View Related
Oct 17, 2001
SQL Server 6.5
NT
A number of procedures where run that filled the transaction log. Can I truncate the log during regular working hours or should I take the database down to single user mode and truncate?
Thanks in advance..
View 4 Replies
View Related
Feb 19, 2004
Hi all,
please show me the way how to truncate transaction log.
thank alot
View 2 Replies
View Related
Feb 27, 2004
DB Newbie question ahead.
I have a mere 100MB db with a 4GB transaction log. I want to truncate the log as I understand that truncating it will shrink the log by removing the transactions that have already taken place. However, the option to do a transaction backup is greyed out. I suspect this is from the db being in transactional replication with another server; however, I don't know for sure.
Are there any other ways that I can shrink the transaction log? I would like to do shrink it without taking the db offline either.
View 3 Replies
View Related
Dec 11, 2007
Hi
I need a simple command to truncate my transaction log. Currently it is 4gb and my .mdf file is only 300mb.
I have tried BOL, but all I need is a command to truncate it.
Thanks!
View 3 Replies
View Related
Dec 11, 2007
We do full backup every day and recovery model is Full, but we never done transaction log backup, so the transaction log files keep growing. What should I do? I think I should set recovery model to Simple, and actually we do DBCC Shrinkdatabase after full backup every day, but the transaction log file is still around 15GB.
Any suggestion would be appreciated.
View 9 Replies
View Related
Sep 2, 2015
I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication. I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS. I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication. SQL-P (publisher),
SQL-D (distributor) and SQL-S (subscriber). Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.
View 6 Replies
View Related
Nov 5, 2015
I have a table that i'm trying to pull the first transaction and last transaction per prodid. The first transaction, the ctrid should be 'TOPPEntry'.
sample raw data result
prodid--------lineid------itemid---------ctrid----------createddate--
=================================================================
TOP02296228--Line15-----TTC3071-IR------TOPPEntry------2015-01-03 07:45:31.000--input
TOP02296228--Line15-----TTC3071-IR------TOPCBFG--------2015-01-07 16:18:26.000--fg--done processing
TOP02296229--Line15-----TTC3071-IR------TOPPEntry------2015-01-04 07:45:31.000--input
TOP02296229--Line15-----TTC3071-IR------TOPLens--------2015-01-05 12:12:31.000--wip--wip means still in process
TOP02296230--Line15-----TTC3071-IR------TOPPEntry------2015-01-05 08:45:31.000--input
TOP02296230--Line15-----TTC3071-IR------TOPCBSCP-------2015-01-06 14:18:42.000--scrap--done processing
[code]...
View 12 Replies
View Related
Aug 12, 1998
Hi all,
Is it possible to TRUNCATE a table and BCP data into the same table in one
TRANSACTION?
My problem is that I want to refresh(delete and via BCP append new data) a
table without disturbing running applications. Can I run BCP from a
SQL-script or a stored procedure?
Thank`s
Jonas Dahlqvist
Alfa Laval Automation AB
View 1 Replies
View Related
Jan 27, 2005
Hello;
Within SQL Ent Manager - I am unable to truncate the tran log using the shrink file option window (although I can shrink the database file) - nor can can I truncate the log using command line sql in a query analzer window (dump tran < > with truncate only)....
Anyone out there offer any suggestions??
Thanks.
View 4 Replies
View Related
Dec 29, 2007
I use SSME to do a full backup of both the database & transaction log, selected "Truncate ..." in the options for the log backup. The log doesn't truncate.
I have looked at the reasons logs don't truncate in Books Online & can not find any that apply. There are no open transactions & in sys.databases log_reuse_wait is 0.
Any help would be appreciated.
Don Seydel
View 1 Replies
View Related
Feb 8, 2007
I'm trying to initialize a subscriber from a backup for a pull subscription. The publisher was started before the full backup was made. I followed the instructions @ Books Online topic "Initializing a Transactional Subscription Without a Snapshot" http://msdn2.microsoft.com/en-us/library/ms151705.aspx and How to: Initialize a Transactional Subscriber from a Backup (Replication Transact-SQL Programming) http://msdn2.microsoft.com/it-it/library/ms147834.aspx . I initialized the subscriber by restoring the db before I run the create subscriber scripts. All agents run successfully except for the following:
The network setup is that the publisher and the subscriber are in 2 different domains with the distribution database at the publisher. The subscriber agent is able to connect and run however the error on the subscriber side is:
"Agent message code 14080. The remote server "subscriber" does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers."
The warning on the publisher side is in the Replication Monitor where in the status it says Uninitialized Subscription.
Any ideas on why it says it isn't initialized? Is there a StoredProc that I have to execute to initialize from backup?
View 5 Replies
View Related
Oct 20, 2005
Hi guys
My website is in asp and sql2000. My problem is the ISP gave access to database through query analyser. some days the transaction log is growing too high. so i want to clear it. i call up them and clear it. My question is can truncate the log file through query analyser ?
I had limited access to database.
Jini
View 11 Replies
View Related
Mar 6, 2007
friend can i ask something like this
what query that can i truncate the transaction logs or shrink it into
2mb.
Please response to this. I need help
Thanks
View 7 Replies
View Related
Aug 28, 2007
Is it possible to truncate Transaction Log and Shrink DATABASE while the database is being used by users or the database becomes unuvailable during this operations?
Thank you.
View 2 Replies
View Related
Feb 25, 2008
Hello all, I have a very simple script which I use to truncate and reclaim space on all the transaction logs in a SQL Server 2005 database. However, I have some Sharepoint db names I can't change that have dashes ('-') in the names, e.g., SharePoint_AdminContent_dc27334f-fb2d-4453-9764-5d8b730fb9e1. The script won't back up those databases because it has a problem with the dashes in the names. Does anyone have any thoughts on how I could modify the script to get it to work? Here is the script:
ALTER PROCEDURE [dbo].[SP_GlobalTruncate_transaction_logs]
AS
Set quoted_identifier off
DECLARE @dataname varchar(300)
DECLARE @dataname_header varchar(75)
DECLARE datanames_cursor CURSOR FOR SELECT name FROM sysdatabases
WHERE name not in ('master', 'pubs', 'tempdb', 'model', 'northwind')
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM datanames_cursor INTO @dataname
CONTINUE
END
SELECT @dataname_header = 'Database ' + RTRIM(UPPER(@dataname))
PRINT ' '
PRINT @dataname_header
EXEC('BACKUP LOG ' + @dataname + ' WITH TRUNCATE_ONLY')
EXEC('DBCC SHRINKDATABASE (' + @dataname + ',TRUNCATEONLY)')
FETCH NEXT FROM datanames_cursor INTO @dataname
END
DEALLOCATE datanames_cursor
PRINT ''
PRINT ' '
PRINT 'Free space removed and transaction log truncated for each user database'
GO
And here is the error I get:
Database SHAREPOINT_ADMINCONTENT_DC27334F-FB2D-4453-9764-5D8B730FB9E1
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Thanks so much for the help.
View 4 Replies
View Related
Aug 21, 2007
Hi,
I currently have a 2000 Ent. production server and a stand by server ready for transaction log shipping.
Is it possible to setup transaction log shipping on a live environment without any interruptions?
I'm currently backing up the log every 1 hour, I'd like to increase to 15 minutes.
Any help would greatly be appreciated.
Thanks,
- Gary
View 4 Replies
View Related
Jan 25, 2002
I seem to be missing something.
I'm trying to pull a subscription from SQL Server 2000.
(To a desktop SQL 2000 install <msde i guess>)
The general error I keep running into is
When creating the subscription...
"Error 15004: Name cannot be NULL"
I cannot figure out where this problem originates.
I'm running the SQL Service account and Executive under a domain account.
(same one on both machines)
Does anyone know where I'm missing the Permissions for the subscription?
Please impart any Replication suggestions you have. Don't hold back....
Thanks in Advance,
Dano
View 3 Replies
View Related
Oct 9, 2007
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
thanks...
View 5 Replies
View Related
Aug 18, 2015
We have a SQL server 2012 database with size 200mb and Transaction Log has gone up to 34GB.
We have SQL server Mirror is enabled and principal database is running with Full Recovery Model.
How can I truncate/Shrink Log files? Will it impact on existing mirroring setup? I am very much new to SQL server.
View 17 Replies
View Related
Jun 28, 2007
Hi,
I have one database configured with the Recovery Model "Simple".
I am getting alot of full transaction log messages... is this supposed to happen?
Another question is:
Imagine i am in a middle of a big select into statement... and in another query i run the backup truncate log... am i going to loose information on the other batch ("select into?")??
Kind Regards,
Luis Simões
View 5 Replies
View Related
Jul 23, 2001
hi,
I want to setup transactional replication(PULL) between 2 servers .
can anyone guide me with the steps involved while performaing a Pull replication from server1 to server2.
Any help appreciated.
View 2 Replies
View Related
