-2147217871 Timeout expired - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

-2147217871 Timeout expired

Author

Topic

CodeDreamer68
Starting Member

9 Posts

Posted - 2008-03-06 : 15:50:45

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.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-06 : 22:06:24

Did you check procedure cache hit ratio? How about blocking?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources