Hi, everytime I put some load on my SQL Server, I get timeouts when trying to insert in a specific table (the table that gets the most insert activity). It does not matter if I do the insert in the Management Console or programmatically, it still times out. It is fixed by a server reboot.
It only happens after load, say some 50 processes continuously inserting into this table for a while (around 12 hours). So it smells like livelock but I'm newbie in SQL Server administration so I don't know where to peek and how to solve it. I've been watching the activity monitor, but see no weird state in any connection
If I insert in another tables of the same database, it works.
As extra information I can tell that there's a daily backup process that still has not completed (some 4 hours after its start). But that the timeouts started to occur this time some 6 hours before the time the backup was scheduled for.
So, what I'd like to know is how to detect what is really happening here and what can I do to fix this without rebooting the machine (or the server), hopefully tweaking only SQL Server, as the code that does the insert is not under my control, but if I can provide proof of a bug on their part that would be great too, as it would serve to make them generate a fix.
The insert that fails looks like
insert into calleventstbl (callId,startTime,endTime,type,subType,term,found,confidence,resourceID,ite ration,resourceName,Text,language) SELECT '63401','79','83','3','1',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','86','118','3','4',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','208','309','3','4',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','335','364','3','4',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','372','387','3','1',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','388','393','3','1',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','394','407','3','1',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','435','440','3','1',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','1','5','1','1','PSUT_Portales_y_comercios_1','1','37','1315','3',' Portales y comercios','portales y comercios','2'
it also fails if I do
insert into calleventstbl (callId,startTime,endTime,type,subType,term,found,confidence,resourceID,ite ration,resourceName,Text,language) SELECT '63401','9','13','1','1','PSUT_Traspaso_10','1','71','1150','3','Traspaso', 'es un traspaso a cuenta','2'
or
insert into calleventstbl (callId,startTime,endTime,type,subType,term,found,confidence,resourceID,ite ration,resourceName,Text,language) VALUES ('63401','9','13','1','1','PSUT_Traspaso_10','1','71','1150','3','Traspaso' ,'es un traspaso a cuenta','2')
This sounds like you have an open transaction somewhere. Run DBCC OPENTRAN in that DB. If it ha something to report, take note of the SPID and the time the transaction was begun. Do this when the backup is NOT running. If the SPID and time never change, then it looks like a connection has begun a transaction and never committed it or rolled it back.
news:b648f48a-27a8-465a-b03f-fc6f5a29590d@x35g2000hsb.googlegroups.com... Hi, everytime I put some load on my SQL Server, I get timeouts when trying to insert in a specific table (the table that gets the most insert activity). It does not matter if I do the insert in the Management Console or programmatically, it still times out. It is fixed by a server reboot.
It only happens after load, say some 50 processes continuously inserting into this table for a while (around 12 hours). So it smells like livelock but I'm newbie in SQL Server administration so I don't know where to peek and how to solve it. I've been watching the activity monitor, but see no weird state in any connection
If I insert in another tables of the same database, it works.
As extra information I can tell that there's a daily backup process that still has not completed (some 4 hours after its start). But that the timeouts started to occur this time some 6 hours before the time the backup was scheduled for.
So, what I'd like to know is how to detect what is really happening here and what can I do to fix this without rebooting the machine (or the server), hopefully tweaking only SQL Server, as the code that does the insert is not under my control, but if I can provide proof of a bug on their part that would be great too, as it would serve to make them generate a fix.
The insert that fails looks like
insert into calleventstbl (callId,startTime,endTime,type,subType,term,found,confidence,resourceID,ite r ation,resourceName,Text,language) SELECT '63401','79','83','3','1',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','86','118','3','4',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','208','309','3','4',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','335','364','3','4',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','372','387','3','1',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','388','393','3','1',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','394','407','3','1',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','435','440','3','1',' ','1','-1','0','0','x','0','-1' UNION ALL SELECT '63401','1','5','1','1','PSUT_Portales_y_comercios_1','1','37','1315','3',' P ortales y comercios','portales y comercios','2'
it also fails if I do
insert into calleventstbl (callId,startTime,endTime,type,subType,term,found,confidence,resourceID,ite r ation,resourceName,Text,language) SELECT '63401','9','13','1','1','PSUT_Traspaso_10','1','71','1150','3','Traspaso', ' es un traspaso a cuenta','2'
or
insert into calleventstbl (callId,startTime,endTime,type,subType,term,found,confidence,resourceID,ite r ation,resourceName,Text,language) VALUES ('63401','9','13','1','1','PSUT_Traspaso_10','1','71','1150','3','Traspaso' , 'es un traspaso a cuenta','2')
On 11 jun, 14:27, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote:
> This sounds like you have an open transaction somewhere. Run DBCC OPENTRAN > in that DB. If it ha something to report, take note of the SPID and the > time the transaction was begun. Do this when the backup is NOT running. If > the SPID and time never change, then it looks like a connection has begun a > transaction and never committed it or rolled it back.
Happened again, and it seems your prognosis was spot on.
Oldest active transaction: SPID (server process ID): 12s UID (user ID) : -1 Name : SetExpectedRowCount LSN : (121363:7464:59) Start time : Jun 11 2008 5:18:32:497PM SID : 0x0 DBCC execution completed. If DBCC printed error messages, contact your system administrator
now is 5:24 PM so it certainly seems stuck, and it never changes.
so, what do I do now? I can't find the process id "12s"
> On 11 jun, 14:27, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote:
> > This sounds like you have an open transaction somewhere. Run DBCC OPENTRAN > > in that DB. If it ha something to report, take note of the SPID and the > > time the transaction was begun. Do this when the backup is NOT running. If > > the SPID and time never change, then it looks like a connection has begun a > > transaction and never committed it or rolled it back.
> Happened again, and it seems your prognosis was spot on.
> Oldest active transaction: > SPID (server process ID): 12s > UID (user ID) : -1 > Name : SetExpectedRowCount > LSN : (121363:7464:59) > Start time : Jun 11 2008 5:18:32:497PM > SID : 0x0 > DBCC execution completed. If DBCC printed error messages, contact your > system administrator
> now is 5:24 PM so it certainly seems stuck, and it never changes.
> so, what do I do now? I can't find the process id "12s"
this is the result of select * from sys.sysprocesses where spid = 12
You need to find out what the 12s means. I've never seen that and I don't think it relates to SPID 12. Is there anything else in sys.sysprocesses that looks like 12s?
> On 11 jun, 14:27, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote:
> > This sounds like you have an open transaction somewhere. Run DBCC OPENTRAN > > in that DB. If it ha something to report, take note of the SPID and the > > time the transaction was begun. Do this when the backup is NOT running. If > > the SPID and time never change, then it looks like a connection has begun a > > transaction and never committed it or rolled it back.
> Happened again, and it seems your prognosis was spot on.
> Oldest active transaction: > SPID (server process ID): 12s > UID (user ID) : -1 > Name : SetExpectedRowCount > LSN : (121363:7464:59) > Start time : Jun 11 2008 5:18:32:497PM > SID : 0x0 > DBCC execution completed. If DBCC printed error messages, contact your > system administrator
> now is 5:24 PM so it certainly seems stuck, and it never changes.
> so, what do I do now? I can't find the process id "12s"
this is the result of select * from sys.sysprocesses where spid = 12
Okay, that's part of the Full Text indexing service, and the problematic table is the only one that has FT enabled. I killed the msftesql process and restarted full text indexing service and it worked.
So we now know the culprit, but not yet the why.
If you see my other problems posted today, at least one of them also seem related to full text issues (restores that fail). Is it that unstable? Do I have it misconfigured?
The catalog was created as on a "template" database that is later copied to (or restored into) the production database (I mean that this was only ran once, not on the current production database, is this relevant?)
On the template DB it was created as:
EXEC sp_fulltext_database 'enable'
CREATE FULLTEXT CATALOG [myFullText] WITH ACCENT_SENSITIVITY = ON
CREATE FULLTEXT INDEX ON [dbo].[callEventsTbl] KEY INDEX [PK_callEventsTbl] ON [myFullText] WITH CHANGE_TRACKING AUTO ALTER FULLTEXT INDEX ON [dbo].[callEventsTbl] ADD ([Text]) ALTER FULLTEXT INDEX ON [dbo].[callEventsTbl] ENABLE
news:bfaa7949-9cb1-4d1b-9c67-3e993dbaa026@8g2000hse.googlegroups.com... Okay, that's part of the Full Text indexing service, and the problematic table is the only one that has FT enabled. I killed the msftesql process and restarted full text indexing service and it worked.
So we now know the culprit, but not yet the why.
If you see my other problems posted today, at least one of them also seem related to full text issues (restores that fail). Is it that unstable? Do I have it misconfigured?
The catalog was created as on a "template" database that is later copied to (or restored into) the production database (I mean that this was only ran once, not on the current production database, is this relevant?)
On the template DB it was created as:
EXEC sp_fulltext_database 'enable'
CREATE FULLTEXT CATALOG [myFullText] WITH ACCENT_SENSITIVITY = ON
CREATE FULLTEXT INDEX ON [dbo].[callEventsTbl] KEY INDEX [PK_callEventsTbl] ON [myFullText] WITH CHANGE_TRACKING AUTO ALTER FULLTEXT INDEX ON [dbo].[callEventsTbl] ADD ([Text]) ALTER FULLTEXT INDEX ON [dbo].[callEventsTbl] ENABLE
|
