Replication :: Blocking And Deadlocks Are Occurring Continuously
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.
ok, about a few months ago I brought up the issues we were having with deadlocks. My company is running a .net data entry application hitting against a sql 2000 box. Forget hardware since we have more then enough hardware to handle the load.
The issue we are seeing is we get a process that blocks a table, and the bulding up of block process that cause timeouts/deadlocks etc. A couple of things we see when this happens is that .net on the web server seems to open up more and more connections(it seems to double or triple the connections as the blocking begins), we start to see deadlocks and we see alot of timeouts in the app due to the blocking.
The blocking aways happen on an insert of an order or a customer. One thing I keep wondering about is how we handle the unique id for our tables. Our DBA had us put in the following:
Begin Trans select max(id) + 1 insert.... commit trans
I've heard from a few here and in other forums this is the wrong way to go. I'm still get a gut feeling that this could start causing issues like what we are seeing. My problem is I'm not knowledgable enough to make a strong enough case with it.
What I need help is getting enough information to either point to how we manage ID's or at the very least rule them out. I've done searchs in google and some other search engines but there doesnt seem to be any good articles on how best to handle unique ids and show a good case for it. I do know I can use sql identity, or even guid with the newid() function but I need solid proof before my boss will move to one of these methods. Any links you guys can give or even your own write up will really help my case and hopefully help my headache.
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?
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.
Is there anyway to prevent deadlocks during the snapshot replication?
I understand that you can minimize by maybe creating a couple different snapshots (mixing tables to minimize locking while snapshot is being created), but is there any other way?
We have an SQL2000 database server that uses merge, pull replication with about 70 clients around the country, each with their own publication. Each article in each publication is row filtered by a single condition. There are busy times of the day when all of these clients attempt to replicate within a 15-20 minute period. We have noticed the following error that is creating conflicts that are resulting in data not being transferred to the server and very long replication run times (error message is paraphrased):
The record was inserted at the client, but could not be inserted at the server. Transaction (Process ID XXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Is there a way to lessen the effects of this error? How can I reduce the number of deadlocks that are occurring? We will need to be adding a great deal more clients to the system in the future; is there a way to grow the system without making the deadlock problem worse?
I am using the DTS wizard to import tables from an AS/400. Some of the tables in the job are quite large and Sql Server either hangs up or completely crashes. My current configuration is WIN NT, SP4.0 SQL Server 7.0 no Service pack. Any suggestions?
I need to create a package that will monitor a table in a source system and when a flag is set, load data from other tables in this source system to my destination system. Today this is accomplished with a SQL Agent job that executes every 15 minutes. If there is no work to do the job simply exits. I would like to create a SQLIS package that checks this control table every 30 seconds. Can I create a package that runs continuously?
I have small db and load is very less. I am set full backup at  9:00 pm once a day,and set  transaction log backup to every 15 min.then i am taking the transaction log backup in 9:15,9:30,9:45....... My question is  I lost my data between 9:15 to 9:30 . in those time i will do some transactions.Then how to recover my data even with out lasing single  transactions.
I'm after hosting a website with basically a table in it that is linked to a MS SQL Server 2005 database, which I want to update on a pretty much continual basis from my own server PC which I'm running a data mining tool that updates the MS SQL Server 2005 database.
Any idea on how I would achieve this, or any pointers would be much appreciated.
I have a query that uses a lot of joins, subqueries and temp tables (I inherited it and am leary about rewriting it). I tried to rewrite it using table variables at one point, but the run time just got ridiculous for any query spanning more than a few months.
It currently runs as-is in about 3 minutes or less in Management Studio; however, when I try to run it via SRS or the Visual Studios 2005 Designer it runs indefinitely. It also runs indefinitely if I try to refresh the fileds or run it from the data tab (basically any time it has to call the stored procedure).
Does anyone have an idea of how I can get this report to run or know why it runs differently in SRS than in Management Studio?
I was wondering if it is possible to continuously query a real-timedatastream using SQL Server. Does anyone have any experience of this? Ihave found LINUX based systems such as Borealis and STREAM. I wouldprefer to use a Windows based system as the program using the queryresults is Windows based.
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?
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
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.
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
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.
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
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}
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:
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).
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.
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
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
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?
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?
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!!
Our system is reasonably complex with a lot of non-trivial stored procedures. As the load on our DB increased we're now getting more and more deadlocks (10 per day or so from about a million stored proc executions).
We try to avoid transactions where we can, and we do attempt to optimse stored procs to steer clear of deadlock conditions, but with the sheer number of stored procedures we can't possibly avoid all deadlock conditions.
One solution I'm considering is to re-run stored procs that failed because of a deadlock. In the .net code we'll run the stored proc, check for a deadlock error and if one happened, wait 100ms and try again.
we have a production inviremont that is running for about 10 months. Since a couple of weeks we are having problems with "Deadlocks".
This cant be due to an increase in data size on the tables that are having the issues because these are cleaned in the same transaction that populates them.
These tables are used to store temporary data that the production system needs to calculate the correct price for any given order. This transaction takes between 0.5 to 1 second to commit.
We are running on a dual processor machine with 1 Gb of RAM with SQL Server 7 - sp 3, Windows NT 4 sp 6, Microsoft Transaction Server.
In all our queries and stored procedures we use the optimizer hints (nolock) for select statements and (rowlock) for updates or deletes.
Is there any way to totally avoid deadlocks. In some critical applications we have removed transactions entirely, counting on other means to maintain database consistency. We still get deadlocks in this area. These are mainly inserts, and the only thing I can think is that updates to the indexes are causing multiple page locks which result in deadlocks. Is this true?
Will deadlocks be eliminated in 7.0 with row level locking for this situation? Or will index page splits still cause a possibility of deadlock contention?
I have a problem with a SP in 6.5. When i try to run a Stored Proc which is a simple select statement dumped into a temp table in a particular database, I lock other users who are tring to log into other databases some in tempdb database. When i try to kill the process the rollback takes almost 45 mins or so..till then no one can log on to the server.
The SP works fine when no one is logged into the Great Plains server. One more thing i observed is that, the SP when run results on a deadlock only when the owner is a user. If the owner is DBO it works fine.
I've got a deadlock problem. The log below has been generated. The problem is that during one day, I have more than 300 deadlocks like it. Before, the were not so many deadlocks. During past year, the number of users has grow (from 100 before to 500 or 700 now)
*** Deadlock Detected *** - Requested by: SPID 360 ECID 0 Mode "S" - Held by: SPID 113 ECID 0 Mode "S" Index: aaaaa_PK Table: TABLE_1 Database: MYDB == Lock: KEY: 22:325576198:1 (ff009ae5078d) - Requested by: SPID 113 ECID 0 Mode "S" - Held by: SPID 374 ECID 0 Mode "X" Index: aaaaa_PK Table: TABLE_1 Database: MYDB == Lock: KEY: 22:325576198:1 (ff009ae5078d) - Requested by: SPID 374 ECID 0 Mode "IX" - Held by: SPID 360 ECID 0 Mode "S" Table: TABLE_2 Database: MYDB == Lock: PAG: 22:1:2428 == Deadlock Lock participant information: Input Buf: S E L E C T the_rest_of_the_query SPID: 360 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 1 Input Buf: s p _ e x e c u t e 8 Input Buf: s p _ c u r s o r 8À B 8 8f ç @ Table I Input Buf: S E L E C T the_rest_of_the_query SPID: 360 ECID: 0 Statement Type: SELECT Line #: 1 == Session participant information: == Deadlock Detected at: ==> Process 360 chosen as deadlock victim
I have done : - rebuild indexes on all tables (fillfactor 90) - analysed memory activity
Could a lack of memory be at the origin of the problem ? Which counters in perfmon are significant for memory lack ?
Could the index fill factor could be at the origin of the problem ? At time, it is at 90 percent.
Config : Winnt4 Server, MS-SQL 7 SP4 , 2 GB of RAM , 2 x Xeon 700
I have an application built on top of a questionable DB design which requires overcomplicated selects. The application is experiencing deadlocks regularly, in some cases with only one concurrent user.
I set the trace flag 1204 but am not seeing anything in the Error.log and I initiated a trace in profiler which does not seem to show any deadlock. Despite having recreated the problem which show my browser hanging indefinitely. When I run the following queries:
SELECT spid, waittime, lastwaittype, waitresource FROM master..sysprocesses WHERE waittime > 10000 AND spid > 50
SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)] FROM master..sysprocesses p WHERE open_tran > 0 AND spid > 50 AND datediff (s, last_batch, getdate ()) > 30 ANd EXISTS (SELECT * FROM master..syslockinfo l WHERE req_spid = p.spid AND rsc_type <> 2)
I get:
55860978LCK_M_XPAG: 13:1:2573
54AWAITING COMMANDsleeping sa 11499 55UPDATE sleeping sa 21499