-2147217871 Timeout Expired

Hi folks,

My SQL 2005 Server is acting up.

For a long while, at least a week, a particular stored procedure was working great. It was returning records just fine. Then my applicatino stated giving me the error message (after a long time of waiting) in the subject of this post.

I went to the server and opened a new query window in SQL Management Studio. From there I executed the stored procedure and it timed out. While executing the SQL server's CPU usage jumped to 100%. It bogged down the whole server!

Thinking the problem was a corrupt database or something like that, I created a copy (using the detatch/re-attach method) of that database.

I ran the same stored procedure with the same parameters on the copy and it completed execution in 0 seconds.

I then ran the stored procedure again on the original database (that was hogging 100% cpu) and it completed execution in 0 seconds. The act of coyping the database seemed to fix the problem?!?!

This is the second time this has happened. The first time I ignored it and moved on after I was able to get it to work again (not sure what I did). However as this is the second time in two weeks I've seen this behavior (I call "gremlins"), I thought I would ask the advice of my peers.

Do any of you know what might cause this behavior?

This query will recreate the table in question (note a Full Text catalog is created and the [Title] and [Description] fields are indexed.)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RSSHarvestedHeadlines](
[HeadlineID] [int] IDENTITY(1,1) NOT NULL,
[SourceID] [int] NOT NULL,
[Title] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Link] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PubDate] [datetime] NULL,
[GMTDateAdded] [datetime] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_GMTDateAdded] DEFAULT (getutcdate()),
[GMTLastHarvested] [datetime] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_GMTLastHarvested] DEFAULT (getutcdate()),
[HideFlag] [bit] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_HideFlag] DEFAULT ((0)),
[PublishedFlag] [bit] NOT NULL CONSTRAINT [DF_RSSHarvestedHeadlines_PublishedFlag] DEFAULT ((0)),
[EditStamp] [timestamp] NOT NULL,
CONSTRAINT [PK_RSSHarvestedHeadlines] PRIMARY KEY CLUSTERED
(
[HeadlineID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO


This is what I used to enable full text:

if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
begin
EXEC sp_fulltext_database 'enable'
end
GO

IF NOT EXISTS (select * from sysfulltextcatalogs where name = N'FTCatalog')
BEGIN
exec sp_fulltext_catalog N'FTCatalog', 'create'
END
GO

exec sp_fulltext_table N'[dbo].[RSSHarvestedHeadlines]', 'create', N'FTCatalog', PK_RSSHarvestedHeadlines
GO
exec sp_fulltext_column N'[dbo].[RSSHarvestedHeadlines]', N'Title', 'add', 1033
GO
exec sp_fulltext_column N'[dbo].[RSSHarvestedHeadlines]', N'Description', 'add', 1033
GO
exec sp_fulltext_table N'[dbo].[RSSHarvestedHeadlines]', 'activate'
GO

exec sp_fulltext_catalog N'FTCatalog', 'start_full'
GO

exec sp_fulltext_table RSSHarvestedHeadlines, 'Start_change_tracking'
GO

exec sp_fulltext_table RSSHarvestedHeadlines, 'start_background_updateindex'
GO




And last but not least, this is my stored procedure:

ALTER Procedure [dbo].[sp_RSSHarvestedHeadlines_FullTextSearch]
(
@ORKeywords varchar(4000) = 'xxxx',
@ANDKeywords varchar(4000) = 'xxxx',
@NOTKeywords varchar(4000) = 'xxxx',
@SourceID int = -1,
@IsHidden bit = null
)
As
set nocount on

SELECT HHL.HeadlineID,
HHL.Title,
HHL.Link,
HHL.[Description],
HHL.PubDate,
HHL.GMTDateAdded,
RSSSources.SourceTitle,
RSSSources.SourceLink
FROM RSSHarvestedHeadlines HHL
INNER JOIN RSSSources ON HHL.SourceID = RSSSources.SourceID
WHERE HHL.PublishedFlag = 0
AND (@IsHidden is null OR HHL.HideFlag = @IsHidden)
AND (@SourceID = -1 OR HHL.SourceID = @SourceID)
AND (@ORKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ORKeywords) OR CONTAINS(HHL.Description, @ORKeywords)))
AND (@ANDKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ANDKeywords) OR CONTAINS(HHL.Description, @ANDKeywords)))
AND (
@NOTKeywords = 'xxxx'
OR (
(NOT CONTAINS(HHL.Title, @NOTKeywords)
AND
NOT CONTAINS(HHL.Description, @NOTKeywords))
)
)

ORDER BY HHL.GMTDateAdded DESC, HHL.PubDate DESC


This is the query that I execute in a query window in order to test. This caused the server to go to 100% for a good 14 seconds to get results (but after the copy it now returns results instantly)

DECLARE@return_value int

EXEC@return_value = [dbo].[sp_RSSHarvestedHeadlines_FullTextSearch]
@ORKeywords = N'test',
@ANDKeywords = N'xxxx',
@NOTKeywords = N'xxxx',
@SourceID = -1,
@IsHidden = 0

SELECT'Return Value' = @return_value

GO

Note: running this qeury returns results instantly... (even when it was hanging)

DECLARE@return_value int

EXEC@return_value = [dbo].[sp_RSSHarvestedHeadlines_FullTextSearch]
@ORKeywords = N'xxxx',
@ANDKeywords = N'xxxx',
@NOTKeywords = N'xxxx',
@SourceID = -1,
@IsHidden = 0

SELECT'Return Value' = @return_value

GO



Any help or advise will be appreciated.