468,507 Members | 1,496 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,507 developers. It's quick & easy.

Select, insert and delete queries timing out

I am using a sql server 2000 database to log the results from a monitor
that I have running - essentially every minuite, the table described
below has a insert and delete statements similar to the ones below run
againt it.

Everything is fine for a few weeks, and then without fail, all accesses
to the table start slowing down, to the point where even trying to
select all rows starts timing out.

At that point, the only way to make things right that I have found, is
to delete the table and recreate it.

Am I doing something specific that sql server really doesn't like? Is
there a better solution then deleting and recreating the table?

CREATE TABLE [www2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[stamp] [datetime] NULL CONSTRAINT [DF_www2_stamp] DEFAULT (getdate()),
[success] [bit] NULL ,
[report] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[level] [int] NULL ,
[iistrace] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO [www2] ([Report],[Success],[Level],[iistrace],[Stamp])
VALUES ('Error on: <a
href="http://www2.klickit.com/include/asp/system_test.asp">http://www2.k
lickit.com/include/asp/system_test.asp</a><br><br>The operation timed
out
<br><br>(Test Activated From: Lynx/2.8.2rel.1
libwww-FM/2.14)',0,1,'',getDate())

DELETE FROM [www2] WHERE (Stamp<getDate()-3) AND (Success=1) AND (Report
Not Like 'Reset
Thanks in advance,
Simon Withers
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
1 3661
Simon Withers (si***@klickit.com) writes:
I am using a sql server 2000 database to log the results from a monitor
that I have running - essentially every minuite, the table described
below has a insert and delete statements similar to the ones below run
againt it.

Everything is fine for a few weeks, and then without fail, all accesses
to the table start slowing down, to the point where even trying to
select all rows starts timing out.

At that point, the only way to make things right that I have found, is
to delete the table and recreate it.

Am I doing something specific that sql server really doesn't like? Is
there a better solution then deleting and recreating the table?


I can't see any indexes on the table, so once the table start to
fill up, the DELETE statement will have more and more rows to scan.
That could also cause the INSERTS to block. A clustered index on stamp
seems to be in place. Maybe Success should be included too.

You could also consider whether you really need to have the report
and listtrace columns as text, maybe a varchar(4000) each could do?


--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

19 posts views Thread by John Keeling | last post: by
1 post views Thread by Craig Stadler | last post: by
11 posts views Thread by Jeff Sandler | last post: by
3 posts views Thread by Tcs | last post: by
11 posts views Thread by Eugene Anthony | last post: by
2 posts views Thread by paulmitchell507 | last post: by
6 posts views Thread by BobRoyAce | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.