Replication - Several Transactions Occurring
Feb 8, 2012
I have configured a transactional replication between 2 MSSQL 2005 instances. It is set to replicate every 15 minutes. Most replications take less than a minute with about 10 - 50 transactions being replicated.
However, 3 times during the day, the replication takes about 25 minutes to complete with 500-700 transactions being replicated. This also causes the application that is accessing the DB being replicated to become unresponsive when it attempts to retrieve rows from tables in the DB.
Might there be a setting that causes the transactional replication to accumulate transactions during the day?
View 1 Replies
ADVERTISEMENT
Jul 2, 2015
We have transactional replication. After creating replication, we found that many blocking and deadlocks are occurring continuously. Will it cause any blocking on publisher db.
View 4 Replies
View Related
Sep 26, 2001
On SQL 7.0, NT SP6 we had transactional replication running fine last Friday. After a reboot (Fri night) replication continued fine and then by Tuesday am, we saw a lot of 'timeout errors' for the distributor agent and the difference in numbers of rows (count(*)) for same tables on each side grew alarmingly high even though the replication continued fine..
I have changed the timeout from '0' to '300' which seemed to help with timeouts, but the difference in row counts kept progressing slowly while the replication seemed to be running fine..
Eventually we resorted to re-intializing the subscriptions on the replicated database to get past the issue and now 24 hours later after intializing was complete, we are still pretty much in synch..
Any one have any ideas why we got so much out of synch (wrt row counts) .. ? Any bugs/ gotchas/ quirks any one can think of?
thanks!!
Bruce
View 1 Replies
View Related
Jun 13, 2005
Hi,
i have a publisher, a distributer and 3 subscribers all working. All created at the same time.
Today i have tried to introduce a 4 th subscriber server and replication has fallen over. Each cycle will have updates eg
merged 75 data changes(6 inserts, 6 updates, 63 deletes, 48 resolved conflicts)
but the next cycle will show:
merged 55 data changes(4 inserts, 3 updates, 48 deletes, 37 resolved conflicts)
the 48 deletes are the conflicts from the previous cycle. this repeats hour on hour. The conflicts table is storing all of the changes but what have i done wrong in introducing the 4th server to get these symptoms? I followed the wizard all the way (may be that was it? :rolleyes: ) i have disabled this agent now and the others are working fine again. it is merge replication although the subscriber i have added is not going to be causing any updates.
conflicts are set to be resolved automatically
View 6 Replies
View Related
Apr 25, 2014
how many transactions per second transactional replication can handle?
Server has 128GB RAM, 12 core 2 Process (24 core total) each transaction is approximately 32k.
Publisher and Subscriber are on different servers on same LAN, separate disks for logs, data, indexes and tempDB.
I've found data on how many transactions per second SQL Server can handle but nothing on replication.
View 1 Replies
View Related
Jul 19, 2006
I am beginning design for a project to maintain an on-line archive of historical data for a production database, and then truncate old, rarely-used data from the production database. I am considering a method whereby we (A) replicate data changes from the production database to an archive database and (B) truncate old data from the production database according to predetermined business rules.
I am trying to determine whether SQL Server merge or transactional replication can be used to accomplish the replication portion of this requirement.
Given this scenario, there are two types of modifications that are made to the production database: (1) application inserts, deletes and updates which should be replicated, and (2) truncation deletes (as described in "B", above) which should not be replicated.
My question is: Will I be able to control the replication process such that application deletes are replicated, but "truncation" deletes are not? Could static row filters somehow be used to accomplish this?
View 6 Replies
View Related
Jun 14, 2007
I'm trying to create a transaction replication from SQL Server 2000 to 2005. Basic replication between the servers works just fine. However, what I want to accomplish is to be able to skip some of the transactions. Example - from time to time we want to purge some of the historical data from the main database (the publisher). We don't want the same purging to occur on the destination database, which will be used for reporting purposes and needs to include all the historical information. I wanted to simply stop the replication log reader, purge the records, backup the transaction log with truncation and then restart the reader. The only problem - the truncation on the replicated database keeps the transactions of the purging until they are replicated, so the transaction log backup doesn't help. Any ideas would be greatly appreciated!
View 5 Replies
View Related
May 12, 2015
There is a database "Foo" sitting on server "A". There is a database "Bar" sitting on server "B". A.Foo publishes a subset of its schema. B.Bar subscribes to A.Foo's publication. The distribution database is on "B" (B.distributor). This a push subscription (transactions are pushed to the subscriber from the distributor). Every day (including the weekend) I get the following alert:
"5/12/2015 3:53:16 AM, Unsubscribed Transactions (Count) on "B" is Warning.
SQL Server instance "B" has 636771 unsubscribed replication transactions received by the Distributor and not received by a Subscriber.
Unsubscribed Transactions (Count): Number of replication transactions received by the Distributor and not received by a Subscriber."
The number of transactions will vary. The alerts will be sent between 1:20 AM (EST) and 3:30 AM (EST). I'm trying to figure out what is causing the backup of transactions. I assume the issue precedes the alerts by 30-minutes or so.
There are no backups occurringNothing is blocking the distributor agent in the subscription databaseJob activity is at a minimum; the few jobs running run throughout the dayThe machine has plenty of resources -- CPU, RAM, etc.The publisher database shows no signs of stress.
View 3 Replies
View Related
Apr 11, 2007
Recently we disabled the transactional replication on a prodution database(data size:300GB) , two weeks later we were aware of the transaction log of that db almost reached the limitation (70GB).I investigated the issues and found:
1. open transactions pending for distribution:
DBCC opentran('xxxx')
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (323830:7083:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
2. exec sp_replcounters, got over 240,000 replicated_transactions, which caused the tran log extremely large.
The transactional replication has been disabled on this server, there are no publication, distribution DBs any longer, all replication-related agents are also gone, why did this still happen???
3. use sp_repldone manaully reset those 240,000 transactions to distributed, check opentran, it was fine for a while. The tran log file was freed up 99% after that.
4. check open transactions and run sp_replcounters today, found the over 60000 transactions again on the db.
Did anybody experience the same issue and knows how to fix this issue and stop this happening ?
Many Thanks,
Henry
View 4 Replies
View Related
Feb 22, 2008
Hi,
I have multiple questions and would appreciate any suggestions in resolving them. I'm a novice to these issues.
1) First of all, what is the exact command to setup the trunc. log on chkpt. option on for a transactional log of a SQL Server 2000 database? Is this option on by default? I have noticed for one of the databases I'm managing that the transaction log was over 12 GB, while the db was only 425 MB.
2) How's it possible to run a DBCC TRACEON to see the content of the transaction log to see if we are having any issues with any uncommitted transactions, i.e. updates, inserts, and/or deletes.
3) What are the commands to truncate inactive transactions and increasing the readbatchsize?
4) lastly, how do I validate transactional replication via checksum and find valid latency between a small number of changes that need to be committed between publisher and subscriber.
Thank you so much in advance!
Sincerely,
Alla
View 3 Replies
View Related
May 12, 2015
We have a filtered publisher (SQL Server 2012) with several pull and push subscriptions that have been functioning fine for years.
We have just added a new pull subscriber from a remote server. We configured an alternate folder location on the subscriber (pointing to existing path on a remote server). We generated new snapshot and partitioned snapshot, and moved to subscribing server at alternate path.We're seeing a problem we've never seen before:
When the merge agent runs, it applies the schema and performs a few hundred bulk inserts, but then proceeds to 'download changes to subscriber' as though the snapshot is old (it is brand new and should have all transactions). We have never seen this before, i.e. all data is usually applied in bulk when creating a new or reinitializing an existing subscriber.
we marked the subscriber for re-initialization (from both the publishing and subscribing server) with a fresh set of snapshots. The same problem occurred.We have also tried in our test environment (which mirrors production) and the snapshot applies as expected (via bulk inserts).
(It might be relevant to note the publisher was recently upgraded in-place from 2005 to 2012.)
View 11 Replies
View Related
Apr 12, 2005
Create Procedure UpdateGameIsOverlappingFlagByIds (@gameDateIds as varchar(200) = '') as
begin
if (@gameDateIds = '')
raiserror('UpdateGameIsOverlappingFlagByIds: Missing parameters', 16,1)
else
begin
Execute
('update games Set IsOverlapping = 1 where gameDateId IN (' +
@gameDateIds + ')')
end
end
return
--------------------------------------------
That is the sproc that doesn't seem to update the date when called from
asp.net but does work when done through the query analyzer. Am I
missing something?
Thanks for any help you can give me.
View 4 Replies
View Related
Jul 23, 2005
HiI have a strange problem with my SQL server 2k instllation - every 10mintutes when I have the Profiler trace with the entire "Errors" Eventcategory selected, the following 5 exceptions show up in the trace andthat too for the same SPID.Error: 16955, Severity: 16, State: 2Error: 16945, Severity: 16, State: 1Error: 16955, Severity: 16, State: 2Error: 16945, Severity: 16, State: 1Error: 16955, Severity: 16, State: 2Error: 16945, Severity: 16, State: 1I have no clue why this is occurring - I tried running a trace with theSP:StmtCompleted event on, but no other stored procedures show up withthe same spid close to the time where this exception is logged.Does anyone have a clue as to why this error is occurring ?Rahul
View 1 Replies
View Related
May 14, 2015
I have 3 columns of data CustomerNum, Newsletter, and NumSent.
I need to return the only the CustomerNumber and Newsletter combinations having the Max(NumSent) So it should be a unique customernumber with the newletter having the most numsent.
For the data below, my result set will be:
0000000000000000101 Healthcare
0000000000000000102Â Construction-Environ Svcs
How can I do this easily with GROUP BY , Max or subselect?
Sample Data:
0000000000000000101 Healthcare                      19
0000000000000000101 Construction-Environ Svcs 11
0000000000000000101 Manufacturing                  8              Â
0000000000000000101 Homecare                       5
0000000000000000101 Daycare                         4
0000000000000000102 Healthcare                      9
0000000000000000102 Construction-Environ Svcs 21
0000000000000000102 Manufacturing                   5             Â
0000000000000000102 Homecare                        11
0000000000000000102 Daycare                          1
View 6 Replies
View Related
Apr 16, 2007
This is strange... just started getting this today, while it's worked for weeks. But alas, that is programming.
The error I'm getting is
[Connection manager "FTP DQ Connection"] Error: An error occurred in the requested FTP operation. Detailed error description: 200 Type set to I. 200 Command okay. 550 /usfsr/DFE_INPUT/FATest_DQ.txt: No such file or folder. .
I'm uploading a file (FATest_DQ.txt) to the folder /usfsr/DFE_INPUT/. I eventually just uploaded the file manually using IE6 to make sure the connection properties were still the same.
Any ideas?
View 1 Replies
View Related
Mar 17, 2008
I have TableA with the following structure and Data.
CaseID ActionID StartDate
------ ------------ --------------
A232/21/2007
A212/8/2007
B2212/4/2007
B1310/12/2007
B1710/7/2007
C617/6/2007
D3311/2/2007
D5610/22/2007
D267/29/2007
E226/21/2007
E585/25/2007
E874/8/2007
E293/23/2007
Expected Results:
I want to create TableB with the following structure and data .
CaseIDX YZ
----- - - -
A001
B011
C000
D001
E111
X = 1 when a case has 4 or more actions within 180 days , 0 otherwise
Y= 1 when a case has 3 or more actions within 90 days , 0 otherwise
Z= 1 when a case has 2 or more actions within 30 days , 0 otherwise
Any help will be welcomed
View 12 Replies
View Related
Apr 2, 2008
Here is the table that I am wanting to insert the information into (BTW I am wanting the UserID, EntryDate, Note) On my page i do have a text box for the UserID as well as a Note textbox. When I hite the submit button on my page I am already sending the UserID textbox information to be sent to another table (called RequestTable). However, I am wanting to take that same UserID and insert it into the RequestNote table as well. Let me know if you have any questions for me to further explain anything.
**RequestNote**RequestNoteKey (PK) (has identity set to yes)RequestKey (allows nulls)NoteEntryDateEntryUserID (allows Nulls)****This is my stored procedure that I am calling called "NoteInsert"***@Note nvarchar(1000),@EntryUserID nvarchar(50)AS INSERT INTO RequestNote (Note, EntryDate,EntryUserID)
VALUES (@Note,GetDate(), @EntryUserID)
RETURNGO
****THIS IS THE PAGE THAT CONNECTS THE USER INTERFACE AND STORED PROCEDURE***public static void AddRequestNote(string requestNote, string userID){using (SqlConnection connection = new SqlConnection(connRequestNote)){using (SqlCommand command = new SqlCommand("NoteInsert", connection)){command.CommandType = CommandType.StoredProcedure;command.Parameters.Add(new SqlParameter("@Note", requestNote));command.Parameters.Add(new SqlParameter("@EntryUserID", userID));connection.Open();command.ExecuteNonQuery(); <--THIS IS WHERE I GET AN ERROR THAT SAYS Cannot insert the value NULL into column 'RequestNoteKey', table 'RequestNote'; column does not allow nulls. INSERT fails}
View 5 Replies
View Related
Mar 15, 2004
I have a mixed mode account setup with exec permissions granted on my stored procedures. I am running an sp that is editing a member, and if i run it using my query analyzer with the same login, my sp writes to the db. however, if i'm calling it through my web app (asp.net) it doesn't. here is my sp code:
USE DATABASE
--DROP PROC sp_EditMember
GO
--Create the stored procedure
CREATE PROCEDURE sp_EditMember
@member_id smallint,
@last_name nvarchar(50), --not nullable
@first_name nvarchar(50),
@spouse_name nvarchar(50),
@street_address nvarchar(50),
@city nvarchar(35),
@state nvarchar(5),
@zip_code nvarchar(15),
@zip_4 nvarchar(4),
@area_code nvarchar(10),
@phone_number nvarchar(20),
@email nvarchar(50),
@child_1 nvarchar(30),
@child_2 nvarchar(30),
@child_3 nvarchar(30),
@child_4 nvarchar(30),
@child_5 nvarchar(30),
@member_status nvarchar(20),
@member_exp nvarchar(10),
@plaques_st nvarchar(10)
AS
BEGIN TRAN
UPDATE Members
SET last_name = @last_name, first_name = @first_name,
spouse_name = @spouse_name, street_address = @street_address,
city = @city, state = @state, zip_code = @zip_code, zip_4 = @zip_4,
area_code = @area_code, phone_number = @phone_number, email = @email,
child_1 = @child_1, child_2 = @child_2, child_3 = @child_3,
child_4 = @child_4, child_5 = @child_5,
member_status = @member_status, member_exp = @member_exp,
plaques_st = @plaques_st
WHERE member_id = @member_id
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
END
ELSE COMMIT TRAN
GO
--------------------------------------
on my app side- i've already ensured the datatypes match up and the sizes are all ok (except for member_id, the variable is an int and i pass it as a small_int- the number is ranging from 1000-10000).
any troubleshooting on this?
thanks in advance,
sudeep.
View 1 Replies
View Related
Nov 16, 2000
I am not successful in getting SQL Server Agent to startup when SQL server starts up.
I believe the problem has to do with a lack of permissions or rights. I will list the steps
below which seem to me to be all that are needed to install SQL Server Agent. I used
Enterprise Manager to get this information.
If anyone can shoot any holes in the process, please do, that may be where my
problem lies.
a. Created NT accounts called MSSQLSERVER for the server and MSSQLSERVERAGENT
for SQL Agent.
b. Each of these accounts has the "right" to logon as a service.
c. Each of these accounts has "admin" rights.
d. Each of these accounts has been added to SQL SERVER under Security and Logins.
e. The properties assigned to each of the accounts are:
(1) on the general tab -- Windows NT authentication is in effect, database is master
and language is English (2) on the server roles tab -- System Administrators is checked
(3) on the database access tab -- the master database is checked
f. Under SQL Server Agent, properties, selecting the General Tab, in the frame
titled "service startup account":
(1) the circle labelled "this account" is clicked (2) the account is filled in using
domain namemssqlserveragent (3) the correct password is filled in taking case
into consideration
g. Under SQL Server Agent, properties, selecting the Connection Tab, in the frame
titled "sql server connection":
(1) the circle labelled "use windows NT authentication" is clicked
h. Right clicking the server and selecting properties show a multi-tab window
(1) the general tab shows that all three autostart boxes are checked
(2) the security tabs shows SQL Server and Windows authentication are in effect
(3) the startup service account shows "this account" selected and the account
is filled in with domain namemssqlserver and the password is filled in (taking
case into consideration)
In all cases above, the domain name is not the domain in which the server resides,
but is in a "trusted" domain.
Any help would be very much appreciated!!!!!
View 2 Replies
View Related
Sep 14, 2015
I want to check that no inserts are occurring in 5 tables that are depending on each other and then drop and create those 5 tables. I have scripts to drop and recreate the tables. How do I check that no inserts are happening in these 5 tables?
Table A
Table B dependant on
Table A
Table C dependant on
Table B
Table D dependant on
Table C
Table E dependant on
Table D
View 9 Replies
View Related
May 22, 2005
Hi there,
I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following:
<code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated
View 3 Replies
View Related
Jun 5, 2006
When any of
my tasks or script tasks in my control flow, or data flow, have an
error, the entire package, and then the calling package fail, as far as
I've seen, through some CTP versions, beta versions, and the release
version of SQL Server 2005.
But, I've just made a change, in an
sproc called from an ExecuteSQL Task in a PreExecute event attached to
a DataFlow, and that ExecuteSQL Task is reporting an error in the
output window, but the package execution is not being stopped. Why not?
I see something like so
Error: 0xC002F309 at MyExecuteSqlTaskName, Execute SQL Task: ....
Task failed: MyExecuteSqlTaskName
Warning:
0x80019002 at OnPreExecute: The Execution method succeeded, but the
number of errors raised (1) reached the maximum allowed (1); resulting
in failure. This occurs when the number of errors reaches the number
specified in MaximumErrorCount. Change the MaximumErrorCount or fix the
errors.
repeated many times in the output stream, and the same thing for the PostExecute event attached to the same object.
I'm disappointed, because I want errors propagated upwards, as I'm used to.
I
looked, and as far as I can tell, all my ExecuteSQL tasks (in events
and in the regular control flow) have the default settings of
FailPackageOnFailure: False
FailParentOnFailure: False
MaxErrorCount: 1
Do
I have to go revise these settings on every ExecuteSQL Task in every
event handler in every SSIS package? (That will be exceedingly
tedious.) Do I set these on the ExecuteSQL task inside the event? Do I
set these on the event handler itself?
View 3 Replies
View Related
May 14, 2015
We created sql alerts on all our sql servers environments. Now, i want to see each sql server which sql alerts so far got fired and which one never occurs. is there any way, we can get this information from any system database?
View 9 Replies
View Related
Jul 11, 2006
I had created a CLR function in my db and was able to execute it successfully a couple of months ago. But when I tried to execute it today it was throwing errors saying there was something wrong with the permissions on the assembly. So I decided to drop everything and recreate it except I can not longer create the assembly with EXTERNAL ACCESS permissions. Whenever I try to create the assembly I get the followng error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I also tried to create the assembly with Unsafe permissions and got the same error. Does anyone know why this error would be occurring now? I tried creating the same assembly on a different SQL 2005 server and it creates successfully and can be executed successfully. Any help would be greatly appreciated!!
Thanks!
GN
View 3 Replies
View Related
Aug 13, 2003
Hi All,
Can anybody suggest me a website where I can find articles on Managing transactions with Sql server. Also a scenario where the transactions take place in a environment involving 2 different databases, Like the bank account and credit card transactions (specifically of 2 way kind)
Thanks
View 5 Replies
View Related
Apr 6, 2005
I have a web application with a shopping cart, how do I stop all the shopping cart transaction from going into the db log? Is this possible? These are are only transient data movements, and will never be need to to restore to, and they are cause log bloat. Or is there a better way to stop log bloat?
Thanks
View 2 Replies
View Related
Apr 17, 2000
Hi All,
How can we change connection properties in a DTS pkg with connection?
You can loop through the connection count but the connection ID is not static one.So can’t rely on that.
Is there another way of changing connection properties?
Thanks in Advance
Barath
View 4 Replies
View Related
Sep 5, 2000
Hi all....
I am currently designing a DTS Package to import data that is processed daily into a large database.
I have to design the package such that if any step fails when importing, I roll back the entire transaction.
I have designed the package with this in mind, checked "join transaction if present" and "rollback transaction on failure" in all of the workflows. I have also made all workflows serialized.
However, when I run the package, it fails on one of the data pumps with the error:
Transaction context in use by another session.
Any ideas?
Thank you,
Brian
View 2 Replies
View Related
Dec 1, 2000
I am replicating (finally!!) and on my publishers agent history I can see it says xx transactions with xx commands were delivered. (xx being the number)
Where can I look to see what the transactions or commands are?
Is there a place the system stores this information?
View 1 Replies
View Related
Oct 17, 2003
what is maximum limit of no. of transactions per sec. in sql server 2000
View 2 Replies
View Related
Sep 6, 2004
Is there a point to wrapping a single UPDATE or INSERT statement in an explicit TRANSACTION:
BEGIN TRANSACTION
INSERT INTO Table (...) VALUES (...)
COMMIT TRANSACTION
I understand ACID and concept of transactions. However, I thought they were only necessary for multi-statement operations. I'm maintaining code that does this and am wondering if this is necessary. Does SQL Server guarantee ACID for single statements? Are single UPDATE/INSERT statements prone to race condition like affects without using explicit transactions?
View 2 Replies
View Related
Apr 17, 2008
Are there any scenarios where an un-commited transaction would block further queries?
View 1 Replies
View Related
May 27, 2008
If you run the Begin Transaction code and then run a create such as an update query and you see that it effects the number of rows that you wanted it to effect is there a way to look at the actual data that changed before you Commit Transaction?
Thanks!
View 7 Replies
View Related
