Management help. Insert times out, but server is up and running.
Vinko
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,iteration,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,iteration,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,iteration,resourceName,Text,language)
VALUES
('63401','9','13','1','1','PSUT_Traspaso_10','1','71','1150','3','Traspaso','es
un traspaso a cuenta','2')
Tom Moreau
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.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Vinko" <vin...@gmail.com> wrote in message
news:b648f48a-27a8-465a...@x35g2000hsb.googlegroups.com...
Vinko
> 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"
V.
Vinko
this is the result of select * from sys.sysprocesses where spid = 12
12 5404 0 0x00CC 579781 MSSEARCH
1 1 0 62 0 2008-06-11 11:18:11.763 2008-06-11 11:18:11.763 0 0
background
0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
FT CATLG
MONITOR
sa
0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0x0000000000000000000000000000000000000000 0 0 0
Tom Moreau
think it relates to SPID 12. Is there anything else in sys.sysprocesses
that looks like 12s?
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Vinko" <vin...@gmail.com> wrote in message
news:8f7581bc-1a88-48d1...@k37g2000hsf.googlegroups.com...
Vinko
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
SET NOCOUNT OFF
Tom Moreau
have a solution there. At least, we have found the source...
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Vinko" <vin...@gmail.com> wrote in message
news:bfaa7949-9cb1-4d1b...@8g2000hse.googlegroups.com...
Vinko
> Not sure. Could you please post this in the .fulltext newsgroup? They may
> have a solution there. At least, we have found the source...
Excellent. Many thanks for your help, moving on to .fulltext...
V.